SSRS – Using Lookup Function for Multiple Column Join

When SQL Server 2008 R2 was released several new Reporting Services expressions were made available that would help join multiple datasets together.  Those functions were the Lookup Function, LookupSet Function and the MultiLookup Function.  My general recommendation for these function is not to use them when you have two dataset from the same data source.  They’re really intended for connecting datasets together that are not directly related because otherwise I could just handle it in my source query.

My experience with them had been fairly academic until recently when I had a real scenario where the Lookup Function would be beneficial.  I was working with a customer that had a report requirement that needed data from DB2 and Oracle in a single tabular report.  What made this circumstance more difficult was that the join between the DB2 dataset and the Oracle dataset was not a single column but two.

Seems basic enough right?  If we were writing T-SQL we would simply do a join on multiple columns with an AND between them.  Because these are two different data sources in Reporting Services I had to get a little more creative.  If you’ve used the Lookup function in SSRS (template below) expressions then you know it can only accept one field from the source and one from the destination.  So what if you have two columns to join on?

Lookup(source_expression, destination_expression, result_expression, dataset)

The approach I took was to concatenate the two join columns together and then place the concatenated value in the Lookup function.  I also placed a literal string between the two columns to protect myself from coincidental matches.  To make the solution a little cleaner I created a calculated field on each dataset that handled the expression.  So both datasets had a calculated field I called JoinColumn and was defined similar to this:

=Fields!JoinColumn1.Value + ”||” + Fields!JoinColumn2.Value

Notice the double pipes that are used here to protect from coincidental matches.  With these calculated field created on each dataset now my Lookup function would be fairly standard.  Here’s my end result:

Lookup(Fields!JoinColumn.Value, Fields!JoinColumn.Value, Fields!ReturnColumn.Value, “DataSet2”)

I hope this helps as you find practical uses for the Lookup Function in Reporting Services.

SSRS – Join Function

Often when designing a Reporting Services with parameters you may be required to display those parameter values on your report.  This is beneficial when you have a lot of values for one parameter or maybe you just want to print the report and need to know how the report is being filtered.

Normally if you would like to use a parameter anywhere in the design of your report you can simply drag in the parameter name from the Report Data tab.  Here I’ve concatenated the parameter value with some text:

="Category: "+Parameters!Cat.Value

However, when you have a parameter that can have multiple values you will run into an issue with displaying these parameter values correctly.

The problem it is displaying an array where normally a single value would be placed.  The fix for this is a pretty simple function that exists in Reporting Services called Join.  By changing my parameter textbox to the following:

=”Category: “+Join(Parameters!Cat.Value, “, “)

Making changes places the array in a comma delimited list visible to the user.

SSRS Commonly Required Date Expressions

Reporting Services has many expressions that can manipulate date fields.  These expressions can be used just about anywhere in SSRS including object properties, displayed data, and parameters.

Recently I’ve worked on some projects that required date parameters to do things that you would typically see in an Analysis Services cube.  My client needed to see data brought back Week to Date, Month to date,  Year to date, and on with a rolling week.  My first suggestion of course was to create a cube to not only to do these calculations but get many other benefits.  Unfortunately, they did not take my advice so I had to use the SSRS expression language to return the results they were looking for.

I thought this made for a great blog topic so I could refer back to these whenever I need them.  All the reports had at least two parameters for start date and end date.  I needed to change the default values so they would default to show the above date range (YTD, MTD…etc).

End date would always have the current date or Today() in the expression language.  If you want time included then use Now().  The default value of the start date parameter will use the DateAdd function to return back a date that is a set number of units back.  The DateAdd function is constructed like this:

DateAdd(interval, number, date)

Argument	Description
Interval	This is the interval you want to add (Ex. Days, Months, Years…)
Number	Numeric value that represents the number of intervals you want to add
Date	The date to perform the addition to.

Expression I use to change my parameter default values.  These could be written many different ways and actually use VB in the customer code are but I wrote them this way if you’re not a VB expert.

Month over month

=DateAdd(“m”,-1,Today)

MTD

=DateAdd(“d”,-(Day(today)-1), Today)

Week over week

=DateAdd(“ww”,-1, Today)

or

=DateAdd(“d”,-7, Today)

WTD

=DateAdd(“d”,-DatePart(DateInterval.WeekDay,Today,0,0)+1,Today)

Year over Year

=DateAdd(“yyyy”,-1, Today)

YTD

=DateAdd(“d”,-DatePart(DateInterval.DayOfYear,Today,0,0)+1,Today)

Notice in the YTD and WTD expression I’m calling a DateInterval function that is actually a Visual Basic function but does not require you do add anything to the custom code section of your report.