Search code examples
ssrs-2012

Is there a way to get list of all column values from multiple datasets

We have 2 datasets. Both datasets have a column called Office. Say Dataset1 Office column has values London, Liverpool and Dataset2 the Office column has values Washington, California.

Is there any way in SSRS to do something like UNION ALL (but within SSRS) that is produce a list of all Office values in one table column?

Solution

  • There may be much more elegant ways of doing this but this is all I could come up with..

    You need to have a unique numeric id for each record

    Assuming you cannot simply union these together in your dataset query, if you can add a numeric ID to each record then you can do it (albeit a bit clunky)

    To start with I created two datasets ds1 and ds2 to hold the two lists of office names.

    For example the first dataset query was just this...

    DECLARE @t TABLE(OfficeID int, Office varchar(20))
INSERT INTO @t VALUES
(1, 'London'),
(2, 'Liverpool')
SELECT * FROM @t

    and the second was this.

    DECLARE @t TABLE(OfficeID int, Office varchar(20))
INSERT INTO @t VALUES
(10, 'Washington'),
(11, 'California')
SELECT * FROM @t

    As you can see, each office now has a unique numeric id

    Next I needed another dataset with a list of number that covered the entire range of office ids. In this example the range is just 20 numbers note: this work on SQL Server for other systems you'll have to come up with another method of getting a list of numbers

    So, the query for dsNums is

    declare @n table(num int)
insert into @n
select top 20 row_number() over(order by t1.number) as N
from   master..spt_values t1 
       cross join master..spt_values t2
SELECT * FROM @n

    Next I added a table to the report, reduced it to two columns and bound it to dsNums. The first column is just the [num] field.

    The second is an expression that does two lookups and takes the first non blank result. The expression was

    =IIF(
    LEN(LOOKUP(Fields!num.Value, Fields!OfficeID.Value, Fields!Office.Value, "ds1"))>0
    , LOOKUP(Fields!num.Value, Fields!OfficeID.Value, Fields!Office.Value, "ds1")
    , LOOKUP(Fields!num.Value, Fields!OfficeID.Value, Fields!Office.Value, "ds2")
    )

    If we run the report now we get.. enter image description here

    Finally we need to hide the empty rows. To do this I set the Row Visibilty expression to 

    =LEN(ReportItems!LookupResult.Value) = 0

    The result is this (obviously you don't need the num column but it was just for illustration)

    enter image description here