If you are unable to create a new account, please email support@bspsoftware.com

 

How to write an expression in Filters for a crosstab to only show certain rows?

Started by lookingforK, 05 Dec 2012 02:21:42 PM

Previous topic - Next topic

lookingforK

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.

CognosPaul

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.

Bark

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

lookingforK

Thank you both.

It is a dimensional source.

Testing the method ...


Lynn


MFGF

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
Meep!