MDX

Distinct count on several dimension members

Posted by Jens Vestergaard on in Programming 0 Comment

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]

Loading

Mosha, manden med lyset

Posted by Jens Vestergaard on in Programming 0 Comment

I dag faldt jeg tilfældigt over en ret så, for mig, brugbar information. Længe har jeg lavet MDX statements, længe har jeg brugt IIF-statements og længe har jeg været irriteret over ikke at kunne returnere ‘N/A’ ved ukendte/ikke-definerede værdier. Det kan man så alligevel. Som skrevet var det lidt tilfældigt, at jeg spottede notationen i en artikel om performance af IIF i MDX af Mosha Pasumansky

Finessen ligger i FORMAT_STRING. Her kan notationen FORMAT_STRING=’#.#;;;NA‘ gøre en verden til forskel. Tak Mosha 🙂

Update: Link updated

Loading

Rank that thing…

Posted by Jens Vestergaard on in Programming 0 Comment

Ranking i MDX kan være en spøjs ting at arbejde med.

Her den anden dag sad jeg med problemet at skulle ranke et Crossjoin set hvor jeg kun ville have .children. Det viste sig, at når man rankede, skulle man benytte sig af Extract( [Dimension].Members, [Dimension].DefaultMember ) istedet for [Dimension].Children

I min verden er det en og samme ting… Men åbenbart ikke i Microsofts verden

Eksempel:
SET Set AS 'NonEmptyCrossjoin( [DimensionI].Children, DESCENDANTS( [DimensionII],, LEAVES )'
MEMBER [Measures].[Rank] AS 'Rank( ([DimensionI].CurrentMember, [DimensionII].CurrentMember ), Crossjoin( Extract( [Dimension].Members, [Dimension].DefaultMember ), DESCENDANTS( [DimensionII],, LEAVES ) ) ) )'
SELECT Set ON ...

Loading