master Detail report

• ilaughforu

  SSC Rookie

  Points: 35

  More actions

  December 14, 2009 at 11:21 am

  #214827

  Hi all,

  I wrote a report with cascading parameter. First parameter gives groupname, and depending upon that selection, Dept name is populated.

  When I select the department names, list of employees are given. Upon Clicking upon the name of employee, his details are given. My problem is that,

  these reports are generated monthly so the manager doesn't want to go to each individual, to get a detailed report. What I wanted to do was upon selecting the department name, get detailed report of all the employees of that department, rather than just one.

  Right now, using subreport, i get detail of the first employee only, rather than each employee of that department.. Please help

  Pooja

• only4mithunc

  SSCrazy Eights

  Points: 8434

  More actions

  December 15, 2009 at 6:45 am

  #1092472

  Hi Pooja,

  In that case better don't use the sub report. Modify your sp so that it will return the details of all the employees under that department, then display this result in the main report itself.Also if required control the visibility by the expand/collapse property.

  Regards,

  MC

  Thanks & Regards,
  MC

• William S

  SSC Enthusiast

  Points: 117

  More actions

  December 20, 2009 at 8:51 pm

  #1094572

  Hi Pooja,

  I assume this is a data-driven subscription. I think the employee parameter needs to be changed to multi-value. Then update your SP to reflect the change, e.g. change to "id in @ID" in SP. Or, if it's a text dataset, you should change the filter to "id in (@ID)".

  I hope it makes sense.

  Regards

  Regards,

  William S.
  

• S. Kusen

  SSChampion

  Points: 10936

  More actions

  December 21, 2009 at 8:39 am

  #1094803

  As MC suggests, you'll need to modify the logic of the SP that you're passing the parameters to.

  If you're just using a SQL query, I would change it to an SP, and allow the employee ID to be null. Therefore, if all parameters are selected and employee ID is null, then you can set the logic in your stored procedure.

  An alternate way to do the same logic, would be to have a stored procedure load the employee ID or name values in the employee ID parameter. within that procedure, i would add a union statement to return a value like '**ALL**'

  for example:

  select last_name + ', ' + first_name as [full_name]

  UNION

  select '**ALL**' as [full_name]

  ORDER BY [full_name]

  set the default value for the drop down to be '**ALL**', and that would make it more intuitive for users of the report to make the options before the employee selection, and they would select ALL by default.

  Of course, the logic mentioned above with the employee ID being null would be changed to check for name = '**ALL**', then you would have your select statements based on that.

  Hope that helps,

  SK