Surpress NULL values in Report Studio for Dimensional Model.

[vincydza@gmail.com]

Hi Cognos Experts,

I am using Cognos 10.2 and Dimensional Query  model  to produce crosstabs report in Report Studio.

The format of the report is as follows.

Revenue                                               Year 2014
Product Line  | Product Type |            ###,###.##

Default measure is Revenue.

Product Type is a query calculator and expression  applied on this columns is as follow.

Filter([GOSALES cube].[All Products].[All Products].[Product Type], [Revenue]<> NULL )

When I run the report I find  few product types with blank/NULL  Revenue value for 2014.  Further investigation revealed that this product  type is having revenue values in Year 2013 however it does not have revenue values for 2014. This could be the reason for null values showing  for these product type in  2014

How should filter expression be tweaked so that it suppresses product types that does not have revenue values is 2014.

Please Advise.


Vince

[MFGF]

Hi Cognos Experts,

I am using Cognos 10.2 and Dimensional Query  model  to produce crosstabs report in Report Studio.

The format of the report is as follows.

Revenue                                               Year 2014
Product Line  | Product Type |            ###,###.##

Default measure is Revenue.

Product Type is a query calculator and expression  applied on this columns is as follow.

Filter([GOSALES cube].[All Products].[All Products].[Product Type], [Revenue]<> NULL )

When I run the report I find  few product types with blank/NULL  Revenue value for 2014.  Further investigation revealed that this product  type is having revenue values in Year 2013 however it does not have revenue values for 2014. This could be the reason for null values showing  for these product type in  2014

How should filter expression be tweaked so that it suppresses product types that does not have revenue values is 2014.

Please Advise.


Vince

How about:

Filter([GOSALES cube].[All Products].[All Products].[Product Type], tuple([Your 2014 year member],[Revenue]) is not NULL )

MF.

[vincydza@gmail.com]

Thanks MFGF, recommended solution has resolved the issue. 

you are the best.  :)

Vince

[vincydza@gmail.com]

Hi MFGF


The report had to be changed to add 'Selected Week'  this is a user prompt and the user selects the week i.e. Week 18,2014,  There are two additional Columns 'Month to Selected Week' and 'Year to Selected Week'.

When I add the filter criteria as you mentioned if there is no revenue for Selected Week then that record is getting suppressed, However if there is revenue for 'Month to Selected Week' and no revenue for the 'selected week' then that record is also getting suppressed from the report the same is happening for 'Year to Selected Week' column.

The Revenue for 'Month to Selected Week' and 'Year to Selected Week is valid and should show on the report if there is no revenue for 'Selected Week' revenue.

Please advice

Vince

[vincydza@gmail.com]

fyi,

I have used the PeriodsToDate function to calculate the 'Month to Selected Week' value, and then taken an aggregate of the selected set.

What I have observed, if i revert back to my original expression then the values for 'Month to Selected Week' and 'Year to Selected Week' are displayed on the report even if there are no revenue for 'Selected Week'

Please advise

Vince

[vincydza@gmail.com]

Any Update on this issue?

Regards

Vince

[MFGF]

Hi MFGF


The report had to be changed to add 'Selected Week'  this is a user prompt and the user selects the week i.e. Week 18,2014,  There are two additional Columns 'Month to Selected Week' and 'Year to Selected Week'.

When I add the filter criteria as you mentioned if there is no revenue for Selected Week then that record is getting suppressed, However if there is revenue for 'Month to Selected Week' and no revenue for the 'selected week' then that record is also getting suppressed from the report the same is happening for 'Year to Selected Week' column.

The Revenue for 'Month to Selected Week' and 'Year to Selected Week is valid and should show on the report if there is no revenue for 'Selected Week' revenue.

Please advice

Vince

Hi,

You don't make it clear exactly what the rules are for showing or suppressing product type member rows. You now have three different measures? Have you tried an expression that ORs them in the filter? If I could follow what you are doing I could perhaps try to replicate this in my sample cube, but (typically for a muppet) I'm somewhat confused... :)

MF.

[vincydza@gmail.com]

Hi MF,

No I have not tried an expression that ORs them in the filter, can you please forward me the expression.
The format of the report is as follows  'Selected Week' is a report parameter and in the below format user has selected 'Week 18,2014'

Revenue                                       Week 18, 2014       Month To Selected Week      Year to Selected Week
Product Line | Product Type                2,322.43                             56,595.00                           545,595.67

Regards

Vince


Vincent

[vincydza@gmail.com]

Hi

How can I write am expression that OR's them in a filter  so that null values are excluded from the report. Pleas advise.

Regards

Vince