SQL Server Reporting Services 2005 and Multiple Data Sets

I am using the two queries below in a report I am developing in SQL Server 2005 Reporting Services. Query 1 is the main dataset for a table in the report and I am trying to use Query 2 to provide information in one cell of each row of said table.

I am trying to filter results by employee in each query through the use of the @EmpParam parameter; however, it does not correctly filter the results in Query 2 when I try selecting multiple employees in the report.

What am I missing? How are parameters used (or not used) when one uses multiple datasets in a report, specifically, in a single table in a report?

Query 1
                      PE.employee, PE.emp_name, PL.pe_date, PL.worked_hrs, (CASE SUBSTRING(PP.project, 1, 6) 
                      WHEN 'INSCOP' THEN 0 WHEN 'TRUTAN' THEN 0 WHEN 'MICBIF' THEN 0 ELSE PL.worked_hrs END) AS Billed_Hours, (CASE SUBSTRING(PP.project, 
                      1, 6) WHEN 'INSCOP' THEN PL.worked_hrs WHEN 'TRUTAN' THEN PL.worked_hrs ELSE 0 END) AS NonBilled_Hours, PL.rate_source, PL.project, 
                      PP.project_desc, SUBSTRING(PP.project, 1, 6) AS ProjectSubCode, PR.rate, dbo.PJEMPPJT.ep_id06 AS Salary, CASE SUBSTRING(PE.employee, 1, 1) 
                      WHEN '0' THEN 'Salary' WHEN '1' THEN 'Salary' WHEN '2' THEN 'C2C' END AS EmpType, dbo.PJALLOC.alloc_rate, CASE SUBSTRING(PP.project, 1, 6)
                       WHEN 'MICBIF' THEN ((dbo.PJEMPPJT.ep_id06 / 37.61904762) * (1 + dbo.PJALLOC.alloc_rate - .08) * (PL.worked_hrs)) ELSE 0 END AS Total_Revenue, 
                      CASE SUBSTRING(PP.project, 1, 6) WHEN 'MICBIF' THEN PL.worked_hrs END AS MICBIFHours, (CASE SUBSTRING(PP.project, 1, 6) 
                      WHEN 'INSCOP' THEN 0 WHEN 'TRUTAN' THEN 0 WHEN 'MICBIF' THEN 0 ELSE PL.worked_hrs END) AS Billed_Hours_Two, 
                      SUBSTRING(PE.employee, 1, 1) AS EmpNumSub, dbo.PJEMPPJT.labor_rate, PR.rate * PL.worked_hrs AS PeriodRevenue, PE.gl_subacct, 
                      PE.date_hired, PE.date_terminated
                      dbo.PJEMPLOY AS PE ON PL.employee = PE.employee INNER JOIN
                      dbo.PJPROJ AS PP ON PL.project = PP.project INNER JOIN
                      dbo.PJEMPPJT ON PE.employee = dbo.PJEMPPJT.employee LEFT OUTER JOIN
                      dbo.PJRATE AS PR ON PP.rate_table_id = PR.rate_table_id LEFT OUTER JOIN
                      dbo.PJALLOC ON PP.alloc_method_cd = dbo.PJALLOC.alloc_method_cd
WHERE     (dbo.PJALLOC.alloc_rate <> 0 OR
                      dbo.PJALLOC.alloc_rate IS NULL) AND (PE.employee IN (@EmpParam)) AND (PE.employee <> '000203') AND (PE.employee <> '100018') AND 
                      (PE.employee <> '100001') AND (PE.employee <> '000190') AND (PE.employee <> '000083') AND (DATEPART(yy, PL.pe_date) = DATEPART(yy, 
                      GETDATE())) AND (DATEPART(mm, PL.pe_date) <= DATEPART(mm, GETDATE()) - 1) AND (PR.tstamp IS NULL OR
                      PR.tstamp =
                          (SELECT     MAX(tstamp) AS Expr1
                            FROM          dbo.PJRATE
                            WHERE      (rate_table_id = PP.rate_table_id) AND (rate_key_value2 = PL.project))) AND (dbo.PJEMPPJT.effect_date =
                          (SELECT     MAX(effect_date) AS Expr1
                            FROM          dbo.PJEMPPJT AS PJEMPPJT_1
                            WHERE      (employee = PE.employee) AND (effect_date <= PL.pe_date))) AND (dbo.PJALLOC.alloc_rate <> 1 OR
                      dbo.PJALLOC.alloc_rate IS NULL)
