MDX Named Set to Get Current Month

How to write named set for hierarchy "[Date].[Calendar Year].[Month]" with
structure "
[Date].[Calendar Year].[Year].&[2008].&[Quarter - 2].&[2008]&[5]"

MDX Named set to get Last 3 Years

How to get last three years from hierarchy "" with structure " [Date].[Calendar Year].[Year].&[2007]"
[Date].[Calendar Year].[Year]

MDX Named Set to Get Last 3 Months

How to get last 3 months from hierarchy with structure "
[Date].[Calendar Year]. [Year].&[2007].&[Quarter - 3].&[2007]&[8]"

MDX Named set to get Last 3 Quarters

How to get last 3 Quarters from hierarchy with data structure "
[Date].[Calendar Year].[Year].&[2011].&[Quarter - 1]"

Create and use Named Set in MDX Query

How to use named set in MDX Query:

MDX Named set to get Months of Current Year

Order by Descending Order

Non Empty

MDX Named Set to find the ratio of Dimension Members

In order to find out ratio of Non-Billable hrs of Various cost type use below named set:

[Measures].[Non-Billable Hrs]/(Root([Tasks]),[Measures].[Non-Billable Hrs]) *100

where Tasks is the dimension name.

 


















Similary in order to find Sales Proportion of different country use the below named set

where Locationis the Dimension name.

[Measures]. [Sales Amt]/(Root([Location]),[Measures].[Sales Amt]) *100

MDX query for Top and Bottom Count

Top 10 Campaigns based on Opportunity Count

WITH SET [TopOpportunity] AS {TopCount([Opportunity].[Campaign Name].[Campaign Name], 10, [Measures].[Opportunity Count])}
SELECT [TopOpportunity] ON ROws,
[Measures].[Opportunity Count] ON Columns
FROM [PreSales]

Bottom10 Campaigns based on Opportunity Count

WITH SET [BottomOpportunity] AS {BottomCount([Opportunity].[Campaign Name].[Campaign Name], 10, [Measures].[Opportunity Count])}
SELECT BottomOpportunity ON ROws,
[Measures].[Opportunity Count] ON Columns
FROM [PreSales]

MDX Named Set Using Filter and Except Functions

--Return members filtered on a custom property. We need to exclude the All level from the set to filter on since it does not have any custom properties.

Filter(Except([Warehouse].Members,{ [Warehouse].[All Warehouse]}), [Warehouse].CurrentMember.Properties("PROPERTYNAME") = "PROPERTYVALUE")


--How to create a named set referring to a separate column in the table used for the time dimension as a member property

{Filter([Time].[Month].Members, [Time].CurrentMember.Properties("IsCurrentMonth") <> "0").Item(0).Item(0)}

--To get product names with sales amount greater than 0
([Product]. [Product Name].[Product Name] ,NOT ISEMPTY([Measures].[Sales Amt]OR (NOT ISEMPTY([Measures].[Sales Amt] > 0))
FILTER
(
) )
)

Named Sets for Time Dimensions

--Time examples

--How to define a default value for current month referring to the system clock if the month level in the time dimension is formatted yyyymm
StrToMember("[Time].[Month].[" + Format(Now(), "yyyymm") + "]")

--How to create a named set for current month referring to the system clock if the month level in the time dimension is formatted yyyymm
{StrToMember("[Time].[Month].[" + Format(Now(), "yyyymm") + "]")}

--How to create a named set referring to a separate column in the table used for the time dimension as a member property
{Filter([Time].[Month].Members, [Time].CurrentMember.Properties("IsCurrentMonth") <> "0").Item(0).Item(0)}

--Referring to a named set called Current Month
[Current Month].Item(0).Item(0)

--Referring to last 6 months (up to current month)
LastPeriods(6, [Current Month].Item(0).Item(0))

--How to use the named set Current Month for positioning on the quarter level
LastPeriods(6, Ancestor([Current Month].Item(0).Item(0), Time.Quarter))

--Last 6 members on month
{Tail([Time].[Month].members,6)}

--Last 6 members on month where actual is larger then 0
{Tail(Filter({[Time].[Month].members},[Measures].[Actual] > 0),6)}

--All months where actual is larger then 0
{Filter({[Time].[Month].members},[Measures].[Actual] >0)}

--Order months depending on actual
{Order([Time].[Month].members, [Measures].[Actual], BDESC)}

Named Set for Current Month Calculation

WITH SET [Calendar Months]
AS EXTRACT(STRTOMEMBER("[Date].[Month].&["+cstr(month(NOW ()))+"]",CONSTRAINED)
*{[Date].[Calendar Period].[Month]},[Date].[Calendar Period])
SELECT NON Empty[Calendar Months]  on Rows, [Measures]. [Actual Amount] on Columns FROM [Sales]

MDX named Set for Financial Year

Current Financial Year

STRTOMEMBER("[Date].[Financial Period]. [Year].&["+
  CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN Format(Now(), "yyyy")
  ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "]")

Last Financial Year

STRTOMEMBER("[Date].[Financial Period].[Year].&["+
  CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN Format(Now(), "yyyy") -1
  ELSE CSTR(CINT(Format(Now(), "yyyy")) - 2) END + "]")

Named Set for Months in Current Financial Year

ORDER(
StrToMember("[Date].[Financial Period].[Year].&[" +
CASE WHEN CINT(Format(Now (),"MM"))  >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "].&[" +
CASE WHEN CINT(Format(Now (),"MM"))  >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "]&[1].&[" +
CASE WHEN CINT(Format (Now(),"MM"))  >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "]&[1]")
:
StrToMember("[Date]. [Financial Period].[Year].&[" +
CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "].&[" +
CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format(Now(), "yyyy")) - 1) END + "]&[" +
CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN  CSTR(CINT(DATEPART("q", Now())) - 1)
ELSE  CSTR(CINT (DATEPART("q", Now())) + 3) END + "].&[" +
CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN Format(Now(), "yyyy")
ELSE CSTR(CINT(Format (Now(), "yyyy")) - 1) END + "]&[" +
CASE WHEN CINT(Format(Now(),"MM"))  >= 4 THEN CSTR(CINT(Format(Now(),"MM")) - 3)
ELSE CSTR(CINT (Format(Now(),"MM")) + 9) END + "]"
)
,[Date].[Financial Period].CURRENTMEMBER.PROPERTIES("ID", TYPED), DESC)

To get Sales of Current Year, Previous Year, etc using Lag anf Lead functions

Current Year Sales:

SELECT [Measures].[Sales (000's)] on ROws,
{CROSSJOIN([Date]. [Fiscal Months].[Fiscal Months].Allmembers,STRTOMEMBER("[Date].[Financial Period].[Year].&["+ CASE WHEN
CINT(FORMAT(NOW(),"MM")) >= 4 THEN FORMAT(NOW(),"yyyy") ELSE  CSTR(CINT(FORMAT(NOW(),"yyyy"))-1) END +"]"))}
on Columns
FROM [Sales]

Result

Previous year Sales

SELECT [Measures].[Sales (000's)] on ROws,
{CROSSJOIN([Date]. [Fiscal Months].[Fiscal Months].Allmembers,STRTOMEMBER("[Date].[Financial Period].[Year].&["+ CASE WHEN
CINT(FORMAT(NOW(),"MM")) >= 4 THEN FORMAT(NOW(),"yyyy") ELSE  CSTR(CINT(FORMAT(NOW(),"yyyy"))-1) END +"]").lag(1))}on Columns
FROM [Sales]

SELECT [Measures].[Sales (000's)] on ROws,
{CROSSJOIN([Date].[Fiscal Months].[Fiscal Months].Allmembers,STRTOMEMBER("[Date].[Financial Period]. [Year].&["+ CASE WHEN
CINT(FORMAT(NOW(),"MM")) >= 4 THEN FORMAT(NOW(),"yyyy") ELSE  CSTR(CINT(FORMAT(NOW(),"yyyy"))-1) END +"]").lead(-1))}
on Columns
FROM [Sales]

Result

Named Set to Display Months of Previous Year