Now we are chest-down in DMR we run into some issues that may (or not) be considered quirks.
Consider a regular dimension 'Project' consisting of 1 hierarchy and one level (also 'Project'). To avoid duplicates clashing I have set the Project key (meaningless integer) as the businesskey and the Projectname as Caption.
When I use this in a context filter for just one member, then all member values show, but only facts for the chosen member remain. This is not desired as it should limit the report to just one Project.
When analyzing the generated SQL I notice a seperate statement for the member value; nothing is added to the where clause of the main SQL.
When I deviate from best practice and use Projectname for both _businesskey and _caption then the main SQL is neatly filtered by the added part in the where clause giving the required effect.
(and no additional statements are generated)
Since name may not be unique it looks like I will be forced to add the meaningless key to the name for the sake of uniqueness.
I am overlooking the obvious, or is this a bit of a quirky bug?
Added:
To make things even a little more complex, we are experiencing this type of behavior (not adding the correct filter to the where clause) only in cases where the object in the contextfilter is also in the AS crosstab
Using an integer key as a business key works for me. It correctly filters and shows only what I choose.
You said the Project Key is integer . Did you mark that in the Query Subject as an 'Identifier' or an 'Attribute' before using that in the DMR?
The Project key is an integer and usage is set to identifier.
To verify against 'old' Powercubes I took a cube for testing purposes with the same structure. I experience the same behavior as in DMR:
When I add Project to the crosstab row and use Project in the context filter (and select just one Project) the crosstab still displays all Projects with only measures for the selected Project.
However, activating null suppresion then gives the expected result and just the one project remains.
I guess this is really a matter of 'Works as Designed' from a Cognos perspective..
I don't know why the filter shows all. It works for me.
You probably already know this. Creating a filter for an OLAP source will have a different definition as compared to a relational model.