I have created a pivot report with a few dimensions such as Storename, Storenumber and SupplierKey. If you look at the Excel file, it will show you how the Pivot is set up. SupplierKey is the dimension across the TOP of report. This is what I want to do (refering to the Excel file). The file is annotated to show expressions etc.
I have selected SupplierKey X1, X2 and X3. There are maybe a 100 other SupplierKeys. I want to list first of all the sum of sales for a chosen period (via set analysis) for the selected SupplierKeys. And show a total for selected SupplierKeys. This works fine as you can see in the columns "Sales Period 1" X1, X2 and X3.
Additionally, I want to show the Grand Total of all SupplierKeys ignoring the SupplierKey selection. I achieve this currently by setting an ignore in my set_analysis (see expression 2). This is reflected in the column Grand Total Sales Period 1. The value for the Grand Total is correct. The non-selected SupplierKeys are indicated by the columns marked Y1 - Yn....
Although I want to have the Grand Total over all SupplierKey, I do not want the columns (Y1 through Yn) to show at all if possible. I would at least like to Suppress the Column "Sales Period 1" for non-selected SupplierKeys.
I have been toying with the enable settings giving conditional expressions, but to no avail.
Is there a way to do this? Basically I think I am wanting to say "if this SupplierKey is selected, show the column, otherwise just add to Grand Total for all SupplierKeys".
Thanks for looking!