Indent on report parameter in SSRS (written in MDX)

In this post I will show you how you can make nice indented report parameters in SSRS. This might become handy if you want to display multiple levels in the same parameter list.

Lets start by writing a simple MDX to list years and week numbers.

WITH MEMBER [RSValue]
AS
[Date].[Production].CurrentMember.UniqueName

MEMBER [RSCaption]
AS
[Date].[Production].CurrentMember.NAME

SELECT {
	[Measures].[RSValue],
	[Measures].[RSCaption]
} ON 0,
DESCENDANTS(
	[Date].[Production].[Year], 
	[Date].[Production].[Week], 
	SELF_AND_BEFORE
) ON 1
FROM [Adventure Works Internet Sales Model]

Running this query will produce a result like this in SSMS

Result 1

To add this result to a parameter I will first have to create a dataset. Let’s call that “MyDate”. Then paste the MDX into it. Then assign the “Available Values” as in the screenshot below.

Result 2

During report preview the report parameter will look like this

Result 3

Not too nice and readable I guess. I really need to make som kind of indenting here. So I start to make some changes to my MDX like this

WITH MEMBER [RSValue]
AS
[Date].[Production].CurrentMember.UniqueName

MEMBER [Indent]
AS
[Date].[Production].CurrentMember.LEVEL_NUMBER - 1

MEMBER [RSCaption]
AS
SPACE([Measures].[Indent] * 5) + " " + [Date].[Production].CurrentMember.NAME

SELECT {
	[Measures].[RSValue],
	[Measures].[Indent],
	[Measures].[RSCaption]
} ON 0,
DESCENDANTS(
	[Date].[Production].[Year], 
	[Date].[Production].[Week], 
	SELF_AND_BEFORE
) ON 1
FROM [Adventure Works Internet Sales Model]

It’s not easy to see the result of this indenting if you run the query in SSMS. But if you replace the query in the dataset we called “MyDate” you will see a much nicer list.

Result 4

Make sure you trim your parameter when setting it against the dataset you want to filter

Result 5

Voila!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s