Range parameter on the MDX-query

1
votes

I have a correct MDX-query:

SELECT NON EMPTY { [Measures].[IssueOpened] } ON COLUMNS, 
NON EMPTY { ([Projects].[Id].[Id].ALLMEMBERS * [Priorities].[Id].[Id].ALLMEMBERS ) } ON ROWS 
FROM [Reports]
WHERE [CreatedOn].[Date].&[2010-01-01T00:00:00]:[CreatedOn].[Date].&[2010-02-01T00:00:00]

I need to create SSRS-report with filter on CreatedOn dimension.

Here is my non-working solution:

  • I transform query to:

    SELECT NON EMPTY { [Measures].[IssueOpened] } ON COLUMNS, 
NON EMPTY { ([Projects].[Id].[Id].ALLMEMBERS * [Priorities].[Id].[Id].ALLMEMBERS ) } ON ROWS 
FROM (SELECT (STRTOSET(@CreatedOnDate, CONSTRAINED) ) ON COLUMNS
FROM [Reports])

  • CreatedOnDate parameter (type = datetime)

  • Set value of CreatedOnDate parameter to value:

    ="[CreatedOn].[Date].[" + Format(CDate(Parameters!CreatedOnDate.Value), "yyyy-MM-dd") + "T00:00:00]"

But when I run the report I get:

The restriction imposed by the CONSTRAINED flag in the STRTOSET function were violated
2
sql-serverreporting-services

2 Answers

2
votes

Somehow the parameter is not what you think. CONSTRAINED flag will force generating an error if the string is not a member when using StrToSet MDX function (check the failing example) :

You can try without the CONSTRAINED flag or find out the value of your parameter :

WITH 
 MEMBER myParam AS "[CreatedOn].[Date].[" + 
                   Format(CDate(Parameters!CreatedOnDate.Value), "yyyy-MM-dd") 
                   + "T00:00:00]"
SELECT
  [Measures].[myParam] on 0
FORM [Reports]

Playing a bit with this should make easier spotting the issue.

0
votes

It should work if you use STRTOMEMBER for each side of the range

STRTOMEMBER("[CreatedOn].[Date].&[2010-01-01T00:00:00]", constrained):STRTOMEMBER("[CreatedOn].[Date].&[2010-02-01T00:00:00]", constrained)