SSRS Lookup Function Solves MDX Issue

Josh,

So you have an MDX query that you are using in an SSRS report. You need to show subtotals and totals, but there are a couple of issues:

1. The totals and subtotals are on averages which are difficult to total
2. The objects being counted could span multiple groups so that a sum on the report would not accurately match what's coming from the cube

The SSRS Lookup function can help solve this issue!

After you have created the main MDX query and Table/Matrix on the SSRS report, you'll want to create an MDX query that returns a dataset for each level of granularity needed.

So if the main MDX query is this:

SELECT
    {
        [Measures].[Sales Count],
        [Measures].[Sales Amount - Avg]
    } ON COLUMNS,
    {
        [Product].[Product].[All Products],
       [DimDate].[Fiscal Quarters].[All]
    } ON ROWS
FROM
    MyCube

If you wanted totals by Fiscal Quarter you would add a data set (i.e. "Totals") with just the Quarters in the ROWS:

SELECT
    {
        [Measures].[Sales Count],
        [Measures].[Sales Amount - Avg]
    } ON COLUMNS,
    {
       [DimDate].[Fiscal Quarters].[All]
    } ON ROWS
FROM
    MyCube

Then in the appropriate Textbox Expression on the report, the lookup function would look like this:

=Lookup(
   Fields!Detail_Fiscal_Quarter.Value,
   Fields!Total_Fiscal_Quarter.Value,
   Fields!Total_Sales_Count.Value,
   "Totals"
)

Here are the parts of the lookup function:

1. "Fields!Detail_Fiscal_Quarter.Value": The first argument is the value to lookup in the new dataset. In this case it is a field value of the dataset bound to the tablix.
2. "Fields!Total_Fiscal_Quarter.Value": The second argument is the index column on the new dataset. This will indicate which row of the new dataset ("Totals") to use.
3. "Fields!Total_Sales_Count.Value": The third argument indicates which column of the new dataset to return. This will be the value displayed in the final calculation
4. "Totals": The last argument indicates which SSRS dataset to perform the lookup on.

So basically, the function takes the relevant Detail_Fiscal_Quarter value, searches the "Totals" dataset for a row with a matching value in the Total_Fiscal_Quarter column, and once that row is selected it returns the Total_Sales_Count.