I have a complex MDX query that includes in the row breakdown: (1) a breakdown by a multilevel organisation hierarchy, and (2) a breakdown by a simple spend-type attribute.  The bottom level of the organisation hierarchy is Budget Centre, and each Budget Centre has a one-to-one relationship with a spend type.

When the MDX is run in Management Studio, the output includes both an All level and the attribute level in the rows for each organisation, including each Budget Centre.  However, in SSRS both rows for each Budget Centre have the name of the singleton spend-type value.  Further, both show the level.ordinal value as 1.

There are what appear to be knock-on effects from this when the spend amounts are totalled, with the totals for Budget Centres grouped by Spend Type being faulty.

Any thoughts on exactly what is happening, and how the Budget Centre level can be made to conform with the other Organisational levels.

Hello ,

  Is it possible to post a screen shot of your report for further understanding and analysis ?

Sorna,

This screen shot shows the select clause of an MDX query run in Management Studio and some of the output.  The output includes a breakdown by spendtype for one organisational unit at organisational level 3, and a breakdown by spendtype for one organisational unit at organisational level 4.  Both of these show an 'All' spendtype in both the row breakdown and in the column output.  They also both have several rows for specific spendtypes.  The 'All' output is shown as having spendtype levelcount 0 while the specific breakdowns have spendtype levelcount 1.

There are also breakdowns for several organisational units at organisational level 5.  These have a one-to-one relationship with spendtype.  Again, the row breakdown shows the 'All' level and this time has one specific spendtype breakdown for each organisational unit.  What is unexpected is that in the column output instead of the 'All' levels there is a repeat of the specific breakdown and the spendtype level count is shown as 1 for both what should be the 'All' level and the single breakdown level.

The amounts shown in the output are in fact correct, as the 'All' level should equate with the single spendtype for organisational level 5 units.  However, there are also level 5 units that are totals for all the selected level 5 units, again broken down by spendtype, and the amounts returned for these are faulty.

Apoogies for illegibility of screen shot.  Hope this is better

Still not visible!  What the results pane shows id that the SpendType rows include 'All' for all organisational units.  However, in the column results 'All' is replaced by the only child spendtype for organisational units on level 5.  The spendtypelevel shows as 1 both for what should be the all level and for the child level.  For non-level 5 organisational units, the column results show 'All' as well as all children, and 0 as the level for 'All'.  The MDX Select clause is:

SELECT

ON COLUMNS,

nonempty( (fy, iif('0'/*@SwitchViews*/='1', Z_SET, X_SET), [Organisations].[Spend Type].members, [FieldCount].children), fp) ON ROWS

FROM [Projects] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

{fx, fa, fyy, fp,fq, fr, MEASURES.LEVELNAME, MEASURES.ORGNAME, MEASURES.ORGANAME, MEASURES.SPENDTYPENAME, MEASURES.SPENDTYPELEVEL, [Measures].[PivottedFinance], MEASURES.LEVELCOUNT, MEASURES.ORGANISATIONCOUNT }