I’m very new to MDX. I have a multi-select parameter called @MeasureType that has 3 possible labels (I know the name is confusing but "Measure" is an industry term here):
@MeasureType needs to limit the results of two columns: MeasureType and ContractType.
The parameter values map verbatim to MeasureType. In order to map them to ContractType, we need to apply some logic. Here’s the logic:
Just to make the logic clear, here’s how you could write it in SQL (there are better ways to write it, but it gets the point across):
-- Mimic multi-value parameter as if the user chose SELECT ALL
DECLARE @MeasureType TABLE (ParmValue varchar(20))
INSERT @MeasureType VALUES ('CMS')
INSERT @MeasureType VALUES ('HEDIS')
INSERT @MeasureType VALUES ('CMS/HEDIS')
-- Mimic the logic to assign value(s) @ContractType based on user's selection of @MeasureType
DECLARE @ContractType TABLE (ParmValue varchar(20))
IF (SELECT 1 FROM @MeasureType WHERE ParmValue = 'CMS') = 1
INSERT @ContractType VALUES ('CMS')
IF (SELECT 1 FROM @MeasureType WHERE ParmValue = 'HEDIS') = 1
INSERT @ContractType VALUES ('HEDIS')
IF (SELECT 1 FROM @MeasureType WHERE ParmValue = 'CMS/HEDIS') = 1
BEGIN
INSERT @ContractType VALUES ('CMS')
INSERT @ContractType VALUES ('HEDIS')
END
IF (SELECT 1 FROM @MeasureType WHERE ParmValue = 'CMS') = 1
AND (SELECT 1 FROM @MeasureType WHERE ParmValue = 'HEDIS') = 1
AND (SELECT 1 FROM @MeasureType WHERE ParmValue = 'CMS/HEDIS') = 1
BEGIN
INSERT @ContractType VALUES ('CMS')
INSERT @ContractType VALUES ('HEDIS')
INSERT @ContractType VALUES ('Other')
END
-- Get the results
SELECT MeasureType,
Measure1,
Measure2
FROM Table1
WHERE MeasureType IN (SELECT ParmValue from @MeasureType)
AND ContractType IN (SELECT ParmVaque from @ContractType)
I've tried a couple of approaches.
The first was creating a second (hidden) parameter for @ContractType, which I set using a series of IIFs that evaluate Parameters!MeasureType.Label. The expression is this:
=IIF(
JOIN(Parameters!MeasureType.Label) = "CMS CMS/HEDIS HEDIS",
"[HealthPlan].[ContractType].&[CMS] [HealthPlan].[ContractType].&[HEDIS] [HealthPlan].[ContractType].&[Other]",
IIF( INSTR( JOIN(Parameters!MeasureType.Label), "CMS/HEDIS" ),
"[HealthPlan].[ContractType].&[CMS] [HealthPlan].[ContractType].&[HEDIS]",
IIF( INSTR( JOIN(Parameters!MeasureType.Label), "CMS" ),
"[HealthPlan].[ContractType].&[CMS]",
"[HealthPlan].[ContractType].&[HEDIS]" )
) )
Then I declared both parameters in the Parameters tab of the dataset. I can run the MDX in the query designer but when I try to preview the report, I get this error… “The syntax for ‘[HealthPlan]’ is incorrect.” So I figured there must be something wrong with the parameter construct. I created a couple of textboxes to see the contents of the parameters. When SELECT ALL is chosen, JOIN(Parameters!MeasureType.Value) looks like this:
and Parameters!ContractType.Value looks like this:
which seems correct. Here's the MDX I'm using for this approach...
SELECT NON EMPTY { [Measures].[Physician Count],
[Measures].[Population],
[Measures].[Unmet],[Measures].[Met] } ON COLUMNS,
NON EMPTY { (NONEMPTY([Affiliation].[Affiliation1].[Affiliation1].Members,
[Measures].[Population]) *
NONEMPTY([Affiliation].[Affiliation2].[Affiliation2].Members,
[Measures].[Population]) *
NONEMPTY([Affiliation].[Affiliation3].[Affiliation3].Members,
[Measures].[Population]) *
NONEMPTY([Measure].[Registry].[Registry].Members,
[Measures].[Population]) *
NONEMPTY(([Measure].[Measure].[Measure].Members,
[Measure].[MeasureKey].[MeasureKey].Members,
IIf (
StrToSet ( @MeasureType, CONSTRAINED ) .Count = 1,
StrToSet ( @MeasureType, CONSTRAINED ),
[Measure].[MeasureType].Members
)
), [Measures].[Population]) *
NONEMPTY(([Physician].[PCPNameCode].[PCPNameCode].Members,
[Physician].[PCPID].[PCPID].Members,
[Specialty].[Specialty].[Specialty].Members
), [Measures].[Population]) )
}
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM (
SELECT ( StrToSet ( @MeasureType, CONSTRAINED ) ) ON COLUMNS
FROM (
SELECT ( StrToSet ( @ContractType, CONSTRAINED ) ) ON COLUMNS
FROM [Model]
)
)
WHERE (
IIf (
StrToSet ( @ContractType, CONSTRAINED ) .Count = 1,
StrToSet ( @ContractType, CONSTRAINED ),
[HealthPlan].[ContractType].CurrentMember
) )
CELL Properties Value,BACK_COLOR,FORE_COLOR,FORMATTED_VALUE,FORMAT_STRING,FONT_NAME,FONT_SIZE,FONT_FLAGS
Another approach I tried is evaluating @MeasureType within MDX by creating a calculated member with a case statement. The MDX is below. When I run it, I get no results at all.
WITH MEMBER [HealthPlan].[ContractType].[ContractType2]
AS
CASE @MeasureType
WHEN "CMS" THEN "[HealthPlan].[ContractType].&[CMS]"
WHEN "CMS/HEDIS" THEN "[HealthPlan].[ContractType].&[CMS] [HealthPlan].[ContractType].&[HEDIS]"
WHEN "HEDIS" THEN "[HealthPlan].[ContractType].&[HEDIS]"
WHEN "CMS CMS/HEDIS HEDIS" THEN "[HealthPlan].[ContractType].&[CMS] [HealthPlan].[ContractType].&[HEDIS] [HealthPlan].[ContractType].&[Other]"
END
SELECT NON EMPTY { [Measures].[Physician Count], [Measures].[Population], [Measures].[Unmet],[Measures].[Met] } ON COLUMNS,
NON EMPTY { (NONEMPTY([Affiliation].[Affiliation1].[Affiliation1].Members, [Measures].[Population]) *
NONEMPTY([Affiliation].[Affiliation2].[Affiliation2].Members, [Measures].[Population]) *
NONEMPTY([Affiliation].[Affiliation3].[Affiliation3].Members, [Measures].[Population]) *
NONEMPTY([Measure].[Registry].[Registry].Members, [Measures].[Population]) *
NONEMPTY(([Measure].[Measure].[Measure].Members,
[Measure].[MeasureKey].[MeasureKey].Members,
IIf (
StrToSet ( @MeasureType, CONSTRAINED ) .Count = 1,
StrToSet ( @MeasureType, CONSTRAINED ),
[Measure].[MeasureType].Members
)
), [Measures].[Population]) *
NONEMPTY(([Physician].[PCPNameCode].[PCPNameCode].Members,
[Physician].[PCPID].[PCPID].Members,
[Specialty].[Specialty].[Specialty].Members
), [Measures].[Population]) )
}
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM [Model]
WHERE [HealthPlan].[ContractType].[ContractType2]
CELL Properties Value,BACK_COLOR,FORE_COLOR,FORMATTED_VALUE,FORMAT_STRING,FONT_NAME,FONT_SIZE,FONT_FLAGS
I’m lost at this point. Any help at all would be greatly appreciated!
The hidden Parameter approach should work. The correct syntax for creating an MDX set is:
{[HealthPlan].[ContractType].&[CMS], [HealthPlan].[ContractType].&[HEDIS]}
To test just the parameters, use as short a query as possible, maybe something like the following
SELECT {
[Measures].[Population]
} ON COLUMNS,
StrToSet ( @MeasureType, CONSTRAINED )
ON ROWS
FROM [Model]
If you have problems, try building the MDX query in SSMS. Using SSMS, you can quickly check if the Syntax for the set above is correct (it might not be, as I didn't have a cube to test it on!) :
SELECT {
[Measures].[Population]
} ON COLUMNS,
StrToSet ( '{[HealthPlan].[ContractType].&[CMS], [HealthPlan].[ContractType].&[HEDIS]}',
CONSTRAINED )
ON ROWS
FROM [Model]
Once you know which text you need to build the set, you can set up a reporting Services Expression to build the text.