Matrix SubTotals Visibility

I am creating a report using a matrix, and matrix subtotals.
I give the user the option of including the reporting hierarchy in their
reports. If they want to include it, I would like to hide the subtotal,
completely.
The reason for this is because if I include a hierarchy within a single
field of a report, the subtotals don't sum properly.
Is there any way to hide the entire subtotal field within a matrix report?
I have tried modifying the Hidden field (using an expression) of the
SubTotal textbox, but that only seems to hide the textbox itself, not
the subtotal fields.
Thanks,
NoahNevermind. Figured it out using InScope().
Noah
Noah wrote:
> I am creating a report using a matrix, and matrix subtotals.
> I give the user the option of including the reporting hierarchy in their
> reports. If they want to include it, I would like to hide the subtotal,
> completely.
> The reason for this is because if I include a hierarchy within a single
> field of a report, the subtotals don't sum properly.
> Is there any way to hide the entire subtotal field within a matrix report?
> I have tried modifying the Hidden field (using an expression) of the
> SubTotal textbox, but that only seems to hide the textbox itself, not
> the subtotal fields.
> Thanks,
> Noah

Matrix SSAS dimension/hierarchy order problem

I have a have a relatively simple SSAS cube that I'm trying to display in a matrix but the order of the data is not correct. The rows of the matrix are a time hierarchy and the query designer produces the correct MDX and when I run the MDX in the data tab, the order is correct. However in the report preview tab, the order is incorrect. Specifically, the time hierarchy looks like this Year-Month-FullDate. The report is ordering the Months like this 1,10,11,12,2,3,4,5,6,7,8,9. I ran the MDX query in Sql Studio and the order is correct, and I used Excel to consume the same cube and it produces the correct order.

The report is somehow treating the Month like character data instead of numeric, how do I fix this?

It sounds like your MDX query only retrieves the Member_Caption (which is the string label representation), but not the Member_Value property (which is the value representation with the original data type).

For example:

SELECT NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS, NON EMPTY { ([Due Date].[Month Number Of Year].[Month Number Of Year].ALLMEMBERS * [Due Date].[English Month Name].[English Month Name].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, MEMBER_VALUE ON ROWS FROM [Adventure Works DW] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Note that the .Value field property of RDL expressions always maps to the Member_Caption property value for dimensions (the member caption is always a string!). The additional Member_Value intrinsic dimension property is not mapped directly to any of the predefined RDL extended field property names. Therefore, in order to access the MEMBER_VALUE property in the report, you have to use the so-called field property collection syntax in the matrix group expression and sort expression to get the numeric value for the dimension property and specify the property name exactly as defined in the MDX query above (the property name is case-sensitive in this case):
=Fields!Month_Number_Of_Year("MEMBER_VALUE")

Additional information about MDX intrinsic member properties can be found here: http://msdn2.microsoft.com/en-us/library/ms145528.aspx

-- Robert

|||

Robert - that worked perfectly. Thanks a bunch!

To anyone else in the same situation, to get to the matrix group expression and sort expression right-click the matrix object in the report designer and select Properties. Goto the Groups tab and then select dimension member in question and click the Edit button. This brings up another window, goto the Sort tab and enter the expression.

matrix control parent child grouping

does anybody know how to properly wokt out a parent child hierarchy in a matrix control?

*i already know that i have to work with the 'edit group' button and then

the 'general' tab and the values 'group on' : field.uniquename and 'parent group' .parentuniquename

*but fot some reason i always have to include 2 extra rows (my hierarchy is on a row)

one with field.uniquename and one with field.parentuniquename, i can make the invisible though but i can't remove them, otherwise it won't work

is this a kind of bug?)

*but finally what i really can't find out is how to properly toggle the visibility

the idea is that the hierarchy is collapsed (i have more then 8 levels, and more then 10000 membders) and that by clicking on a parent , the children appear

(the standard intuitive behavour of parent child controls)

anybody any clues?

on my way i found posting:

Creating report based on parent-child dimension

i think it's about the same subject