Dimensional filter on field in report

[mickseven]

Hi, I have a crosstab report that displays Product, Platform and Platform Version in the rows section. My column is a date field and measure is a user count. I need to only display rows where Platform Version is above a certain value, i.e. 7.0. My data is dimensional, coming from a SSAS cube. I've been reading in this forum that it's not correct to use a detail filter with dimensional data. However that is the only way I have been able to correctly filter the data the way I want. The report actually runs pretty quickly, but I would like to know the proper way to do this.  Thanks.

[charon]

Hi Mick,

detailfilters are not the best way to do it (actualy, cognospaul explained in one of his great bcognos blog articles why the result set might even be wrong) because its a pure relational concept, filtering the rows on the detaillevel for your expression.
In case of olap (cube or dmr) you might have several options:
1)  Create a (custom) set of members of the platform dimension. YOu are able not only to define which members are part of the set but also you can exclude specific members. this way is a good solution as long the amount of members in the set as well as the ones you want to exclude is not to big.
2) Of course, you can create "dimensional filter". By that i mean you can create filter expressions in the slicer section (under detail filter).
3) Last but not least, what you can do as well is create a data item with a filter expression (look up the dimensional "filter" function in cognos) for that condition. The filter expression runs pretty fast in my experience...

gl and regards
charon :P

[mickseven]

Hi Charon,
    Thanks for your reply. My understanding is that slicers only apply to measure data. I've been trying data items with the filter function but it doesn't seem too work. This is what my crosstab looks like:

                                                                                           11/5/2013
     Product               Platform              Platform Version

       WS                       A                           6.3                            25
       WS                       A                           7.0                            50
       WS                       A                           7.1                            75


I want the rows with Platform Version < 7 to not appear on the report (row 1 here). I know the filter function syntax is filter(set,expression).  My expression would be something like Platform Version >= '7'. I don't know what to use for the set parameter. I've tried various objects, the report runs but no filtering is happening. FYI- I'm very new to dimensional reporting in Cognos. I appreciate your help. Thanks

[Francis aka khayman]

1. you can drag members you want displayed to the slicer area:

ex:    [7.0], [7.1]

2. you can drag members to Platform Version instead of Platform Version level:

ex:
instead of your Crosstab like this:

Product                                                   Platform                                                  Platform Version
[Hierarchy].[Whatever].[Product]           [Hierarchy].[Whatever].[Platform]        [Hierarchy].[Whatever].[Platform].[Platform Version]     

make it like this:

Product                                                   Platform                                                  Platform Version
[Hierarchy].[Whatever].[Product]           [Hierarchy].[Whatever].[Platform]        [7.0]
                                                                                                                            [7.1]
*** this fells very much like 'hard coding' in relational

3. you can do a filter:

filter([Product], [Platform Version] >= 7)

**** i'm not sure about this though.... your [Platform Version] have to be converted to a number or something... i'm just new to dimensional as well..
     

[mickseven]

Thanks khayman. Appreciate your response. Unfortunately, due to the large numbers of products, versions we have, I won't be able to hard-code version numbers. I've tried item 3 from your post with various values for the first parameter that it takes. It's weird, the report runs with no errors, but there is also no filtering happening. I will keep trying and post if I find a solution.

[Francis aka khayman]

1. try to filter [Platform] as well ... ex filter([Platform];[Platform Version] >= 7)

if this doesn't work you might want to redesign your  dimension...

2. you can add a role for each Platform Version (greater than 7? yes or no) and use filter with roleValue

3. you can make [Platform Version] a level higher than [Platform] (???not sure if this is a good idea??? :) hopefully there is not too many [Platform Version] you can drag them as context filter

... i'm running out of bad ideas :(

[mickseven]

Thanks khayman. I appreciate your help. I haven't worked with roleValue yet. Will give that a try today.

[mickseven]

khayman,  Thank you so much. Your suggestion to use roleValue worked perfectly. Really appreciate your help.