Create a report using 2 different data sources

Question

I need to create a report which uses 2 different datasets on different servers.

The queries run fine when run separately. But I am unable to link them.

Query1:

SELECT Employee.[Person Number],
       Employee.[Ethnic Origin],
       COUNT(Employee.[Person Number]) AS Staf_Count
FROM Employee
GROUP BY Employee.[Person Number],
         Employee.[Ethnic Origin]

Query2:

SELECT DISTINCT y.StYear,
                s.Student_ID,
                s.Ethnicity,
                COUNT(s.Ethnicity) AS EthCount,
                CASE s.STUD_Ethnicity
                    WHEN 31 THEN 'White - English / Welsh / Scottish / Northern Irish / British'
                    WHEN 32 THEN 'White - Irish'
                    WHEN 33 THEN 'White - Gypsy or Irish Traveller'
                    WHEN 34 THEN 'White - Any Other White background'
                    WHEN 35 THEN 'Mixed  / Multiple Ethnic group - White and Black Caribbean'
                    WHEN 36 THEN 'Mixed  / Multiple Ethnic group - White and Black African'
                    WHEN 37 THEN 'Mixed / Multiple Ethnic group - White and Asian'
                    WHEN 38 THEN 'Mixed  / Multiple Ethnic group - Any Other Mixed / multiple ethnic background'
                    WHEN 39 THEN 'Asian / Asian British - Indian'
                    WHEN 40 THEN 'Asian/ Asian British - Pakistani'
                    WHEN 41 THEN 'Asian / Asian British - Bangladeshi'
                    WHEN 42 THEN 'Asian / Asian British - Chinese'
                    WHEN 43 THEN 'Asian / Asian British - Any other Asian background'
                    WHEN 44 THEN 'Black / African / Caribbean /  Black British - African'
                    WHEN 45 THEN 'Black / African / Caribbean /  Black British - Caribbean'
                    WHEN 46 THEN 'Black / African / Caribbean /  Black British - Any other Black / African / Caribbean background'
                    WHEN 47 THEN 'Other ethnic group - Arab'
                    WHEN 98 THEN 'Any Other'
                    WHEN 99 THEN 'Not provided'
                END AS Ethnicity
FROM dbo.STUDstudent s
LEFT JOIN dbo.GNICodes g ON s.Ethnicity = g.GNIC_Code
INNER JOIN dbo.STYRstudentYR y ON s.Student_ID = y.Student_ID
WHERE STYR_Year = @Year
GROUP BY s.Student_ID,
         s.Ethnicity

O/P: Should look like below:

I have checked and 2nd database is listed as linked server under first one.

Report design looks below:

Created a View combining both datasets:

CREATE VIEW Staff_Student_Ethnicity
AS
        SELECT  DISTINCT 
    [Ethnic Origin] COLLATE SQL_Latin1_General_CP1_CI_AI AS Ethnicity, 
    COUNT([Person Number]) AS Staf_Count
    FROM  Employee.[Monitoring with Organisation As At Evaluation Date]
    GROUP BY  [Ethnic Origin]

    UNION ALL

    SELECT DISTINCT 
    Ethnicity COLLATE SQL_Latin1_General_CP1_CI_AI , 
    COUNT(STUD_Ethnicity) AS StudCount
    FROM SQL10.NG.[dbo].[Student_Ethnicity]
    GROUP BY Ethnicity

But after executing the View, I just see Ethnicity and Staf_Count fields and StudCount is missing..Please let me know where am I going wrong...

S3S · Accepted Answer

Thanks for the error and screen shot in your example Aruna. I'm sure someone with more SSRS knowledge will explain why aggregated columns must originate from the same source as your category axis identifier, I just know this is the case in many visual platforms such as Spotfire and Tableau and seems to be the case here. However, there's a work around.

Instead of bringing in TWO separate data sources from separate data elements, handle the relations on the server side. Since your servers are already linked, create a STORED PROCEDURE on one of the servers which combines both of the data tables. You can much easier control the relation of the data and the expected results. Then, use this as your data source for your SSRS report.

CREATE VIEW Staff_Student_Ethnicity
AS
    SELECT  DISTINCT 
    [Ethnic Origin] COLLATE SQL_Latin1_General_CP1_CI_AI AS Ethnicity, 
    COUNT([Person Number]) AS Staf_Count,
    NULL as StudCount
    FROM  Employee.[Monitoring with Organisation As At Evaluation Date]
    GROUP BY  [Ethnic Origin]

    UNION ALL

    SELECT DISTINCT 
    Ethnicity COLLATE SQL_Latin1_General_CP1_CI_AI , 
    NULL as Staf_Count,
    COUNT(STUD_Ethnicity) AS StudCount
    FROM SQL10.NG.[dbo].[Student_Ethnicity]
    GROUP BY Ethnicity

EDIT FOR DRILL DOWN

If you want to have the details of your aggregations to build a layered report in SSRS, then you are going to have to bring in those details from your query. Therefore, you might as well do the aggregations in SSRS for your counts and not have two queries. A simple procedure as the one below should combine your data for you. Then, you can bring this into SSRS and aggregate the columns as you want. I put in a PersonType field for you to use in your IF statements in SSRS. i.e. IF([PersonType] = 'Student' then Count([PersonNumber])) as [StudentCount]. I know this syntax isn't accurate for SSRS, but I am just giving an example of the logic.

CREATE PROCEDURE usp_staff_student_sthnicity()
AS
    SELECT  DISTINCT 
    [Ethnic Origin] COLLATE SQL_Latin1_General_CP1_CI_AI AS Ethnicity, 
    [Person Number] AS PersonNumber,
    'Staff' as PersonType
    FROM  Employee.[Monitoring with Organisation As At Evaluation Date]

    UNION ALL

    SELECT DISTINCT 
    Ethnicity COLLATE SQL_Latin1_General_CP1_CI_AI, 
    Student_ID AS PersonNumber
    'Student' as PersonType
    FROM SQL10.NG.[dbo].[Student_Ethnicity]

Then, you can enable a user to click on the StudentCount or EmployeeCount fields and see the students / employees that are associated with this aggregation. Steps on how to do this is subject to a new question post on StackOverflow after research though.

Create a report using 2 different data sources

Answers (2)

Related Questions