Text only | Text with Images

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Report Studio => Topic started by: lookingforK on 05 Dec 2012 02:21:42 PM

Title: How to write an expression in Filters for a crosstab to only show certain rows?
Post by: lookingforK on 05 Dec 2012 02:21:42 PM
Hi,

I am using Report Studio to extract data with a crosstab.

The layout of the crosstab looks like:
Store Number   [Current Week]   [Current Week of Prior Year]   [YTD]   [YTD of Prior Year]
***1            
***2            
***3            
......            

The intersection values should be Sales figures.

I want to add a filter upon this crosstab, and its purpose should be:
If the sales figure for [Current Week] is null or 0, don't dsplay the row for the store. In other words, the crosstab only show the rows that have actual sales in [Current Week].

For example, if the original data is:
Store Number   [Current Week]   [Current Week of Prior Year]   [YTD]   [YTD of Prior Year]
***1                        1003                                  987                     50678          50232
***2                                                                 1123                                        55456
***3                        1102                                 1027                     57231          56763
......                         ......                                  ......                     ......           ......

The crosstab should generate:
Store Number   [Current Week]   [Current Week of Prior Year]   [YTD]   [YTD of Prior Year]
***1                        1003                                  987                     50678          50232
***3                        1102                                 1027                     57231          56763
......                         ......                                  ......                     ......           ......


How to write the expression in Filters?


Thank you in advance.
Title: Re: How to write an expression in Filters for a crosstab to only show certain rows?
Post by: CognosPaul on 05 Dec 2012 11:03:37 PM
Is this a relational or dimensional source?

Is there a reason you're using a crosstab over a list? Are the Current Week, Current Week of Prior Year, etc. coming from a single field or fields from a different table (multiple levels of granularity in the fact) or different measures?

If it's a relational source, and Current Week is a distinct measure, try creating a filter with the expression [Current Week] > 0, set to after auto aggregation.
Title: Re: How to write an expression in Filters for a crosstab to only show certain rows?
Post by: Bark on 06 Dec 2012 03:24:46 AM
And to exend the information, if it is dimensional try to place in the rows a DataItem with something like:

filter([Store Number],tuple([Current Week],[Sales]) > 0)

or something like that, it will depend on the context.

Regards,

Bark
Title: Re: How to write an expression in Filters for a crosstab to only show certain rows?
Post by: lookingforK on 07 Dec 2012 10:56:28 AM
Thank you both.

It is a dimensional source.

Testing the method ...
Title: Re: How to write an expression in Filters for a crosstab to only show certain rows?
Post by: lookingforK on 07 Dec 2012 03:49:54 PM
Thank you both again,

Solved ...
Title: Re: How to write an expression in Filters for a crosstab to only show certain rows?
Post by: Lynn on 10 Dec 2012 02:48:29 PM
Quote from: lookingforK on 07 Dec 2012 03:49:54 PM
Thank you both again,

Solved ...

Does this mean you've found K and no longer need to look?
  :)
Title: Re: How to write an expression in Filters for a crosstab to only show certain rows?
Post by: MFGF on 11 Dec 2012 05:37:54 AM
Quote from: Lynn on 10 Dec 2012 02:48:29 PM
Does this mean you've found K and no longer need to look?
  :)

Lynn, you always manage to make me laugh! :D
Text only | Text with Images