UNION multiple datasets in SSRS outside query

Question

I have two data sets with exactly the same fields that I would like to combine into one dataset.

I cannot put a UNION/JOIN and make it return one dataset in the query as all data is encrypted and gets decrypted by an assembly reference loaded in SSRS (so I cannot run the necessary WHERE's unless I use a filter once the data is in SSRS).

I can return the second dataset using Lookup/LookupSet however I am using this data in a bar chart so (somehow) need both sets of data to display on it. If I was using a tablix I would be able to "hack" it by putting a second tablix without headers underneath the main tablix and show it like that. Unfortunately being a graph I cannot do this.

I also tried running it as one dataset, returning all values and then running "filters" based on category groups in the chart however for whatever reason [bug in SSRS?] the filter on these filters the whole result set, not just the one category group.

Is what I am trying to do possible in SSRS? Seems so basic but after a week of trying I have just about given up!

Hannover Fist Hannover Fist · Accepted Answer · 2015-04-13T20:24:52

Merging two datasets is not possible but there may be a way to fake it for your chart purposes by doing a SUM of LookUpSet. What ever field is used as your value needs to be looked up using whatever criteria is being used for your axis.

Let's say you're displaying sales by month. Your value field is SUM(Fields!TotalSales.value) and your date field is Fields!month.value. You'd want to add the value from dataset1 and lookup the total from dataset2 - like:

=Fields!TotalSales.value + Code.SumLookup( LookupSet(Fields!month.Value, Fields!month.Value, Fields!TotalSales.value, "Dataset2") )

Unfortunately, SSRS doesn't let you sum a lookupset (so what's the point of it?), so you have to use custom code to do it.

Function SumLookup(ByVal items As Object()) As Decimal
  If items Is Nothing Then
    Return Nothing
  End If

  Dim suma As Decimal = New Decimal()
  suma = 0

  For Each item As Object In items
    suma += Convert.ToDecimal(item)
  Next

  Return suma
End Function

Stolen from: How to combine aggregates within a group with aggregates across groups within SSRS

You're data is obviously different but the concept should be the same.

UNION multiple datasets in SSRS outside query

1 Answers