Total Column appearing when it shouldn't
I've just encountered an issue in a dataview where the total column appears even though 'row totals' is un-selected on the block (appearing in 10.3 and 10.5)
The ORANGE column below should not be appearing. The cube/block it relates to is dimensioned by an entity called 'Column Header Hours', and the block is set to data entry and 'detail by' this Column Header Hours entity.
A capsule procedure triggered by a pager on the screen then uses a mapping cube to select members from this entity based on a mapping cube. This is to control what DataEntry the end user has available based on the paged report.
Depending on the pager 'selection' some times there will be 0 members of the 'Column Header Hours' entity selected. Doing this as a dynamic screen select doesn't work, as BOARD will select ALL members of the entity where the mapping cube is empty (which is the opposite of what is required).
So we tried to use a trigger procedure.
However even though no members of the Column Heading Hours entity are selected, and none are showing, BOARD still displays the 'TOTAL' column. If a member of Column Header Hours is selected the Total column doesn't show, and only the column for the selected member shows.
It seems like a bit of a bug to me, but just wondering if anyone else has come across this and found a way around this issue?
With 0 members mapped the 'TOTAL' column shows
with 'Labour Hours' mapped - no 'TOTAL' column showing.
Answers
-
Hi Brendan,
Just wondering on this one have you tried using an algorithm block with reverse algorithm for enabling entry and then this will hide the column if there is nothing to enter, also could use an if formula so that if no "labour hours" are selected the block is 0 (create a block which has a one for every labour hour) if this block is 0 or equals the max then you can hide this block.
Haven't tried it in full but looks to be likely, from a quick review.
regards
Graeme
1 -
Thanks Graeme Whorrall, yeah Reverse Algorithm is one of the possible workarounds we've highlighted, but I'm wondering if there's something that actually gets the Dataview to display what it should without the need for a workaround.
0 -
Actually having had a quick think Reverse Algorithm won't work in this situation. We need Show All Columns checked as we need columns to show even when they have no data (to make them available/visible for D/E)...so it needs to be a selection that controls the columns to show. This is exactly where the Idea https://community.board.com/ideas/1027-hide-based-on-in-layout-block would come in handy, particularly as we can't use an algorithm to control the hide/show of an empty column in this case.
0 -
Hi Brendan,
Just a thought, and not tried it out, but if you have the algorithm looking to the block with reference and then also to the block you are dynamically filtering on, the block you are dynamically filtering on will be set to 0 so you can then use that on the algorithm block to hide it?
Think it is still a workaround and agree it should not show any if the dynamic selection is 0 but could get you round the issue?
Regards
Graeme
0 -
Hi Graeme Whorrall, sorry but I don't quite get what you mean so perhaps I didn't explain what I need clearly.
But the way I see it we can't use an algorithm regardless of how that algorithm is set up. If the block we want to enter into contains zero, then the algorithm we are showing (and setting reverse on to return the value to the cube) MUST also show zero. This is where I believe it will fail.
The only way to control a column displaying with an algorithm is if you have 'SHOW ALL' unchecked, we can them multiply a cube WITH a value by the mapping/control cube with either 1 or 0, to give either the cube Value or 0, the turn on SHOW ALL and all non-mapped columns will then hide (as the algorithm returns 0).
However that does not apply in this case as the cube starts empty, and will be cleared each month as it is used for data entry, so there are no values at the beginning. So even if our mapping cube has a 1 for that column/entity member, we have a 0 value in the cube. So regardless of what the mapping cube has this needs to show 0, and as long as it shows 0 we need 'SHOW ALL' checked, which then means all those columns mapped with a 0 will also show. We could force this 'mapped' column to show a value, but that value would then be incorrect, and it would then require SHOW ALL to be UNchecked which is not possible here due to other blocks within the layout that also required D/E and start empty.
0 -
Hi Brendan,
Thought I would doc it, is quite convoluted, but think this is what you are after but might have the wrong end of the stick. let me know if it will work for you so as below I have the Dynamic selection in the top of the screen so I can select and the result (the one you want below).
(The select on screen isn’t important to this cube but if you need it for something else should be fine.)
So as you can see with nothing select nothing is available in the bottom screen but if I select 2 then they alone are available
To achieve this in the cube list as below I have test which is the cube I want to enter to I have the input algorithm which is set to look at the dynamic selection if it is 0 keep as 0 otherwise set to 0.0000001
Now in the axes I have unticked show all as I don’t want the blanks to cause me issues (this may cause you issues if one of your other blocks is 0 but you need it to show regardless)
So if I go to the cubes all are referenced by entity I called my hello as below
But there is the issue the amount is 0 but we need the line to show, to get round this in the algorithm I say if block c is 0 then 0 which will ignore if it is not selected or if none are selected. But if it is something then take block a but if block a is 0 then set algorithm to something tiny I used 0.0000001 so it will show as 0 on screen
In the reverse algorithm I then say I the block is 0.0000001 then a=0 else =b
Hope this will work for you, know it is convoluted but think it gets there? (as long as you don't need to show all 0 on columns)
Regards
Graeme
1 -
just seen you have other blocks that need to return even if empty so not sure this will help you.
0 -
Thinking you could use the reverse algorithm in the same manner to make block D and E visible? if there is only a few and you cant use another data view is the only way around I can think of?
regards
Graeme
0 -
Thanks for that Graeme Whorrall. It is pretty much what I thought you were referring to, the only thing I never thought of was putting 0.00000001 in your IF so that it still appears as zero, good idea. I was picturing a line of 1s sitting there. An extension to the 0.0000001 that might also be useful could be to add a ROUND() into the reverse algorithm so that it doesn't go and store 0.0000001 in the actual cube if they don't enter anything.
But yes, the main issue is I need 'show all' to be checked. As I have 4 other cubes which are also data-enterable, all of which will start empty, and one is a text cube with entity based selected values so I can't use a reverse algorithm. If I didn't have that limitation your 0.0000001 would've been handy.
Cheers!
Brendan
0
