Overview:  A quick tip re: using SQL Server Reporting Services (SSRS) Dataset Properties to derive a concatenated field.

Level:  Familiarity with SSRS parameters & datasets is presumed.

Recently I was working on an SSRS report.  One parameter required its label to be formatted Number – Name (for example:  80 – Revenue).  In my SSAS data source, I had the Number field and the Name field available, but I did not have them concatenated together.  That I would need to derive in the report.

A quick reminder about parameters in SSRS:  a parameter looks for 2 fields:  a value, and a label.  This blog entry is focusing on the label that the user sees when he interacts with the parameter.

Option 1 – Derive in the MDX source query

Deriving within the source query is what I did initially.  However, in my situation, I had about 4,000 possible options that could be returned by the parameter query (if the user had full security to the data, like I do).  After adding the WITH MEMBER, this query ran extremely slowly.

To translate my situation into an Adventure Works example, here’s what I did first:

WITH
MEMBER [Measures].[AccountParameter_Label] AS
[Account].[Account Number].CURRENTMEMBER.NAME + " - " + [Account].[Account Type].CURRENTMEMBER.NAME

SELECT NON EMPTY
(
[Measures].[AccountParameter_Label]
)
ON COLUMNS
,NON EMPTY
(
[Account].[Account Number].[Account Number].MEMBERS
,[Account].[Account Type].[Account Type].MEMBERS
)
ON ROWS

FROM [Adventure Works]

Since the performance of the above query was not acceptable, I looked for an alternative.

Option 2 – Derive in the SSRS Dataset

This second option, deriving the field within the Dataset Properties, performed significantly faster.  To set it up:

Step 1:  Create the basic Parameter results query

WITH
MEMBER [Measures].[AccountParameter_Value] AS [Account].[Account Number].CURRENTMEMBER.UNIQUENAME

SELECT NON EMPTY
{
  [Measures].[AccountParameter_Value]
}
ON COLUMNS
, NON EMPTY
(
[Account].[Account Number].[Account Number].MEMBERS
,[Account].[Account Type].[Account Type].MEMBERS
)
ON ROWS

FROM [Adventure Works]

Step 2:  Add a Calculated Field in the “Fields” pane of the Dataset Properties

First, create a Calculated Field:

Then, give it a Field Name and a Field Source.  The expression for the Field Source is:

=Fields!Account_Number.Value & " - " & Fields!Account_Type.Value

Step 3:  Create (or modify) the parameter

What the user sees is the concatenated label we created:

Conclusion

Usually I prefer to derive fields within my dataset, just to keep the logic consolidated in one place.  However, in this particular situation having the Report Server perform the concatenation performed much, much faster for me – sub 1 second as opposed to 30 seconds execution time.  Doing it this way won’t always be the best answer – but it’s a possibility to keep in the ‘ol toolbox.