Tuesday, February 9, 2010

Lists and Crosstabs

Lists and Crosstabs work differently with DMR. My requirement is to allow the user to select any member from the Product Hierarchy and the report should display the members in the selected level along with the children of each such member and the corresponding revenue.

Basically if the user selects any Product Line then the report should display Product Line, Product Type and Revenue or if the user selects any Product Type then the report should display Product Type, Product and Revenue.

I have created 2 data items for the same -

Parent - members(level([great_outdoors_company].[Products].[Products] -> ?Parent?))
Child - descendants([Parent],1)

and included Revenue measure.

When I drag the same into the crosstab the results are perfect while a list report based on the same query results in no data.

Has anyone else come across this issue?

[Update] The same can be achieved in List by creating the expressions using a Calculated Member rather than a Query Calculation. This feature is available 8.2 onwards.

No comments: