Measures on Rows
Issue: You want an MDX query that returns measures on rows and calculated members on columns. You get The Measures hierarchy already appears in the Axis0 axis error. That’s because calculated members added to measures are treated as measures and you cannot have the same dimension (Measures in this case) on different axes.
Solution: Let’s point out that the above-mentioned error doesn’t occur if you request dimensions on columns. For example, the following is a perfectly legit query:
select {[Date].[Calendar].[Calendar Year].&[2002],
[Date].[Calendar].[Calendar Year].&[2003]} on 0,
{[Measures].[Internet Sales Amount],
[Measures].[Reseller Sales Amount],
[Measures].[Sales Amount]
} on 1
from [Adventure Works]
In addition, you may be able to use the capabilities of the tool to rotate dimensions from columns to rows, such as using a crosstab report layout in Reporting Services. Interestingly, PivotTable in Excel 2007 lets you add measures on rows although behind the scenes Excel requests measures on columns but does an internal rotation to move the measure columns on rows.
However, sometimes you may need asymmetric columns that filter the results. This may force you to use calculated members on columns and measures on rows. Since you cannot put the calculated members on Measures, consider adding them to a dimension. The following query adds Column1 and Column2 calculated members to the Product.Category attribute. Notice that the first column filters the bikes sales for USA only.
with member [Product].[Category].Column1 as ([Product].[Category].[Bikes], [Date].[Calendar].[Calendar Year].&[2002], [Geography].[Country].&[United States])
member [Product].[Category].Column2 as ([Product].[Category].[Bikes], [Date].[Calendar].[Calendar Year].&[2003])
select {[Product].[Category].Column1, [Product].[Category].Column2 } on 0,
{[Measures].[Internet Sales Amount],
[Measures].[Reseller Sales Amount],
[Measures].[Sales Amount]
} on 1
from [Adventure Works]
Of course, you can use any tuple or aggregation function for the member definition. BTW, you can cheat SSRS to bypass the rule that insists that measures must be on columns and the entire validation and preparation goodness by manually entering the query in the report definition and defining the dataset fields and mappings.
