For reference, I have attached a sample report based on the "Sales and Marketing (cube)" sample. It is a very straightforward list report of Product Line, Product Type, Product, and Quantity. There is a filter defined with "[sales_and_marketing].[Products].[Products] in ?pProductLine?". The prompt page has a multi-select tree control populated by a query with data item "[sales_and_marketing].[Products].[Products].[Product line]".
Yes *I* know you should not use detail filters on cubes, but this is to emulate a portion of a user-created report, so it's not my fault. :D
My question relates to the selection of multiple categories within the same hierarchy. This works fine when they are on the same level, e.g. select "Golf Equipment > Irons" and "Mountaineering Equipment > Climbing Accessories". Now switch from "Climbing Accessories" to child "Granite Caribner" and leave "Golf Equipment > Irons" in place. You'll get everything on all levels. Would this be expected behavior? It seems to me it could work, but it clearly does not.
The real report is an org tree. There are people who want multiple departments for a particular report, and they are not always on the same level. Yes I have suggested they simply run the report multiple times using single-select, but they asked me to look into this so here I am.
Am I missing an obvious solution, apart from multiple report runs?
You've got two problems here.
The first you already know. Shame shame shame!
Second is the list. Lists are best suited for relational data sources, when using a cube, try to use a crosstab.
Let's take a look at the MDX for the two examples (I'm removing the total row to make it easier to understand).
With the list:
WITH SET [COG_OQP_INT_s1]
AS 'CROSSJOIN(
HIERARCHIZE(
UNION(
UNION([Products]..[Product line 2].MEMBERS, [Products]..[Product type 3].MEMBERS, ALL)
, [Products]..[Product 4].MEMBERS, ALL)
)
, {[MEASURES]..[Quantity]})'
SELECT
[COG_OQP_INT_s1] DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(0)
FROM [sales and marketing]
I selected the exact members you mentioned in your post. Notice something missing? It's completely ignoring the detail filter! I'm honestly not sure why. Best to raise a bug with IBM.
Next, let's convert the list to a crosstab and check the MDX:
SELECT
{[MEASURES]..[Quantity]} DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(0)
, GENERATE(
INTERSECT(
[Products]..[Product line 2].MEMBERS
, GENERATE(
{[Products]..[@MEMBER].[50], [Products]..[@MEMBER].[968]}
, {ANCESTOR([Products].[].CURRENTMEMBER, [Products]..[Product line 2])}
)
)
, UNION(
{([Products].[].CURRENTMEMBER)}
, GENERATE(
INTERSECT(
DESCENDANTS([Products].[].CURRENTMEMBER, [Products]..[Product type 3])
, GENERATE(
{[Products]..[@MEMBER].[50], [Products]..[@MEMBER].[968]}
, UNION(
UNION(
DESCENDANTS([Products].[].CURRENTMEMBER, [Products]..[Product type 3])
, {ANCESTOR(
[Products].[].CURRENTMEMBER
, [Products]..[Product type 3]
)}
, ALL
)
, GENERATE({[Products]..[@MEMBER].[50], [Products]..[@MEMBER].[968]}
, {ANCESTOR([Products].[].CURRENTMEMBER, [Products]..[Product type 3])}
)
)
)
)
, UNION(
{([Products].[].CURRENTMEMBER)}
, INTERSECT(
DESCENDANTS([Products].[].CURRENTMEMBER, [Products]..[Product 4])
, GENERATE(
{[Products]..[@MEMBER].[50], [Products]..[@MEMBER].[968]}
, UNION(
DESCENDANTS([Products].[].CURRENTMEMBER, [Products]..[Product 4])
, GENERATE(
{[Products]..[@MEMBER].[50], [Products]..[@MEMBER].[968]}
, {ANCESTOR([Products].[].CURRENTMEMBER, [Products]..[Product 4])}
)
)
)
)
, ALL
)
, ALL
)
, ALL
)
, ALL
) DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(1)
FROM [sales and marketing]
The formatting on that may be a bit off, but we can clearly see that it is taking the correct values.
I still need to shameeducate the users on the evils of detail filters for dimensional data.
I kind of suspected it might be a bug, but wanted to get some feedback before reporting it to IBM. So I will report that.
I had not thought about converting it to a crosstab. Personally I almost always use crosstabs for dimensional data so good call. I will try taking the original and see if I can convert it and make it work. Good idea. :D
As always, thanks PaulM!
If you want to avoid the nested cells, you could also try using the following expression:
hierarchize(
union(
union(
generate([Prompted],ancestor(currentMember([sales_and_marketing].[Products].[Products]),[sales_and_marketing].[Products].[Products].[Product line]))
,
generate([Prompted],ancestor(currentMember([sales_and_marketing].[Products].[Products]),[sales_and_marketing].[Products].[Products].[Product type]))
)
,
descendants([Prompted],[sales_and_marketing].[Products].[Products].[Product], beforewithmember self )
)
)
With another data item called Prompted with the expression
#promptmany('pProductLine','mun','[sales_and_marketing].[Products].[Products].[Product line]','set(','',')')#
That will work in both lists and crosstabs, but crosstabs have the automatic level indentations (remember to start after 1 level). For a list you'll need to manually set the padding with a variable though.
Hi,
Iam having the following issue.Initially my requirement was for a single select within the hierarchy.But now the requirement is multi-select within the hierarchy.
Here is my issue
I have a list report which has summary at the top and the detail at the bottom.There are prompt selections like current month,ytd etc.I have a tree prompt which i select by the name.For instance Peter has 5 levels in selection and Mark has 4 levels in selection.When i select just peter iam getting correct values in the report in both summary and detail which includes children as well.When i select both Peter and Mark my summary captures only Peter i.e the first selection.Whereas my detail captures both Mark and Peter children values.
The source for the report is a cube.
We use prompt macro,MDX functions ,Set,Descendants etc.:Summary and Detail uses the same query.I have slicers in place.
I have tried modifying using different functions.Nothing seem to work.
Also i use parent ( item( [Children], 0 ) ) (does not work)-THis is supposed to get all the selected values.-Summary
and descendants( set( #promptmany('Hierarchy','MUN' )# ), 1 )- this works fine - Detail
I tried using generate function.