Posts Tagged ‘MDX’

How to get SSAS to return the correct currency format.

Although setting the language and collation correctly, it’s not easy to see why a specific MDX would not return the right formatting, in regards to monetary, thousands seperator etc.
There is however a way of forcing the language, through a property available on the calculated measure. In the following example, I’ll show that even though the server settings are da-DK (Danish), the results are displayed as en-US.

First, a screenshot of the server settings:

Then the query, that returns en-US formatting:

For this to work, we need to add: Language=1030 as property to the calculated member, then we get:

See more on FORMAT_STRING and LANGUAGE @ http://msdn.microsoft.com/en-us/library/cc879322.aspx

Reklamer

MDX Query using other hierarchy in same dimension as in slicer

The other day a coworker of mine presented me with a problem in one of our solutions. His basic pain was, that the front-end of the application only allowed for one dropdown containing whatever dimension was set up to be displayed. This dimension is to act as slicer in a scorecard application. Since his KPIs were aiming different levels of different hierarchies in the date dimension, he was experiencing problems when faced with the choice of only one available date hierarchy.

Luckily we are able to solve this issue by means of MDX.
The following statement should display the solution, by the use of the method EXISTS and the knowledge of the structure of the date dimension hierarchies.

WITH
	MEMBER [Measures].[Test] AS 
		( EXISTS([Date].[Calendar Week].Members, [Date].[Calendar].CurrentMember).Item(1), [Measures].[Internet Sales Amount])
SELECT
	{[Test]} ON COLUMNS
FROM [Adventure Works]
WHERE { [Date].[Calendar].[Date].&[20070201] }

We use the EXISTS function to find the members of the [Date].[Calendar Week] hierarchy with the slicer set to  a member of another hierarchy, in this case [Date].[Calendar].[Date].&[20070201]. Note that we need to get Item(1) of the collection returned, as we get the All member at first index (0).

UPDATE:

As mentioned by Hrvoje Piasevoli (blog|twitter) it is a more clean solution to just query by the tuple made by the week hierarchy and the measure, eg.:

([Date].[Calendar Week].CurrrenMember, [Measures].[Internet Sales Amount])

In our case however, we discovered that the attribute relations in the Date dimension was not setup correctly to allow us to “convert” a member in one hierarchy to another. We ended up with the [All] member of that hierarchy.
We solved the issue by making a common root for the two date hierarchies, as seen in the Adventure Works cube.

Pct af totalen

Et eksempel på hvordan man kan beregne pct. af totalsalg og pct af salget på niveauet over det aktuelle.

WITH

MEMBER [Measures].[Pct of total] AS ‘SUM( {[Product].CurrentMember}, [Measures].[Unit Sales]) /SUM( {[Product].DefaultMember}, [Measures].[Unit Sales])’, FORMAT_STRING=’#0.00%’

MEMBER [Measures].[Pct of parent total] AS ‘IIF( [Product].Parent <> NULL, SUM( {[Product].CurrentMember}, [Measures].[Unit Sales]) /SUM( {[Product].Parent.Children}, [Measures].[Unit Sales]), 1)’, FORMAT_STRING=’#0.00%’

SELECT

{ [Measures].[Unit Sales], [Measures].[Pct of Total], [Measures].[Pct of parent total] } ON COLUMNS,

NON EMPTY Order( DESCENDANTS( [Product]  ),[Measures].[Pct of total], DESC ) ON ROWS

FROM

[Sales]

Et eksempel på totalten af en delmængde:

WITH
SET [Categories] AS {[Product].[Product Categories].[Category].&[4], [Product].[Product Categories].[Category].&[1]}
MEMBER [Measures].[Contribution] AS ([Product].[Product Categories].CurrentMember, [Measures].[Sales Amount]) / SUM([Categories], [Measures].[Sales Amount]), FORMAT_STRING=”#,##0.00%”
SELECT
{ [Measures].[Sales Amount], [Measures].[Contribution] } ON COLUMNS
,{[Categories] } ON ROWS
FROM [Adventure Works]

 

Kumulative værdier

Her ses et eksempel på hvordan man beregner kumlative værdier over en periode

WITH 
 MEMBER MEASURES.[Sales To Date] as ' 
        SUM( {[Time].CurrentMember.Level.Members}.Item(0) 
        : Time.CurrentMember,[Measures].[Sales Count])' 
 MEMBER MEASURES.[Sales Year To Date] AS ' 
        SUM(YTD([Time].CurrentMember),[Measures].[Sales Count])' 
 MEMBER MEASURES.[Sales Quarter To Date] as ' 
        SUM(QTD([Time].CurrentMember),[Measures].[Sales Count])' 
SELECT 
        {[Measures].[Sales Count], 
        [Measures].[Sales To Date], 
        [Measures].[Sales Quarter To Date], 
        [Measures].[Sales Year To Date]} ON ROWS, 
 DESCENDANTS([Time], , LEAVES) ON COLUMNS 
FROM 
[Sales]

Pct af total

Dette MDX eksempel viser hvordan man kan beregne den aktuelle pct af total salget, og pct af total salget for niveauet over det aktuelle.

WITH 
 MEMBER [Measures].[Pct of total] AS ' 
     SUM( {[Product].CurrentMember}, [Measures].[Unit Sales]) / 
     SUM( {[Product].DefaultMember}, [Measures].[Unit Sales])', 
     FORMAT_STRING='#0.00%' 
 MEMBER [Measures].[Pct of parent total] AS ' 
     IIF( [Product].Parent  NULL, 
     SUM( {[Product].CurrentMember}, [Measures].[Unit Sales]) / 
     SUM( {[Product].Parent.Children}, [Measures].[Unit Sales]), 1)', 
     FORMAT_STRING='#0.00%' 
SELECT 
 { [Measures].[Unit Sales], 
    [Measures].[Pct of Total], 
    [Measures].[Pct of parent total] } ON COLUMNS, 
 NON EMPTY 
    Order( DESCENDANTS( [Product]  ), 
    [Measures].[Pct of total], DESC ) ON ROWS 
FROM 
[Sales]

Den absolutte værdi

Her er et eksempel på hvordan man beregner den absolutte værdi af et givent Measure.

Code Block

WITH
 MEMBER [Measures].[Neg Profit] AS ‘-1 * [Measures].[Profit]’
 MEMBER [Measures].[Abs Profit] AS ‘ABS([Measures].[Neg Profit])’
SELECT
 {[Measures].[Neg Profit], [Measures].[Abs Profit]} ON COLUMNS,
 NON EMPTY DESCENDANTS( [Customers] ) ON ROWS
FROM
[Sales]

 

Distinct count on several dimension members

Here is an example on how you can select the distinct count on a combination of product categories.
The example is written for FoodMart 2000

WITH   

SET [Categories] AS ' 
	{[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine]} 
	+ 
	{[Product].[All Products].[Drink].[Beverages].[Carbonated Beverages]}'  
MEMBER [Measures].[Count] AS 'SUM( {[Categories]}, [Measures].[Sales Count] )' 
MEMBER [Measures].[Count A] AS 'SUM( 
	{[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine]}, 
	[Measures].[Sales Count] )' 
MEMBER [Measures].[Count B] AS 'SUM( 
	{[Product].[All Products].[Drink].[Beverages].[Carbonated Beverages]}, 
	[Measures].[Sales Count] )' 
SELECT 
{ [Measures].[Count], [Measures].[Count A], [Measures].[Count B] } ON COLUMNS, 
NON EMPTY DESCENDANTS( [Customers] ) ON ROWS   

FROM   

[Sales]