Our Cognos BI is 10.2, we have 5 levels in a Product Dimension of our cube:
Department, Product Category, Product Group, Product Line, Item
In our report the prompt lets the user to choose (checkbox) the Product Group and Product Line. The Items will then be retrieved depending on the Product Lines chosen.
The Crosstab will look like this:
[Month]
[Qty] [Amount]
[Department] [Product Category] [Product Group] [Product Line] [Item]
My question is how to filter [Department] to include only the [Item] derived from the chosen [Product Line]
I think I can do detail filter but I am putting that off as one of the last resorts
** Note, I will proceed to choose the Top ten by sales but I need to figure out how to display like this first.
Quote from: khayman on 13 Apr 2015 10:32:16 PM
Our Cognos BI is 10.2, we have 5 levels in a Product Dimension of our cube:
Department, Product Category, Product Group, Product Line, Item
In our report the prompt lets the user to choose (checkbox) the Product Group and Product Line. The Items will then be retrieved depending on the Product Lines chosen.
The Crosstab will look like this:
[Month]
[Qty] [Amount]
[Department] [Product Category] [Product Group] [Product Line] [Item]
My question is how to filter [Department] to include only the [Item] derived from the chosen [Product Line]
I think I can do detail filter but I am putting that off as one of the last resorts
** Note, I will proceed to choose the Top ten by sales but I need to figure out how to display like this first.
Hi,
Assuming you are prompting for Product Group and Product Line using dimensional expressions in query calculations such as
[Your Product Group level] -> ?Prod Group parameter?
[Your Product Line level] -> ?Prod Line parameter?
This would then mean you are returning a single member from the Product Line level. You can retrieve the Department and Product Category using the ancestor() function in query calculations
eg
ancestor([your Product Line query calculation], [your Department level])
ancestor([your Product Line query calculation], [your Product Category level])
Cheers!
MF.
Since khayman indicated that they're using a checkbox prompt, and judging from the use case itself, I suspect that the product line is multi select.
Assuming you are using proper dimensional expressions to filter, such as:
set([your product line level]->?pLine?)
you won't be able to use the ancestor function, because it expects a member. Rather, you'll have to filter "across the measure", with an expression such as this one:
filter([your department level],total([measure] within set [selected product lines set])>0)
awesome!
i encountered a minor issue though. somehow using set([your product line level]->?pLine?) forces my multi select prompt (checkboxes) to become radio buttons.
so i used #promptmany# instead and worked like magic.
but it was just weird while i was trying out the same approach, filter([department],[somekind of measure] > 0), it was making no sense to me. but with your suggestions everything just suddenly became crystal clear.
thanks again.
I should probably point out, for the sake of people of the future (Hi people of the future! Please tell me you cracked cold fusion by now!) this isn't a very elegant solution.
Filtering "across the measure" (i.e. using a measure totalling to over 0 to filter between one node and another) isn't natural in MDX (In MS cubes they have the nonempty function for that, for instance). It's imposing relational thinking on dimensional structure. It works, but it lacks finesse. It also cross joins all the nodes which in large dimensions can be a strain.
The more natural way to build this sort of a report would be to filter the entire hierarchy using a tree prompt, and displaying the entire hierarchy expanded (This is also better performance-wise since expanding is more cost effective then cross joining nodes). But I assumed you had a good reason not to do that, so we opted for the brute force option. Glad I could help.