0

I got a problem using Multi-Value Parameters in SSRS.

I got a DataSet with this MDX Query :

SELECT NON EMPTY { [Measures].[Air Temperature Out - Fact Germination Continue] } ON COLUMNS, NON EMPTY { ([81 Germination Continue].[Batch Number].[Batch Number].ALLMEMBERS * [80 Germination General].[Plant Name].[Plant Name].ALLMEMBERS * [80 Germination General].[Production Unit].[Production Unit].ALLMEMBERS * [81 Germination Continue].[Characteristic Date].[Characteristic Date].ALLMEMBERS * [80 Germination General].[Start Date Unloading].[Start Date Unloading].ALLMEMBERS * [80 Germination General].[Start Date].[Start Date].ALLMEMBERS * [80 Germination General].[End Date].[End Date].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@GerminationGeneralProductionUnit, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@GerminationGeneralPlantName, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@GerminationContinueBatchNumber, CONSTRAINED) ) ON COLUMNS FROM [Supervision]))) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

When running the report, I got the error :

The restrictions imposed by the CONSTRAINED flag in the STRTOSET function were violated.

From my research, this means I have to put the full name in the parameters of the DataSet. The thing is that I think I did it, even if it's a bit different from what I saw, because of the construction of the datasets. For example, for the Germinaction Continue Batch Number, my parameter value is :

=Split(Replace(Join(Parameters!BatchNumbers.Value, ","), "[70 Steeping General]", "[81 Germination Continue]"), ",")

As you can guess, my data come from the dimension "[70 Steeping General]", which I try to put in the dimension "[81 Germination Continue]", the values are the same in both dimension ([81 Germination Continue].[Batch Number].[Batch Number] and [70 Steeping General].[Batch Number].[Batch Number]

Does the problem come from the fact that it's a multi-value parameter that looks like this? :

[81 Germination Continue].[Batch Number].[1234567890],
[81 Germination Continue].[Batch Number].[1234567891],
[81 Germination Continue].[Batch Number].[1234567892]

EDIT :

The parameters of the report :

FromSteepingStartDate ToSteepingStartDate

Both are Date/Time parameters and have an available value set to None, Default Value is DateAdd("m", -1, Today()) and DateAdd("d", -2, Today())

They are used in the DataSet DataSteeping, and the value of the parameter is

="[70 Steeping General].[Start Day].&[" + CStr(Format(CDate(Parameters!FromSteepingGeneralStartDay.Value), "yyyy-MM-dd"))+ "T00:00:00]"

SteepingGeneralPlantName

This parameter is a multi-value parameter, that initialy come from my dataset DataSteeping, so an autogenerated dataset has been created, with the following query :

WITH MEMBER [Measures].[ParameterCaption] AS [70 Steeping General].[Plant Name].CURRENTMEMBER.MEMBER_CAPTION MEMBER [Measures].[ParameterValue] AS [70 Steeping General].[Plant Name].CURRENTMEMBER.UNIQUENAME MEMBER [Measures].[ParameterLevel] AS [70 Steeping General].[Plant Name].CURRENTMEMBER.LEVEL.ORDINAL SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [70 Steeping General].[Plant Name].ALLMEMBERS ON ROWS FROM [Supervision]

The dataset is filtered like Parameter.Level <> 0 and Parameter.Caption <> "Unknown"

This parameter is used in every other DataSet, but they don't all come from the same dimension, so the parameter value in the others datasets properties looks like this:

=Split(Replace(Join(Parameters!SteepingGeneralPlantName.Value, ","), "[70 Steeping General]", "[80 Germination General]"), ",")

SteepingGeneralProductionUnit

This parameter is a multi-value parameter too, that initialy come from my dataset DataSteeping, so an autogenerated dataset has been created, but I changed it, so now it doesn't come from my dimension [70 SteepingGeneral], but from [04 Productions Unit], which contains all the productions unit, and is compatible with all the other dimensions, so it was easier to load in the other datasets, the MDX query is :

WITH MEMBER [Measures].[ParameterCaption] AS [04 Production Units].[Production Unit].CURRENTMEMBER.MEMBER_CAPTION MEMBER [Measures].[ParameterValue] AS [04 Production Units].[Production Unit].CURRENTMEMBER.UNIQUENAME MEMBER [Measures].[ParameterLevel] AS [04 Production Units].[Production Unit].CURRENTMEMBER.LEVEL.ORDINAL SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [04 Production Units].[Production Unit].ALLMEMBERS ON ROWS FROM ( SELECT ( STRTOSET(@PlantsPlant, CONSTRAINED) ) ON COLUMNS FROM [Supervision])

The dataset is filtered like Parameter.Level <> 0

This parameter is used in every other DataSet, only my Dataset DataGermination need an expression for this parameter, because it's from another dimension :

=Split(Replace(Join(Parameters!SteepingGeneralProductionUnit.Value, ","), "[04 Production Units]", "[80 Germination General]"), ",")

TempSteepingLimit

This parameter is not important for the problem, it's just the temperature limit, which I need to display the duration where the temperature is above this limit. It's a text parameter that I convert to a float (with a replace so the user can type "," or "."). It has no dataset and is used in no one, just in a textbox expression.

BatchNumbers

This hidden parameter is populated by a DataSet which return all the batch numbers that match with the other parameters (From/To Steeping Start Date, Plant Name and Production Unit). They are used in 2 DataSet (DataGermination and DataBatchProcessData), because I couldn't add the Steeping Start Date in those datasets.

In DataGermination, the value for BatchNumber is :

=Split(Replace(Join(Parameters!BatchNumbers.Value, ","), "[70 Steeping General]", "[81 Germination Continue]"), ",")

The dataset that populate this parameter is not autogenerated, I just added it, with this query :

SELECT NON EMPTY { [Measures].[Barley Volume] } ON COLUMNS, NON EMPTY { ([70 Steeping General].[Batch Number].[Batch Number].ALLMEMBERS * [70 Steeping General].[Plant Name].[Plant Name].ALLMEMBERS * [70 Steeping General].[Production Unit].[Production Unit].ALLMEMBERS * [65 Batch Process Data].[Steeping Start Date].[Steeping Start Date].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOMEMBER(@FromSteepingGeneralStartDay, CONSTRAINED) : STRTOMEMBER(@ToSteepingGeneralStartDay, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@SteepingGeneralProductionUnit, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@SteepingGeneralPlantName, CONSTRAINED) ) ON COLUMNS FROM [Supervision]))) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

