Cognos 11 crosstab suppression issue

[Maddy]

Hi All,

   I'm having a crosstab where slicer is applied for 'rolling 13 month data'. Suppression is applied to suppress columns- zero and missing values. The crosstab column must display 13 m, but it's showing only 11 as 2 months have data count zero.
When I tried to remove suppression to show the zero records as that's the requirement. All the month-year data is coming along with 13 month data. All those extra months coming as zero.
Now I don't know how to filter only the 13 month data and not apply suppression.
Can anyone pls help. It's very urgent

[MFGF]

Hi All,

   I'm having a crosstab where slicer is applied for 'rolling 13 month data'. Suppression is applied to suppress columns- zero and missing values. The crosstab column must display 13 m, but it's showing only 11 as 2 months have data count zero.
When I tried to remove suppression to show the zero records as that's the requirement. All the month-year data is coming along with 13 month data. All those extra months coming as zero.
Now I don't know how to filter only the 13 month data and not apply suppression.
Can anyone pls help. It's very urgent

Hi,

Can you confirm what type of package you are using - relational or dimensional? As you mention I slicer I'm guessing dimensional, but we need to be sure to advise you appropriately.

Cheers!

MF.

[Maddy]

Yes. It's dimensional.

[MFGF]

Yes. It's dimensional.

Hi,

In that case, the best way to get the months you need to see in the columns is to use a dimensional expression rather than using the month level and trying to filter the members. You haven't indicated how you are isolating the current period to begin with (is it a prompt or is it the latest month that exists or is it being identified another way?) but let's make some assumptions.

To get the set of the latest 13 month members, you could first use the closingPeriod() function to isolate the latest period (month), and the lastPeriods() function to give you the 13 periods up to and including this member

eg

lastPeriods (13, closingPeriod ([your Month level]))

This assumes you are identifying the current period as being the latest month in your Month level.

Pop this expression into a query calculation, and then use this query calculation as the columns of your crosstab. Remove your filters and your suppression and see if this gives you what you need.

Cheers!

MF.