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
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.