XLCubed is now FluenceXL. The new wiki can be found here: https://help.fluencexl.com/

Difference between revisions of "MDX Calculations"

(Creating Named Sets)
(Creating Named Sets)
Line 100: Line 100:
  
  
[[Image:NamedSet5.png|50px|left]] Click Insert a new calculated set  
+
[[Image:NamedSet5.png|20px|left]] Click Insert a new calculated set  
  
  

Revision as of 16:36, 16 November 2011

Not all OLAP environments are as rich as we, the analysts would like. These examples show how XLCubed allows the power of OLAP to be simply used to improve our analyses.

Creating Calculated Members (based on the Bicycle Sales cube)

We will create new Gross Profit and Gross Profit Margin measures.

Start by creating a connection to the BicycleSales Demo Cube. This cube only has one measure, Value, but several different 'views' of the data, provided by the KeyFigures hierarchy.

To create the calculated measures:

  1. Open the Manage Calculations dialog by selecting the XLCubed > Custom Calculations ribbon or menu option.
  2. To create the Gross Profit member, click the Insert a new calculated member toolbar button (NewCalculatedMember.png).
  3. A new 'untitled' member will appear. Fill in the following details for the new member:
Name Gross Profit
Parent hierarchy KeyFigures
Parent member [KeyFigures].[All]
Expression [KeyFigures].[All].&[Revenue] - [KeyFigures].[All].&[Cost of Sales] - [KeyFigures].[All].&[Production Costs]
Custom calculations dialog with the Gross Profit member
The Gross Profit member
  1. Create a second calculated member for the Gross Profit Margin member, and fill in the following details. To enter the format string, Show advanced controls must be clicked:
Name Gross Profit Margin
Parent hierarchy KeyFigures
Parent member [KeyFigures].[All]
Expression [KeyFigures].[All].[Gross Profit] / [KeyFigures].[All].&[Revenue]
Format string 0%
Custom calculations dialog with the Gross Profit Margin member
The Gross Profit Margin member
  1. We now have members in the KeyFigures hierarchy, but if we want to use them as normal measures, we need to create two more members in the Measures hierarchy:
Name Gross Profit
Parent hierarchy Measures
Parent member Leave empty
Expression ([KeyFigures].[All].[Gross Profit],[Measures].[Value])


Name Gross Profit Margin
Parent hierarchy Measures
Parent member Leave empty
Expression ([KeyFigures].[All].[Gross Profit Margin],[Measures].[Value])
Format string 0%
  1. Click on OK to create the calculated members.

We can now see the new measures in context. Let's find badly performing product groups for 2004.

  1. Click on the XLCubed > Grid ribbon item (or the XLCubed > Design Grid menu item in Excel 2003 and below). For more information about using this dialog, see Report Designer.
  2. Move Measures to columns, Product to rows and Time to headers.
  3. Click on the Time hierarchy, then select 2004, the year we want to analyse.
  4. Click on the Measures hierarchy to select the Gross Profit and Gross Profit Margin measures.
  5. Click OK to insert the Grid.
  6. When we drill down on the Product hierarchy, we can see that Road S8000 is the failing product.

We could extend this report to use In-Cell Charts. See Visual Grids for Performance Analysis to continue.

Creating Named Sets

A named set is a Multidimensional Expressions (MDX) expression that returns a set of dimension members. Named sets can be created at cube level or within an application such as XLCubed. They can be created combining cube data, arithmetic operators, numbers and functions.


You create a named set from the Custom Calculation tab:

NamedSet6.png


NamedSet5.png

Click Insert a new calculated set


In this example I have created a named set, RacksAndStands, which is a grouping of Bike Racks and Bike Stands.

Note that the expression needs to be inside parenthesis as below { } and separated by commas.

{[Product].[Product Model Categories].[Subcategory].&[26],[Product].[Product Model Categories].[Subcategory].&[27]}

NamedSet1.png

The next step is to include the named set in the report.

NamedSet2.png

You must be editing the hierarchy/dimension that the named set is linked to – in this example Product Model Categories.

Click on the Advanced tab and select Member Set

NamedSet3.png

You should see your named set appearing as below – select it and click OK.

NamedSet4.png

In this example the members of the named set will not appear in the report.

See Also