Quick Tip #1: Guided parameters using MDX

When selecting a parameter in a report you may find that the results returned can be empty. A guided parameter is to guide the user of the report away from displaying empty values.

Let’s say you have two parameters one being calendar quarter and the other being customer.

Here is an example using the Adventure Works database:

Edit the parameter data-set for customer.

WITH
MEMBER [Measures].[ParameterCaption] AS
[Customer].[Customer].CurrentMember.Member_Caption
MEMBER [Measures].[ParameterValue] AS
[Customer].[Customer].CurrentMember.UniqueName
MEMBER [Measures].[ParameterLevel] AS
[Customer].[Customer].CurrentMember.Level.Ordinal
SELECT
{ [Measures].[ParameterCaption]
,[Measures].[ParameterValue]
,[Measures].[ParameterLevel]
}  ON COLUMNS
,NonEmpty
(
[Customer].[Customer].MEMBERS
,
(
Strtomember(@DimDateQuarter)
,[Measures].MEMBERS
)
) ON ROWS
FROM [Adventure Works];

So in this query:


The NonEmpty returns customers crossed with a quarter that contains no null values for any measure ([Measures].MEMBERS)
The Strtomember returns the value of the calendar quarter parameter which is specified within the nonempty function.

I use this for easier parameter selections so customer attributes are not displayed with no measure data.

Latest from this author