| Statement |
Expressions |
| To get previous month in the Time dimension |
SELECT ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember) ON 0
FROM [Sales Summary]
WHERE ([Measures].[Sales Amount]) |
| To display the first day of last month in the cube |
SELECT OpeningPeriod([Date].[Calendar].[Date]
, ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember)
) ON 0
FROM [Sales Summary] |
| MDX query to get the last month loaded in the cube |
SELECT ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember) ON 0
FROM [Sales Summary] |
| MDX query that uses execution date/time as a parameter |
SELECT {[Measures].[Internet Order Count]} ON 0
, {StrToMember(“[Date].[Date].[”
+ Format(now(), “MMMM dd, yyyy”) + “]”)
} ON 1
FROM [Direct Sales]; |
| To get the latest months and previous years same month data |
SELECT {ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember)
, ParallelPeriod([Date].[Calendar].[Calendar Year]
, 1
, ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember)
)
} ON 0
FROM [Sales Summary] |
| To create a calculated member for AVG sales over last 3 years based on NOW () function |
CREATE MEMBER CurrentCube.Measures.[Avg3Years] AS
Avg(
{ParallelPeriod( [Date].[Date].[Date Yr], 3, StrToMember(“[Date].[Date].&[” + Format(now(), “yyyyMMdd”) + “]”))
: StrToMember(“[Date].[Date].&[” + Format(now(), “yyyyMMdd”) + “]”)}
, [Measures].[Sales Qty]
) ; |
| To get the Previous year to date values |
WITH MEMBER [Measures].[Current YTD] AS
SUM(YTD([Date].[Calendar].CurrentMember), [Measures].[Internet Order Quantity])
MEMBER [Measures].[Last YTD] AS
SUM(YTD(ParallelPeriod([Date].[Calendar].[Calendar Year]
, 1
, [Date].[Calendar].CurrentMember))
, [Measures].[Internet Order Quantity]
)
SELECT {[Measures].[Current YTD]
, [Measures].[Last YTD]
} ON 0
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Date].[March 22, 2004]) |
| To calculate monthly average of one year |
WITH MEMBER [Measures].[AvgVal] AS
Avg( Descendants([Date].[Calendar].[Calendar Year].&[2004]
, [Date].[Calendar].[Month])
, [Measures].[Internet Order Count]
)
SELECT {[Measures].[AvgVal]} ON 0
FROM [Adventure Works]
WHERE ([Product].[Product Model Lines].[Model Name].&[Classic Vest]) |
| To return lists of months from start of year to specified month |
SELECT YTD([Date].[Calendar].[Month].&[2003]&[8])
ON 0
FROM [Sales Summary] |
| To order the date dimension members in descending order in a report |
SELECT {[Measures].[Reseller Order Quantity]} ON 0
, ORDER(Tail([Date].[Calendar].[Calendar Year].Members,3)
, [Date].[Calendar].CurrentMember.Member_Key, DESC
) ON 1
FROM [Adventure Works] |
| To load the first month of last year in the cube |
SELECT OpeningPeriod([Date].[Calendar].[Month]
, ClosingPeriod([Date].[Calendar].[Calendar Year], [Date].[Calendar].DefaultMember)
) ON 0
FROM [Sales Summary]; |
| MDX query to show the year level data for all years except the last one, and month level data for the last year |
SELECT {NULL:
ClosingPeriod([Date].[Calendar].[Calendar Year], [Date].[Calendar].DefaultMember).PrevMember
, DESCENDANTS(ClosingPeriod([Date].[Calendar].[Calendar Year], [Date].[Calendar].DefaultMember)
, [Date].[Calendar].[Month])
}
ON 0
FROM [Sales Summary] |
| To calculate sales for 12month to date in MDX |
WITH MEMBER [Measures].[Last 12 Mth Order Count] AS
SUM(
ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].[All Periods]).Lag(12)
: ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].[All Periods])
, [Measures].[Order Count]
)
SELECT [Measures].[Last 12 Mth Order Count] ON 0
FROM [Adventure Works] |
| To get count of months with sales amount > 0 in defined period |
WITH Member [Measures].[Months With Above Zero Sales] AS
COUNT(FILTER(
DESCENDANTS({[Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004]}
, [Date].[Calendar].[Month])
, [Measures].[Sales Amount] > 0
)
)
SELECT {[Measures].[Sales Amount], [Measures].[Months With Above Zero Sales]} ON 0
, [Product].[Product Model Lines].[Product Line].Members on 1
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004]) |
| To calculate monthly average of a year including empty months |
WITH MEMBER [Measures].[AvgVal] AS
Avg( Descendants([Date].[Calendar].[Calendar Year].&[2004]
, [Date].[Calendar].[Month])
, CoalesceEmpty([Measures].[Internet Order Count], 0)
)
SELECT {[Measures].[AvgVal]} ON 0
FROM [Adventure Works]
WHERE ([Product].[Product Model Lines].[Model Name].&[Classic Vest]) |