GROUP BY PE.employee, PE.emp_name, PL.pe_date, PL.pe_date, PL.rate_source, PL.project, PP.project_desc, PL.worked_hrs, PP.project, PR.rate, 
                      dbo.PJEMPPJT.ep_id06, dbo.PJALLOC.alloc_rate, PL.crtd_datetime, dbo.PJEMPPJT.labor_rate, PE.gl_subacct, PE.date_hired, 
ORDER BY PL.pe_date, PE.emp_name
Query 2
                      CASE SUBSTRING(dbo.PJEMPLOY.employee, 1, 1) 
                      WHEN '0' THEN dbo.PJBILLABLEHOURS.W2SalaryBillabeHours WHEN '1' THEN dbo.PJBILLABLEHOURS.W2SalaryBillabeHours WHEN '2' THEN dbo.PJBILLABLEHOURS.C2CBillableHours
                       END AS EmpHours, dbo.PJBILLABLEHOURS.TimePeriodDate, dbo.PJEMPLOY.employee AS EmployeeOne, 
                      dbo.PJLABDIS.employee AS EmployeeTwo
                      dbo.PJLABDIS ON dbo.PJBILLABLEHOURS.TimePeriodDate = dbo.PJLABDIS.pe_date INNER JOIN
                      dbo.PJEMPLOY ON dbo.PJBILLABLEHOURS.TimePeriodDate >= dbo.PJEMPLOY.date_hired AND 
                      dbo.PJLABDIS.employee = dbo.PJEMPLOY.employee
WHERE     (DATEPART(yy, dbo.PJLABDIS.pe_date) = DATEPART(yy, GETDATE())) AND (DATEPART(mm, dbo.PJLABDIS.pe_date) <= DATEPART(mm, GETDATE()) - 1) 
                      (dbo.PJBILLABLEHOURS.TimePeriodDate <= CASE WHEN dbo.PJEMPLOY.date_terminated > '1/1/1900' THEN dbo.PJEMPLOY.date_terminated ELSE GETDATE()
                       END) AND (dbo.PJEMPLOY.employee IN (@EmpParam))
ORDER BY dbo.PJEMPLOY.employee, dbo.PJBILLABLEHOURS.TimePeriodDate

Open in new window

Who is Participating?
Auric1983Connect With a Mentor Commented:

If I'm following your question, when you specify for one employee, the report works, for multiple employees it gives you incorrect results.

The reason for that is a table object can only have one DataSet assigned, and you cannot nest tables in SSRS2005.  The report has no way to know what rows in DataSet2 it should be referencing.

Two options come to mind.  Create a view that gives you all the data you need for the report for both the Employee "header" information and the detail below, you would then use a group header on EmployeeID, display your "header" information in the group header, and the detail information below.

Option Two is to use a subreport linked on the Employee ID, as you CAN nest objects within a table.  

You will probably need to use a subreport for query 2 in order to get it to filter properly.
Shouldn't your subquery also reference the @EmpParam? the MAX(tstamp) may return the incorrect timestamp due to the sub query allowing an effective_date that doesn't coorespnd to a valid @EmpParam.

PR.tstamp =
                          (SELECT     MAX(tstamp) AS Expr1
                            FROM          dbo.PJRATE
                            WHERE      (rate_table_id = PP.rate_table_id) AND (rate_key_value2 = PL.project))) AND (dbo.PJEMPPJT.effect_date =
                          (SELECT     MAX(effect_date) AS Expr1
                            FROM          dbo.PJEMPPJT AS PJEMPPJT_1
                                          INNER JOIN PJEMPLOY PE
                                          ON (PE.employee = dbo.PJEMPPJT_1.employee) AND
                                                (PE.employee IN (@EmpParam))
                            WHERE      (employee = PE.employee) AND (effect_date <= PL.pe_date))) AND (dbo.PJALLOC.alloc_rate <> 1 OR
                      dbo.PJALLOC.alloc_rate IS NULL)
Ah..hmm looks like you are referenceing that in (employee = PE.employee)...back to the drawing board.
rdracer58Author Commented:
Subreports are out of the question as the data returned needs to be used in calculations in the report - values returned by subreports cannot be used in calculations. Consequently, I think the idea of creating a view, as recommended by auric1983 would probably be best.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.