You can retrieve current year, current month and current day by using VBA functions.
MDX for retrieving current Year;
WITH MEMBER [Measures].[Current Year] AS
VBAMDX.Format(VBAMDX.Now(),"yyyy")
SELECT
{[Measures].[Current Year]} ON COLUMNS
FROM [Adventure Works];
MDX for retrieving current Month;
WITH MEMBER [Measures].[Current Month] AS
VBAMDX.Format(VBAMDX.Now(),"MM")
SELECT
{[Measures].[Current Month]} ON COLUMNS
FROM [Adventure Works];
MDX for retrieving current Date;
WITH MEMBER [Measures].[Current Date] AS
VBAMDX.Format(VBAMDX.Now(),"dd")
SELECT
{[Measures].[Current Date]} ON COLUMNS
FROM [Adventure Works];
Sometimes user wants to retrieve data for current year, current month, and current day. You can retrieve data by using VBA function and STRTOMEMBER function.
Check following MDX samples by using Adventure Works sample.
MDX for retrieving current Year;
WITH
MEMBER [Measures].[Current Year Value] AS
(
StrToMember
(
"[Date].[Calendar].[Calendar Year].&["
+ VBAMDX.Format(VBAMDX.Now(),"yyyy") + "]"
)
,[Measures].[Internet Sales Amount]
)
SELECT
[Measures].[Current Year Value] ON COLUMNS
FROM [Adventure Works];
MDX for retrieving current Month;
WITH
MEMBER [Measures].[Current Month Value] AS
(
StrToMember
(
"[Date].[Calendar].[Month].&[" +
VBAMDX.Format(VBAMDX.Now(),"yyyy") + "]
&[" + VBAMDX.Format(VBAMDX.Now(),"MM") + "]"
)
,[Measures].[Internet Sales Amount]
)
SELECT
[Measures].[Current Month Value] ON COLUMNS
FROM [Adventure Works];
MDX for retrieving current Day;
WITH
MEMBER [Measures].[Today's Value] AS
(
StrToMember
(
"[Date].[Calendar].[Date].&[" +
VBAMDX.Format(VBAMDX.Now(),"yyyyMMdd")
+ "]"
)
,[Measures].[Internet Sales Amount]
)
SELECT
[Measures].[Today's Value] ON COLUMNS
FROM [Adventure Works];
Note : AdventureWorks sample does not contain data for current year,so if you execute above mentioned MDX,it results in Null values but if you insert data for current year,it will result the required output.
