Saturday, November 6, 2010

MDX: Average based on any dimension on row axis

This was an interesting learning i did. There was a requirement were user wishes to calculate the average of sales based on any dimension that he wishes to pass. i.e. the calculated member need to be dynamic enough to calculate based on any dimension passed (not multiple).

To achieve this one need to use the Axis function provided by MDX


WITH MEMBER [Average] AS
      [Measures].[Internet Sales Amount]/
      SUM(Axis(1).Item(0).Item(0).Dimension.DefaultMember,[Measures].[Internet Sales Amount])
      ,FORMAT='PERCENT'
SELECT
      {[Measures].[Internet Sales Amount],[Average]} on COLUMNS,
      NONEMPTY
      (
            [Product].[Product Categories].[Category].MEMBERS,
            [Measures].[Internet Sales Amount]
      ) ON ROWS
FROM
      [Adventure Works]

Friday, November 5, 2010

MDX: Ordered versus Purchased Product by a Customer

WITH
 MEMBER Measures.SoldProducts AS
    DISTINCTCOUNT(
      NonEmpty
      (
        Descendants
        (
          [Product].[Product Categories]
         ,[Product].[Product Categories].[Product]
        )
       ,[Measures].[Internet Sales Amount]
      ))
    
SELECT
  {
  [Measures].[Internet Order Quantity],Measures.SoldProducts
  } ON 0
 ,filter( [Customer].[Customer].&[15536],Measures.SoldProducts>0) ON 1
FROM [Adventure Works]

MDX Top N with Remainder every Year

This is some good stuff. I tried a bit on this but was lost in mid way. This was coolly solved by HrvojePiasevoli in the MSDN forums. Thought of sharing this and may be this will be useful for few more.

Learnt Extract, Union and many more.
The requirement here is to Display the Top N sales, remainder and total for each year


with
member [Product].[Product].[AllProducts] AS [Product].[Product].[All Products]
,BACK_COLOR ="&H0000D00D"

MEMBER [Product].[Product].Remainder
AS Aggregate(
        [Product].[Product].[Product].MEMBERS -
        EXTRACT(
                  TOPCOUNT(
                        [Date].[Fiscal Year].CURRENTMEMBER *
                        NonEmpty([Product].[Product].[Product].MEMBERS, [Measures].[Internet Sales Amount]),
                        3,
                        [Measures].[Internet Sales Amount]
                        )
        ,[Product].[Product])
            ), BACK_COLOR ="&H0000FFFF"
           
set TopSetWithReminder as
Generate(
    [Date].[Fiscal Year].[Fiscal Year].MEMBERS,
    UNION(
            TOPCOUNT
            (
                  [Date].[Fiscal Year].CURRENTMEMBER *
                  NonEmpty([Product].[Product].[Product].MEMBERS, [Measures].[Internet Sales Amount]),
                  3,
                  [Measures].[Internet Sales Amount]
                  ),
                  ([Date].[Fiscal Year].CURRENTMEMBER,[Product].[Product].Remainder),
                  ([Date].[Fiscal Year].CURRENTMEMBER,[Product].[Product].[AllProducts] )
            )
    )

SELECT
{
      [Measures].[Internet Sales Amount]
      ,[Measures].[Internet Gross Profit]
} ON 0,
TopSetWithReminder ON 1
FROM 
[Adventure Works]
CELL PROPERTIES FORMATTED_VALUE, BACK_COLOR

MDX Employee Ancestors

MDX to get the employee and managers above him. User can restrict to what level above him the manager details need to be fetched. Here we are getting immediate manager and his manager.


WITH
      MEMBER [EmployeeLevel] AS [Employee].[Employees].CURRENTMEMBER.LEVEL_NUMBER
    
SELECT
      FILTER(
            ORDER(
                  NONEMPTY(
                        ASCENDANTS([Employee].[Employees].&[291])
                        ,[Measures].[Reseller Order Count]
                  ),
                  EmployeeLevel,BDESC
            ),
            EmployeeLevel<>0 and EmployeeLevel>=2
      )
      ON 0,
      {
            [EmployeeLevel],
            [Measures].[Reseller Order Count]
          
      } ON 1
FROM [Adventure Works]


Using the descendant function


WITH
MEMBER Manager as [Employee].[Employees].currentmember.parent.name
MEMBER Director as [Employee].[Employees].currentmember.parent.parent.name

SELECT NON EMPTY
{
      [Measures].[Reseller Order Count],
      measures.manager,
      measures.director
} ON COLUMNS,
NON EMPTY filter
(
      {
            (
   DESCENDANTS([Employee].[Employees].[Employee Level 04].ALLMEMBERS,,leaves)
            )
      }, 
      [Measures].[Reseller Order Count] >0
) ON ROWS
FROM ( SELECT [Employee].[Employees].&[291] ON COLUMNS FROM [Adventure Works])

MDX:Display seconds in dd:HH:mm:ss format

WITH
MEMBER measures.seconds as 25324

MEMBER Measures.ddhhmmss as
cstr(int(measures.seconds/86400)) + ":" + format(cdate(measures.seconds/86400 - int(measures.seconds/86400)), "HH:mm:ss")

SELECT
ddhhmmss on 0 FROM [Adventure Works]


If the format is made as "dd:HH:mm:ss" then it would display the value as 31:02:28:44 and not 01:02:28:44

d/dd: It is supposed to give the day of the month, therefore it can not work for more than 31 days.
example: member measures.x as cdate(95324/86400), FORMAT_STRING = 'd:HH:mm:ss'

LinkWithin

Related Posts with Thumbnails