Search code examples
excelmdx

MDX / Cubemember / Excel

Hopefully someone can help, I'm fairly new to MDX and am trying to create an excel template that will be updated automatically.

I have a basic cube setup and I have created a pivot table and have changed the pivot to show the formulas, I have then updated the formulas from A1 etc to the member name so that they can be moved into a different tab in excel.

The below shows my excel values 

A2 =CUBEMEMBER("cubeconnection","[Data Date].[Working Day In Quarter].&[64]")

B2 =CUBEMEMBER("cubeconnection",{"[Data Date].[Working Day In Quarter].&[64]","[Data Date].[Data Date].&[2018-12-31T00:00:00]"})

C2 =CUBEVALUE("cubeconnection","[Data Date].[Quarter Offset].&[1]","[Data Date].[Working Day In Quarter].&[64]","[Measures].[Closing Base]")

I want to be able to setup B2 to be dynamic and not include the reference "[Data Date].[Data Date].&[2018-12-31T00:00:00]" but still display the date as when the next quarter report runs the date won't be 2018-12-31 but a new date.

Is there a way to reference my workingdayinquarter field and bring back the member of datadate?

Solution

  • I think you could try EXISTS with a reference to cell A2 rather than hard-coding it into B2.

    EXISTS example from here: https://learn.microsoft.com/en-us/sql/mdx/exists-mdx?view=sql-server-2017

    SELECT [Measures].[Internet Sales Amount] ON 0,  
EXISTS(  
[Customer].[Customer].[Customer].MEMBERS  
, {[Customer].[State-Province].&[CA]&[US]}  
) ON 1   
FROM [Adventure Works];

    Cool reference to help with CUBE FUNCTIONS: https://powerpivotpro.com/2018/12/cube-functions-the-good-the-bad-and-how-to-make-them-great/

    So for B2 maybe try this:

    =CUBEMEMBER("cubeconnection","EXISTS( [Data Date].[Data Date].MEMBERS, {" & A2 & "}).ITEM(0).ITEM(0)")