The What

If you need to join multiple datasets inside SSRS, perhaps because of different sources, grains of detail etc, then you often need to aggregate over both datasets.

In SSRS, you can easily perform aggregations over another dataset but it can be tough to do this based on a grouping factor in your main dataset.

A key example of this might be Sales and Purchases – you want to show both of these by month but they come from two different data sources.

You could build two tables that appear to be just one table but this can be really clunky. Instead, you want just one table with the month, the total sales, and the total purchases in.

Although there’s no tidy way of doing this built in, you have the power to add your own functions to SSRS using the Code window of the report’s properties. Provided here is a block of VB script that can be added to your SSRS report to allow you to do those tricky aggregations as if they were just another built in function.

I call it AggLookup.

Adding the code

To use this code, it needs to be added to the custom code of the report. To do that:

  1. right click in the area behind the report
  2. select Properties
  3. select Code
  4. copy and paste the below into the Code window
  5. hit OK

The Code

The code is quite long so I have provided it on github.com/OzLocke/SSRSAggLookup.

This means you can get it from GitHub, log issues with it, and can even contribute improvements to it. If you’ve not used GitHub before, check out the handy Getting Started guides.

How to use it

All you need to do to use this code is enter the following expression into a cell:

=code.AggLookup([aggregation], LookupSet([localValue], [matchValue], [returnValue], [dataset]))

The aggregation should be entered in lower case, as a string, and the available aggregations are count, sum, min, max and avg.

So this would allow you to do build a table for your Sales data grouped by month and then sum the Purchase data by that month also, so the figures are unified into one table.

This also has the advantage of enabling dynamic aggregations via a parameter containing the aggregation names for both primary and secondary data sets which is easier than writing SWITCH statements that swap functions. This means you can have one report that shows the counts or sums of values depending on the user’s preference without having to maintain multiple reports or a much bigger report.

Aggregate on a Lookup in SSRS
Tagged on:                 

6 thoughts on “Aggregate on a Lookup in SSRS

  • 09/02/2016 at 23:35
    Permalink

    Just what I needed to get me out of a tricky spot, thanks.

    One comment; there seems to be a really weird bug in SSRS (2008R2 anyway) which causes lookupset to return #error once you get past the first page of your report if the destination_expression refers to a calculated field in the second dataset. This has the effect of causing the aggregation to fail, since it’s no longer got any data to work with.

    Reply
  • 19/08/2016 at 19:09
    Permalink

    How do I adjust the code to return a count of not null or empty string. I only want to count if the data has data in it (in this case a date would be entered and I want to count the dates and ignore null and empty string)

    Reply
    • 20/08/2016 at 13:33
      Permalink

      Hi Stephen, this could be done relatively easily but I don’t have an SSRS instance available right now to test it.

      I think an additional condition like the numeric check could be performed such that it checks for non-null & non-blank and increments a variable. The code could look like this AndAlso check on Stack Overflow.

      Oz no longer works in the field and I haven’t touched SSRS in years – it’d be fab if you could fork the code and make your alterations. You could then either submit a Pull Request to merge it back in or we can amend the post to reference your copy!

      Cheers,
      Steph

      Reply
      • 20/08/2016 at 13:36
        Permalink

        PS If you’ve not used GitHub before, I’m happy to help you through the process.

        Reply
  • 12/01/2017 at 07:47
    Permalink

    Hi Steph
    I get a error when I use your code in my report:
    I use this call:
    =code.AggLookup(“count”(LookupSet(

    Reply
    • 13/01/2017 at 08:57
      Permalink

      Hi Dan, it looks like you’re missing a comma i.e. should be =code.AggLookup(“count”,(LookupSet( but without more info I can’t be sure.

      Reply

What do you think?