0

In SSRS 2016, I'm writing some reports against a Tabular instance. We're using MDX as the query language. I'm having issues specifying a default unique name by hand in the report. Any ideas? Is it a bug? An escaping issue?

We've got a number of parameters where we pull the dimension attributes like this as the available values:

WITH MEMBER [Measures].[Label] AS [Dimension].[Dimension Attribute].CURRENTMEMBER.MEMBER_CAPTION
     MEMBER [Measures].[UniqueName] AS [Dimension].[Dimension Attribute].CURRENTMEMBER.UniqueName
SELECT {[Measures].[Label], [Measures].[UniqueName] ON 0,
    [Dimension].[Dimension Attribute].Members ON 1
FROM [CubeName]

If I try to set the default value by hand, Visual Studio seems to recognize the value, but the SSRS server does not. I am setting it to:

[Line of Business].[Line of Business].[All]

SSRS seems to escape this to 

\[Line of Business\].\[Line of Business\].\[All\]

This is also an issue in trying to pass selections among reports as well. Of note, if I make the default driven by a query, SSRS on the server is happy to select the default:

WITH MEMBER [Measures].[UniqueName] AS AS [Dimension].[Dimension Attribute].CURRENTMEMBER.UniqueName
SELECT [Measures].[UniqueName]
FROM [CubeName]
WHERE [Dimension].[Dimension Attribute].[All]
Steve Cardella
  • 57
  • 10
  • If you specify the parameter default value as a string in SSRS, you have to use STRTOSET function in your MDX script. Let me know if you need further help. – alejandro zuleta Sep 19 '16 at 18:35

1 Answers1

0

In SSRS the default value should be a string with the unique name of your dimension member, in this case:

="[Line of Business].[Line of Business].[All]"

Now in your dataset you have to use MDX STRTOSET function, with the purpose of string value in your parameter be intepreted as a valid set of [Line Of Business] dimension members.

SELECT 
  StrToSet
  (@BusinessLineParam
   ,CONSTRAINED
  ) ON ROWS
 ,[Measures].[Sales Amount] ON COLUMNS
FROM [Sales Cube];

Check a similar answer I posted recently. Also this could help you.

Let me know if this helps.

Community
  • 1
  • 1
alejandro zuleta
  • 13,962
  • 3
  • 28
  • 48
  • Strangely enough, this worked, but with a caveat - any parameters without a default must have a value selected before these defaults activate. I had actually tried that before, but since I had a parameter without a default set, it looked like the parameter defaults were not taking. – Steve Cardella Sep 20 '16 at 12:49
  • @SteveCardella, Yep, If your parameter is not set to allow null values it must have a selected value when default value is missing. Glad you managed to solve it. – alejandro zuleta Sep 20 '16 at 12:52