Suppress Columns in Crosstab

[tallsup336]

Hello

I have a question and hoping someone can help with this.  We are using Cognos 10.2.1 and I am creating a report in Report Studio.  We have a crosstab from dimensional data and everything works fine as long as a user selects the entire year. 

Our cross tab looks like this:
                                                   2015                                                                       
                                                   2015 Q1  2015 Q2  2015 Q3  2015 Q4   
Subject Area 1                            100          125         75            20             
Subject Area 2                            85            45           35            55             
Total

I have suppression turned on for Columns for Missing Values.  Therefore 2010, 2011, etc do not show up on the crosstab as we do not have any data for these years.  But if the user only select 2015 Q4 on the prompt, the crosstab only shows the subject areas and appears to suppress all quarters and years.  If I remove Year from the crosstab, the quarter is shown.  But the users want to see the grouping by years.

Is there a way to use zero suppression and still display the year value?  Any help would be appreciated. 
Tina
     

[MFGF]

Hello

I have a question and hoping someone can help with this.  We are using Cognos 10.2.1 and I am creating a report in Report Studio.  We have a crosstab from dimensional data and everything works fine as long as a user selects the entire year. 

Our cross tab looks like this:
                                                   2015                                                                       
                                                   2015 Q1  2015 Q2  2015 Q3  2015 Q4   
Subject Area 1                            100          125         75            20             
Subject Area 2                            85            45           35            55             
Total

I have suppression turned on for Columns for Missing Values.  Therefore 2010, 2011, etc do not show up on the crosstab as we do not have any data for these years.  But if the user only select 2015 Q4 on the prompt, the crosstab only shows the subject areas and appears to suppress all quarters and years.  If I remove Year from the crosstab, the quarter is shown.  But the users want to see the grouping by years.

Is there a way to use zero suppression and still display the year value?  Any help would be appreciated. 
Tina
     

Hi,

What mechanism are you using in the report to apply the member selected in the prompt? A detail filter? A dimensional expression? Can you give us the expression?

Cheers!

MF.

[tallsup336]

The report does not have any detail or summary filters.  It is using a slicer.  The slicer is defined as:
    set([BI_Cognos_Content_Audit].[Run Date].[Run Date] -> ?parameter_RunDate?)

parameter_RunDate is set on the prompt page when the user selects the Year or Quarters that they want to view on the report.  I've tried adding a detail or summary filter with no success.

[MFGF]

The report does not have any detail or summary filters.  It is using a slicer.  The slicer is defined as:
    set([BI_Cognos_Content_Audit].[Run Date].[Run Date] -> ?parameter_RunDate?)

parameter_RunDate is set on the prompt page when the user selects the Year or Quarters that they want to view on the report.  I've tried adding a detail or summary filter with no success.

Hi,

A slicer will affect the measure values shown in the cells of your report, but it won't affect the members shown in the rows or columns. What you need to do here is to take the slicer expression and use it in a query calculation, replacing the members you are currently using in columns area of your crosstab (then delete the slicer). You then won't need to do any suppression of zeros, and your report will run much more efficiently.

Cheers!

MF.

[tallsup336]

I have tried using the slicer expression and using it in a query calculation, replaced the members you are currently using in columns area of your crosstab and then deleted the slicer. This works except for when quarters from multiple years are selected.

Is there a way in the tree prompt to only display Year and Quarter prompts?  The report is supposed to show the Quarterly Average.  But the tree prompt will allow the user to see the months or days.  This allows them to display monthly values.  The report sub-header states "Quarterly Average by Subject Area." 

I'm assuming there is no way to force the report to show the year and quarter dimensions only on this crosstab.  I will research a few of the dimensional functions tonight.  But would like to get a jump start on this report. 

[MFGF]

I have tried using the slicer expression and using it in a query calculation, replaced the members you are currently using in columns area of your crosstab and then deleted the slicer. This works except for when quarters from multiple years are selected.

Is there a way in the tree prompt to only display Year and Quarter prompts?  The report is supposed to show the Quarterly Average.  But the tree prompt will allow the user to see the months or days.  This allows them to display monthly values.  The report sub-header states "Quarterly Average by Subject Area." 

I'm assuming there is no way to force the report to show the year and quarter dimensions only on this crosstab.  I will research a few of the dimensional functions tonight.  But would like to get a jump start on this report.

Hi,

What is the issue when quarters from multiple years are selected?

If you're using a tree prompt, you have no way of preventing users from selecting members from anywhere in the hierarchy. It might be better to use a couple of value prompts - one for Year members and one for quarter members? The query calculation driving the Year prompt would be set([BI_Cognos_Content_Audit].[Run Date].[Run Date].[your year level] -> ?parameter_Year?) and for the Quarter query calculation it would be set([BI_Cognos_Content_Audit].[Run Date].[Run Date].[your quarter level] -> ?parameter_Quarter?). In the query retrieving quarter members for the quarter prompt, you could use an expression descendants([your Year query calc from above], [your quarter level]) so that the quarter prompt only displays members belonging to the year member(s) already chosen.

Cheers!

MF.