SSRS subreport with parameter rendering only one record of group

Question

I want to build a report that returns records for a group of employees and by specific dates and supervisor, and renders the report by employee (as in a batch). I am doing this with a main report and 3 subreports. My subreports work individually and I just got one subreport to render from main but only the 1st record in the group displays.

Dataset on main has 3 parameters, USERID, BegDate, EndDate. Dataset on subreport take EMPID, BegDate, EndDate. I inserted subreport on main and subreport parameters as follows:

    Name      Value

    CurEmp     =First(Fields!EMPID.Value, "DataSet1")
    BegDate    [@BegDate]
    EndDate    [@EndDate]

Where CurEmp is used in the where clause of DataSet1/sql query i.e. WHERE EMPID = @CurEmp

Main report parameters in DataSet1 are:

Name     Value
@USERID     [@USERID]
@BegDate    [@BegDate]
@EndDate    [@EndDate]

Now I realize that the =First in the Expression in Value of parameter is supposed to render only the first record but nothing I change it to will render at all. I have done tutorials and googled for 2 days. I can get the simple subreports to work but nothing seems to apply to what I am trying to do. Can someone direct me to an example that applies to my situation?

BTW I have set my VS2008 environment to a Business Inteligence Project.

UPDATE: I have added my sql queries below to help explain per suggestion by @Sam. I actually was starting to look at the fact that maybe I am writing the queries wrong. I am confused about what the main report query should be compared to subquery....I hope this helps to clarify my meaning.

Main report dataset =

SELECT
v.EMPID,
UPPER(p.FirstName + ' ' + p.LastName)  as EmpFullName, 
v.dtmDate,  
v.TCode,  
v.Hours, 
v.ProjectNo,  
from vwPersonSummary v
join tblPerson t on v.EMPID = p.EMPID
WHERE  v.USERID = @USERID  --Supervisor’s EMPID
AND 
v.dtmDate BETWEEN @BegDate AND @EndDate  
ORDER BY v.EMPID, v.dtmDate

Subreport dataset=

SELECT
v.EMPID,
UPPER(p.FirstName + ' ' + p.LastName)  as EmpFullName, 
v.dtmDate, 
v.Tcode,  
v.Hours, 
v.ProjectNo  
from vwPersonSummary v
join tblPerson t on v. EMPID = t. EMPID
WHERE
v.EMPID =  @CurEmp 
AND v.dtmDate BETWEEN @BegDate AND @EndDate 
ORDER BY v.dtmDate

If we have some sample data it would make it bit clearer. Where CurEmp is used in the where clause of DataSet1/sql query i.e. WHERE EMPID = @CurEmp is confusing as well. — Sam
So what you are saying is that no records are being populated to the second report? Without any filters just bind the EmpId field from DataSet1 to a grid in the second report and check if you can get data there. I just want to check data gets populated to DataSet1. — Sam
ok, I placed a table inside a rectangle on main report and placed 2 fields, EMPID and EmpFullName in the table. The subreport is inside the same rectangle. In Preview all the employees in the supervisor's group are listed over several pages. Is that what you meant? Without the table, I get error message on subreport: "[rsWarningExecutingSubreport] Warnings occurred while executing the subreport ‘Subreport2’. [rsNone] The value provided for the report parameter 'CurEmp' is not valid for its type." — Doreen
Ok so that means we get data for dataSet1 in sub report. Just to clarify when you run this you don't get any record in sub report or you get records of only one employee? — Sam

Sam Sam · Accepted Answer · 2016-06-28T10:42:54

So your fix/answer is going to be something like below.

When defining the the parameter from the main report it should be something like this.

=Join(LookupSet(1,1,Fields!EMPID.Value,"DataSet1"),",")

Once that is defined make sure that the sub report parameter Data Type is set as Text and Allow multiple values have been checked.

After that for the data set of the sub report don't set a where condition. Instead in Data Set properties go to Filters.

In Expression set something like

=CStr(Fields!EMPID.Value)

and set Operator as In and set the Value as something like below.

=Split(Parameters!EmpId.Value(0),",")

Well basically that's it. Hope this helps.

SSRS subreport with parameter rendering only one record of group

1 Answers