Issue using datasets from different databases in a summary SSRS report

Question

I've a report to show a summary by users, getting data from 2 database. I wrote individual dataset to get it for each column, but as the values could be come duplicate , I don't know how can I get all the records of each user

I'm using UNION ALL in my query to get the data from 2 databases

MainDataset

Select Count(*) as total, Username from database1
where my condition...
group by Username   
UNION ALL
Select Count(*) as total, Username from database2
where my condition...
group by Username 

Username    Total
User1       2  
User2       1  
User3       3
User4       4
User5       10
User6       5

Dataset 2

Select Count(*) as totalCol2, Username from database1
where condition for this column...
group by Username   
UNION ALL
Select Count(*) as totalCol2, Username from database2
where condition for this column...
group by Username 

Username    totalCol2
User1       2  
User2       1  
User2       1  
User3       3  
User3       2  
User4       1  
User5       2  
User5       3  
User6       4

I'm using MainDataset to show the rows in my report and for each column I need to show summary from Dataset2:

Username    Total   totalCol2
User1       2   2  
User2       1   2  
User3       3   5
User4       4   1
User5       10  5
User6       5   4

I tried to use lookup but only got the first match from dataset2, sum function with condition but doesn't work as well. Can someone give me some idea if can I do something in SSRS.

The report have more than 10 column and each one come from differents dataset.

Jamie F Jamie F · Accepted Answer · 2012-02-17T02:58:22

The simplest answers are to change your queries for your datasets so they don't return multiple rows: combine the rows in your query.

The really simple version of this is to just wrap your existing query for Dataset2 in an outer Select ... Group By:

Slightly revised Dataset2:

SELECT
   Username,
   SUM(totalCol2) as totalCol2
FROM
(
   SELECT Count(*) AS totalCol2, Username FROM database1
   WHERE condition for this column...
   GROUP BY Username   
   UNION ALL
   SELECT Count(*) AS totalCol2, Username FROM database2
   WHERE condition for this column...
   GROUP BY Username
) AS tableA
GROUP BY Username

A little bigger rewrite would give you:

SELECT
   Username,
   COUNT(*) AS totalCol2
FROM
   (SELECT UserName FROM database1
    WHERE Condition
    UNION ALL
    SELECT UserName FROM database2
    WHERE Condition
    ) AS tableA
 GROUP BY Username

Either of those queries above will work nicely with a lookup function.

But if your database1 and database2 are the same for all of the ten queries, then I would try to combine them. Maybe something like:

SELECT
   Username,
   SUM(Col1) AS totalCol1,
   SUM(Col2) AS totalCol2,
   ...
FROM
   (SELECT
      UserName,
      CASE WHEN [columns meet condition for Col1]
       THEN 1 ELSE 0 END AS Col1,
      CASE WHEN [test for condition of Column2]
       THEN 1 ELSE 0 END AS Col2,
      ... 
    FROM database1

    UNION ALL
    SELECT
      UserName,
      CASE WHEN [columns meet condition for Col1]
       THEN 1 ELSE 0 END AS Col1,
      CASE WHEN [test for condition of Column2]
       THEN 1 ELSE 0 END AS Col2,
      ... 
    FROM database2
    ) AS tableA
 GROUP BY Username

Issue using datasets from different databases in a summary SSRS report

1 Answers