Use the Argument member_expression as a slicer to define the portion of the cube for which the aggregated value is returned.
A text stringĪ text string of a multidimensional expression (MDX) that evaluates to a member or tuple within the cube. The values in column E refer to the values in column D which are in the ‚ordinary‘ (German) date format which I have to convert to be suitable for the CUBE functions.The CUBEVALUE function returns an aggregated value from the cube. I can correctly format the date in the CUBE formula, or.But you have to pay attention to the correct formatting of the dates and here I have two possibilities: Linking the CUBE formula to the Excel cells is not very difficult. Step #3: Making the CUBE formula refer to cell entries While the first parameter of the CUBESET() function is the connection to the data model, the second parameter let’s me specify the period. The colon is particularly important because it defines a time period from the start value (January 1st 2018) to the end value (May 1st 2018). This function now returns correct values, but is not yet linked to the cells in Excel. Since I am not an MDX person, I explain the behavior of the CUBESET() function, related to my example, with my own non-technical words: It let’s me define a (seamless) time period, which I can send to the data model and let’s me retrieve my measure for this time period. A solution for the period January 1st 2018 to May 1st 2018 could look like this: Referencing time periods in CUBE formulas The thing about time periods in CUBE formulas is that I can’t create them in Excel logic (via >=/ <=), but have to resort to another CUBE function: On CUBESET(). But what about periods? Step #2: Reference seamless time periods in CUBE formulas
Olap cube excel example how to#
So I know at this point how to correctly reference date values in CUBE formulas. If I want to display the value of my measure „Sum Values“ for January 2nd in a CUBEVALUE() formula, the formula could look like this: Using the correct syntax for date references in my CUBE formula The correct syntax is thus structured as follows: „ YYYY-MM-DDThh:mm:ss„, where the time here always contains zero values. It shows how a date reference should be used in a corresponding CUBE formula. The interesting part of the formula is the one marked red. With the 2nd of January 2008 selected, you can see this formula: How excel references date values in CUBE formulas With the PivotTable selected to Analyze in the ribbon and choose OLAP Tools → Convert to formulas.If the rows of the PivotTable are grouped by years, quarters, … expand at least one year, so that you can see a specific day.Put the Measure Sum Values into the values area.Drag the field date from the fields pane to the line caption of the PivotTable.Create a PivotTable from the data model (press Home → PivotTable)….To find the correct syntax, I created a PivotTable from the data model and converted it into CUBE formulas to see how Excel converts it internally. To do this, follow these steps: I got the following message all the time, when trying a syntax like this: This date reference doesn’t return the desired result Referencing a date column of the data model within the CUBEVALUE() function did not initially produce a result for me. Step #1: Reference date values correctly in CUBE formulas
Olap cube excel example download#
You can download my example file from here: Download Example file. What he wanted to achieve should look like this: The desired solutionįrom my point of view, the solution presented here can be divided into three sub-steps, which I will show below.