How to MDX query for last 12 weeks in my time dimension?

Question

I have a bunch of question on MDX queries because i just started to learn about OLAP and SSRS report with SQL report builder.

First question is, could you please give me a link to the best sites that have a tutorial on MDX Queries..

Second, i already deploy a sales cubes.. What is the best way to pass parameter to the query? I mean how if i would like to query my sales for last 12 weeks automatically without using parameters (auto generate if now is in week 30 than it shows the data from week 18 to 30, and so on).. here is my query that generated automatically from designer:

SELECT NON EMPTY { [Measures].[Total Stick] } ON COLUMNS,
NON EMPTY
{
  ( [PALAPA Location].[LocationCode].[LocationCode].AllMembers * [PALAPA Fact Sales].[Year].[Year].AllMembers * [PALAPA Fact Sales].[Week].[Week].AllMembers )
} Dimension Properties MEMBER_CAPTION,
MEMBER_UNIQUE_NAME ON ROWS
FROM (
  SELECT (
  { [Time_Dim].[Week].&[2015-06-21T00:00:00], [Time_Dim].[Week].&[2015-06-28T00:00:00], [Time_Dim].[Week].&[2015-07-05T00:00:00], [Time_Dim].[Week].&[2015-07-12T00:00:00], [Time_Dim].[Week].&[2015-07-19T00:00:00] } ) ON COLUMNS
  FROM (
    SELECT ( { [Time_Dim].[Year].&[2015-01-01T00:00:00] } ) ON COLUMNS
    FROM [PALAPA_DSV]
  )
)
WHERE ( [Time_Dim].[Year].&[2015-01-01T00:00:00], [Time_Dim].[Week].CurrentMember ) CELL Properties Value,
BACK_COLOR,
FORE_COLOR,
FORMATTED_VALUE,
FORMAT_STRING,
FONT_NAME,
FONT_SIZE,
FONT_FLAGS

Third question, i would like to set the default values at SQL Report builder parameter (Week dimension) with this code :

=”[TP DIM CALENDAR].[Date].&[” + Format(CDate(Parameters!FromParameter.Value),”yyyy-MM-dd”) + “T00:00:00]”

But why it didnt work when i run the report? The parameter value is blank..

Thanks for your help !

whytheq whytheq · Accepted Answer · 2015-07-23T13:37:17

Please have a look at these mdx functions

StrToMember

https://msdn.microsoft.com/en-us/library/ms146022.aspx

StrToSet

https://msdn.microsoft.com/en-us/library/ms144782.aspx

These and several other StrTo.. functions are used pretty extensively for passing in parameters.

In your example you need to wrap the whole string in something like this:

strToMember(
    "[TP DIM CALENDAR].[Date].&[" +
     Format(CDate(Parameters!FromParameter.Value),"yyyy-MM-dd") + 
    "T00:00:00]"
)

How to MDX query for last 12 weeks in my time dimension?

2 Answers