For the following Crosstab (District & Store are part of the Store Hierarchy; Department is part of the Product Hierarchy):
WTD
Sales
TY LY
District Store Department
A 100 1 100 90
A 101 2 150 120
B 200 1 null null
There are a lot of null values.
I need to remove the null rows, but adding Suppression on the Crosstab causes performance to become incredibly slow.
What is the best way to do this? I've seen something about filter calculations but I am not sure of the syntax.?
Also, I already have slicers on the query based on District & Department prompts.
Thanks in advance,
Adam.
Hi Adam,
Yes, you can try to improve performance with the filter function.
You have to change the [Department] level in your crosstab with a calculated data item contain the following expression filter([Department];[TY] is not null OR [LY] is not null) - where [Department] is the most detailed hierarchy level you want to use in the crosstab and [LY] and [TY] are the measures.
This calculated item will work fine together with slicers and won't cause any problem.
NoRkoS