MDX StrToMember date range issues

Question

It seems like the query can't get date range to work. I'm passing two dates as a parameters and based on that I would expect to have [Sector Name] dropdown values retrieved based on the date range(@FromJoinDateDate:@ToJoinDateDate) and both @ADMTRANSINSpecialty and @ADMTRANSINSite which last two seems to be working properly.

Example of parameters:
@FromJoinDateDate: [Sector_Join_Date].[Date].&[2020-07-29T00:00:00]
@ToJoinDateDate: [Sector_Join_Date].[Date].&[2021-07-29T00:00:00]

WITH 
MEMBER [Measures].[ParameterCaption] AS [ADM_TRANS_IN].[Sector Name].CURRENTMEMBER.MEMBER_CAPTION 
MEMBER [Measures].[ParameterValue] AS [ADM_TRANS_IN].[Sector Name].CURRENTMEMBER.UNIQUENAME 
MEMBER [Measures].[ParameterLevel] AS [ADM_TRANS_IN].[Sector Name].CURRENTMEMBER.LEVEL.ORDINAL 

SELECT {
[Measures].[ParameterCaption], 
[Measures].[ParameterValue],
[Measures].[ParameterLevel]

} ON COLUMNS , 
[ADM_TRANS_IN].[Sector Name].ALLMEMBERS ON ROWS 
FROM ( SELECT ( STRTOSET(@ADMTRANSINSpecialty, CONSTRAINED) ) ON COLUMNS 
FROM ( SELECT ( STRTOSET(@ADMTRANSINSite, CONSTRAINED) ) ON COLUMNS 
FROM ( SELECT { STRTOMEMBER(@FromJoinDateDate, CONSTRAINED) : 
                  STRTOMEMBER(@ToJoinDateDate, CONSTRAINED) } ON COLUMNS 
FROM [Model])))

I have a feeling that StrToMember function might be returning null in both cases which is why the results are not matching the query, however I need to use it in order to use range function. Could it be also the date format not matching what is in the cube?

score 1 · Answer 1 · answered Nov 07 '21 at 11:14

One way to check the actual date format value in your Date dimension is as follows:

Create a new query
Navigate to your date dimension, expand members, select a date and drag and drop to your query window.

This will then show you the exact date format for your data. An example screen shot using the AdventureWorks sample is given below:

As can be seen, even though in the tree it is shown as "January 1, 2005", the actual data is of the format: 20050101.

Once you able to find the actual date format in your data, you can then constuct the string representation accordingly.

MDX StrToMember date range issues

1 Answers1