November 3, 2015 at 9:12 am
The MDX query below works as needed functionally.
Problem: I need to apply the DESCRIPTION to the parameters on columns
Summary: Note the DESCRIPTION, hierarchy property, in the center of the query ON ROWS. The DESCRIPTION provides a text description for the Rows as designated. Without DESCRIPTION the row returns only cryptic codes. Please advise on how to make this work for parameters & columns.
SELECT
NON EMPTY
{ [Measures].[Periodic] } ON COLUMNS,
NON EMPTY
{ ([Widgets].[Widgets-Factory-H1].[Widge-Fac LEV5].ALLMEMBERS *
[Payor].[PAYORS-H1].[PAYORS-TOT].ALLMEMBERS)},
[Widgets].[Widgets-Factory-H1].[Widge-Fac LEV5].DESCRIPTION,
[Payor].[PAYORS-H1].[PAYORS-TOT].DESCRIPTION
ON ROWS <------- DESCRIPTION works fine ON ROWS
FROM ( SELECT ( STRTOSET(@TimeMONTH, CONSTRAINED) ) ON COLUMNS <------Need help to add DESCRIPTION
FROM [WidgetInfo])
WHERE ( IIF( STRTOSET(@TimeMONTH, CONSTRAINED).Count = 1, STRTOSET(@TimeMONTH, CONSTRAINED), [Time].[MONTH].currentmember )
DESIRED:
To utilize the same DESCRIPTION on the parameter @TimeMONTH ON COLUMNS how can this be done? This should allow a text description on the parameter selection dropdown.
November 4, 2015 at 9:18 am
Hi Rick,
If you want a member property to be returned in your result set you need to do that as a calculated member as you are returning a values. To get the Description property you should access it using the Properties keyword. I don't think it's possible to access those properties in the way you described.
WITH MEMBER [Measures].[WidgetDescription] AS
[Widgets].[Widgets-Factory-H1].CurrentMember.Properties("Description")
MEMBER [Measures].[PayorsDescription] AS
[Payor].[PAYORS-H1].CurrentMember.Properties("Description")
SELECT
NON EMPTY
{ [Measures].[WidgetDescription], [Measures].[PayorsDescription], [Measures].[Periodic] } ON COLUMNS,
NON EMPTY
{ ([Widgets].[Widgets-Factory-H1].[Widge-Fac LEV5].ALLMEMBERS *
[Payor].[PAYORS-H1].[PAYORS-TOT].ALLMEMBERS)} ON ROWS
FROM ( SELECT ( STRTOSET(@TimeMONTH, CONSTRAINED) ) ON COLUMNS
FROM ( SELECT ( STRTOSET(@WidgetFactory, CONSTRAINED) ) ON COLUMNS
FROM [WidgetInfo]))
You cannot use the description for the parameters values in a StrToSet function but you can use it as the parameter Label. So as an example you would want your @WidgetFactory parameter dataset to look like this:
WITH MEMBER [Measures].[ParameterLabel] AS
[Widgets].[Widgets-Factory-H1].CurrentMember.Properties("Description")
MEMBER [Measures].[ParameterValue] AS
[Widgets].[Widgets-Factory-H1].CurrentMember.UniqueName
SELECT
{
[Measures].[ParameterLabel],
[Measures].[ParameterValue]
} ON COLUMNS,
{ [Widgets].[Widgets-Factory-H1].[Widge-Fac LEV5].ALLMEMBERS } ON ROWS
FROM [WidgetInfo]
Obviously I'm unable to run the above so they may contain errors, but hopefully they should give you an idea of how to correctly return the member properties.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply