Crosstab Rows displaying even when there is no data in the Crosstab

[guy222kool]

Hello,

I am using a Dimensional(DMR) package as the source for a Crosstab.
I have nested 2 Levels from a Dimension in the Crosstab rows and used a Level from the Time Dimension in the Crosstab Column and a Measure in the Crosstab Intersection.
The issue is, when there is no data for a particular Prompt selection, the Crosstab Columns are blank but the Crosstab Rows are displayed in the report output.

I have sorted the Levels from the Dimension(used in Rows) in the FM using the Member Sort Tab of the Dimension definition.
When I remove the Sorting in the Dimension, the whole Crosstab is blank for the particular Prompt selection(No data) and that is the expected behavior of the report.

But with the Member Sorting enabled, I had to explicitly specify suppression options for the  Crosstab to Suppress the Rows when there is no data.

I'm using C 10.1.1 and I'm not sure if this is a Cognos Bug. Please let me know if anyone else have faced a similar issue.

[tjohnson3050]

That is expected behavior.  In our environment, this is a common requirement.  We have dimensions that need to show wether or not there is data.  This is how reporting against OLAP cubes always works.

In FM when you have OLAP compatible sorting enable on a dimension, the default behavior is to show all members of the dimension.  Like you found, you can remove the OLAP compatible sorting and it will only display the members with values. 

You can also remove the members with no values by using suppression or by using MDX functions to filter out values with no data.

I have found that with the new Dynamic Query Mode (DQM), this behavior is different.  In order to enable the extra members to display with DQM there is a suppression property on the query in Report Studio that needs to be set to 'No'.

[guy222kool]

Hi tjohnson3050

Thanks for confirming this point..I'll keep this in mind in future when Modelling. :)