Optional filtering in cube-based report

[jonwebb]

Hi,

2 questions on filtering using a dimensional data source (Cognos powercube)

1) I want to create an optional filter using Report Studio 8.4 but have been advised that using 'detail filter' functionality when using a cube as the report's data source leads to poor performance. I've created a context filter by dragging a dimension member to the context filter area at the top and amending the properties to prompt > prompt on hierarchy. Is there anyway to make this context filter optional? Also, is there any way to amend the parameter name that is generated from the default "PSLICE_QUERY_1_0" style name which isn't too helpful when creating lots of filters!

2) As an alternative to the above, I've attempted creating a filter by adding a data item to my query with the expression set([Work Cube].[Supporter].[Supporter]->?Supporter?) which I want to use to filter the data. If I include this column in my crosstab on the report, I am prompted to resolve the parameter. When it is not in the crosstab but is in the query, Report Studio ignores it. Is there a way to enforce the use of this? I don't actually need to see it in the report... there will be around 5 filtered dimensions which don't need to be displayed in the crosstab which is generated.

Thanks in advance,
Jon

[david.stachon]

Good questions...

1. Context filters can't officially be "optional" ...however; you can use a macro to have the same effect: (check out the "prompt" function and the 'memberuniquename' data type from the help guide)

Code Select Expand

(#prompt('WhichLevel', 'memberuniquename', '[goSalesAgain].[PRODUCT1].[PRODUCT].[PRODUCT(All)]->[all]')#)


...if no value is provided by the user for the "WhichLevel" parameter, it will supply the "all" member (3rd argument in the prompt function). By supplying the "All" member, essentially no filter is being applied.

2. Your "Sets" as data items won't filter the cube unless it's used in your cross-tab. You can go with the same approach as #1.

NOTE: It's always worth giving the detailed filters a try. Often the performance is fine, and they tend to be more intuitive (especially with a SQL background)