(Sorry for my english)

Don't hesitate to ask questions!

Thank's for your help! :)

AKMMM
  • 294
  • 1
  • 2
  • 19
  • The values in your parameter must be valid members. Like this: `[81 Germination Continue].[Batch Number].[1234567890]`. – alejandro zuleta Feb 08 '16 at 16:29
  • The source of my multi-value parameter is a dataset that take only valid values, and it's a hidden parameter, so it's not the user that put wrong values ... I tried to make him visible and to select only one batch number which I'm sure the values are good, it still doesn't work – AKMMM Feb 09 '16 at 08:50
  • Now I changed my MDX query so it contains no longer a CONSTRAINED flag ( STRTOSET(@GerminationGeneralProductionUnit, CONSTRAINED) --> STRTOSET(@GerminationGeneralProductionUnit) ) But now, the issue is "The function expects a tuple set expression for the argument 1.A string or numeric expression was used." Does my Split(Replace(Join())) works? How can I put valid values? – AKMMM Feb 09 '16 at 11:01
  • how is your `@GerminationGeneralProductionUnit` parameter being populated? – alejandro zuleta Feb 09 '16 at 13:51
  • In the DataSet Properties of my DataGermination, the value of this parameter is Split(Replace(Join(Parameters!SteepingGeneralProductionUnit.Value, ","), "[04 Production Units]", "[80 Germination General]"), ",") The parameter SteepingGeneralProductionUnit come from another dataset in which he's also a parameter (so I have an autogenerated dataset for this parameter which I just filtered like ParameterLevel <> 0 to remove the "All" Checkbox in the parameter selection) – AKMMM Feb 09 '16 at 14:01
  • I don't get why you are using two parameters, you have to create a dataset that returns a valid member [dimension].[attribute].[value] members for populate the parameter, then you can just use in the parameter in your query without split the values before. Edit your question and include settings for every parameter used and the queries to pupulating it. – alejandro zuleta Feb 09 '16 at 14:23
  • Actually, I only have 1 parameter for Production Unit, the label is just not the same in all the dataset, because they come from differents dimensions – AKMMM Feb 09 '16 at 14:29
  • I see you use `SteepingGeneralProductionUnit` parameter for populating the `@GerminationGeneralProductionUnit`. – alejandro zuleta Feb 09 '16 at 14:42
  • Yes I do, I'm editing the question right now, so you'll have more explanation concerning all the parameters and how they are used – AKMMM Feb 09 '16 at 15:07
  • @GerminationGeneralProductionUnit is just a parameter in the dataset, he's populated by SteepingGeneralProductionUnit, because I created the dataset DataSteeping before DataGermination, but they could both use the same parameter – AKMMM Feb 09 '16 at 18:23

0 Answers0