Crosstab over Cube - Suppression of Rows kills performance

[adam_mc]

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.

[norkos]

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