Hi,
How does one pass a parameter from a prompt to an MDX query?(using the MDX object). I tried the following:
Original MDX Query:
SELECT
NON EMPTY
Hierarchize({DrillDownLevel({[Policy].[Business Type].[All]})})
DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS
FROM [SSAS Cube]
WHERE
[Measures].[Policy Count Ratio]
CELL PROPERTIES
VALUE
,FORMAT_STRING
,LANGUAGE
,BACK_COLOR
,FORE_COLOR
,FONT_FLAGS;
Parameterized MDX Query:
SELECT
NON EMPTY
Hierarchize({DrillDownLevel({#prompt('p_view')#})})
DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS
FROM [SSAS Cube]
WHERE
[Measures].[Policy Count Ratio]
CELL PROPERTIES
VALUE
,FORMAT_STRING
,LANGUAGE
,BACK_COLOR
,FORE_COLOR
,FONT_FLAGS;
I get following error while validating the MDX query:
The data provider returned an error message: "Parser: The following syntax error occurred during parsing: Invalid token, Line 3, Offset 34, #.".
Would appreciate if someone can tell me if the MDX queries can be parameterized like SQL queries and if yes, provide a basic example on how to do that.
Thanks.
I have not ever written MDX directly but you can try the following
#prompt('p_view','memberuniquename')#
If you have a default value for the parameter you can add it as third parameter in parenthesis
I tried that too, but its not accepting any prompt parameter macro in MDX, hence wondering if parameters are actually supported in MDX queries?
Below query also gives same validation error:
SELECT
#prompt('p_view','memberuniquename')# ON COLUMNS
FROM [IAF Cube]
The data provider returned an error message: "Parser: The following syntax error occurred during parsing: Invalid token, Line 2, Offset 3, #.".