MDX calculated column based on user defined value

Question

I am trying to produce an Excel PivotTable which displays and compares Sales Rep revenue figures based on values specified by the user, for example, the InvoiceYear.

This data is coming from an SSAS cube and I have connected to it using PowerPivot. I have columns from the cube in the data model and I am attempting to edit the MDX code in the Table Properties window to create some new columns.

This works so far, but the InvoiceYear value is hardcoded to '2010' at the moment. I need a way for this code to accept a value entered into a cell by the user in the excel worksheet itself.

I am just a beginner at this, so I am not aware if this is even possible.

If this approach is NOT possible, I really need to find another way to do it. Come at it from the other side and refresh the column whenever the cell changes via a macro or vba? Some other solution?

Here is what I have in the Edit Table Properties window. This works correctly, but I can't have the Year value hardcoded like that or users will not be able to specify a custom Year value, obviously. I have tried everything I can think of to reference a cell in a Sheet, but the column usually just appears empty with no errors.

WITH MEMBER RentalSales AS
    CASE when [SalesRep_Dim].[InvoiceYear].currentmember.membervalue = '2010' then [Measures].[LineAmountMST] else 0 end

SELECT NON EMPTY { Measures.RentalSales, [Measures].[LineAmountMST] } ON COLUMNS,  
NON EMPTY { ([SalesRep_Dim].[InvoiceMonth].[InvoiceMonth].ALLMEMBERS * [SalesRep_Dim].[InvoiceYear].[InvoiceYear].ALLMEMBERS * [SalesRep_Dim].[SaleRep].[SaleRep].ALLMEMBERS * [SalesRep_Dim].[ItemGroup].[ItemGroup].ALLMEMBERS * [SalesRep_Dim].[Site].[Site].ALLMEMBERS * [SalesRep_Dim].[Source].[Source].ALLMEMBERS * [SalesRep_Dim].[Type].[Type].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM ( SELECT ( -{ [SalesRep_Dim].[InvoiceMonth].&[]&[0], [SalesRep_Dim].[InvoiceMonth].[All].UNKNOWNMEMBER } ) ON COLUMNS FROM [GMFSalesTransRepRevenue]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Answer 1

I don't believe it is possible with your current approach. However if you switch to using Power Query to get data from SSAS and load to Power Pivot it should be possible.

Power Query can query SSAS as described here. The you read a cell in the Excel workbook that the user has entered and filter the data coming from SSAS in the Power Query.

Answer 2

You should look into slicers, they are more appealing visually and you can use them to filter multiple pivot table with one selection.

Setup a report filter and setup your slicer to the same attribute, you can use the Cell for your formula references and use the slicer for user input.

This should be as easy as selecting your pivot table, going to the Insert excel Tab, under Filter there show be the "Slicer".

Select the attribute you are trying to filter by, and your slicer is done.

Answer 3

There is an option to create a write-back measure. You may convert its value to calculated member which takes place in your pivot table. However it's not a best practise. I'd recommend to avoid this method.

Steps:

1. Create a view (one column int, for example).
2. Crate a measure group from this view.
3. Add a partition with writeback settings, see: http://bidn.com/blogs/DevinKnight/ssis/1768/ssas-creating-and-using-a-writeback-measure-group
4. Create a calculated measure, for example:

(StrToMember('[SalesRep_Dim].[InvoiceYear].&[' + Cstr([Measures].[WriteBackMeasure]) + ']'), [Measures].[LineAmountMST])

where [Measures].[WriteBackMeasure] is your writeback measure and if [Measures].[WriteBackMeasure] = 2010 then you'll get the following:

([SalesRep_Dim].[InvoiceYear].&[2010], [Measures].[LineAmountMST])

5. Open two pivot tables: one with [Measures].[WriteBackMeasure] only, another is your report with the calculated member.