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
Quote from: vincydza@gmail.com on 08 May 2014 02:07:16 PM
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.
Thanks MFGF, recommended solution has resolved the issue.
you are the best. :)
Vince
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
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
Any Update on this issue?
Regards
Vince
Quote from: vincydza@gmail.com on 09 May 2014 10:04:54 AM
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.
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
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