Rows and Columns - Allowable Value Columns

The purpose of this tab is to reliably hide large numbers of columns even if report selections subsequently change. The columns being hidden usually represent selection combinations that aren't interesting to the user, or don't make sense.

To put it another way - a common problem that occurs in reports with more than one dimension in the across axis, is that the user does not want every combination of every selected member to appear as a column in the final report.

Take the example where they have three dimensions in the across axis, and from them, they select:

• Fiscal Year: ThisYear, LastYear

• Fiscal Period: ThisMonth, YTThisMonth

• Measures: Actual, Budget, Variance, Variance%

The user will get a set of columns similar to this:

every single combination of the selected items has resulted in a column.

Now, the first 8 columns are fine and the user probably wants to see them.

Then we get to the next block of 8 columns, for "last year". Does the user care about last year's budgets or how the business performed this time last year against them? Almost certainly not. More likely, they just want to see the actuals from last year, ie: 2020, Period 3, Actual and 2020, YTD, Actual - and hide all the Budget, Variance and Variance% columns.

Similarly in the Last Year Variance group of columns that follows, they probably just want to see Period 3, Actual and Period 3, YTD - and that's it.

So there are quite a number of columns that need hiding.

Now the user, could seek to do this by right clicking on each column in the grid and selecting "Hide Column"

Or they could do it in bulk via the second tab of the "Rows and Columns" dialog as described here

But both of these methods are fiddly and time consuming. And the settings get lost if the user removes the selection from the filter.

However, there is a third way of hiding columns using this, the "Allowable Value Columns" tab.

This allows the user to specify which columns are displayed, by defining some rules. The advantage of this approach, is that as selections change in the filters, the rules continue to be applied meaning that the user doesn't have to go back into the dialog and rebuild the list of hidden columns a second time. The unwanted combinations are always suppressed.

So lets say the user wants to reduce the set of columns shown above, to this reduced set below:

They can set up some rules to do it as shown below:

In this example the rules to achieve have been built around three blocks grouped by the outermost "Fiscal Year" dimension. If a column fulfils the criteria of any one block, it is added to the "Results" area on the right hand side and will appear as a column in the report.

The behaviour that any block can produce a match is governed by the setting on the outer block

The three blocks within, in this example, then relate in turn to ThisYear, LastYear and VarLastYear (which is a calculated item in the Fiscal year dimension).

In each block we have defined that all criteria must be met within the block for a column to be displayed

In the "ThisYear" block

We have specified first "ThisYear" and then, all the selected items from the Measures dimension and Fiscal Period dimensions. This is nice and clear but as an aside, could be simplified to:

Then, in the "LastYear" block

We have specified "LastYear" and then just the Actual from the Measures dimension meaning that all the other budgets and Variances for LastYear are hidden. Nothing is specified for "Fiscal Period" so any item from that dimension will come through.

Finally, in the "VarLastYr" block

We have specified the calculated item "VarLastYr" and then just the Actual from the Measures dimension meaning that all the other budgets and Variances for VarLastYr are hidden and in the "Fiscal Period" dimension selected only the calculated Item "YTThisMonth", ensuring that any other period selection is also hidden.

in summary, this leaves us with these columns

Based on these selections

giving us a grid that looks like this