• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3299
  • Last Modified:

Passing Parameters into a Subreport

I have created a report which shows our company structure. The stored procedure lists all staff in the oganisation and their position, imagine Main Boss is Simon who has 2 subordinates Fred and Dave, Dave has three subordinates call Harry, Sally and Amy and Amy has one subordinate called Joe, This would look like this
Field1   Field2  Field3 Field4
Simon   Fred  <Null>  <Null>
Simon   Dave  Harry  <Null>
Simon   Dave  Sally   <Null>
Simon   Dave  Amy     Joe  

I designed the sp this way to make the report easy to design as i am simply grouping by the first field  and then on 2nd Field.
This report looks great and works perfectly.

But now i want to have a subreport in the main report which will give me more details about the selected employee in the form of a business card to the right of the structure. I have set the parameters in the main report manually to an employees ID and the subreport shows the correct record.

But how do i set it to show the business card for a different person depending on who they click??

What i want to be able to do is when a user clicks a name in the structure, i then need some code to say 'right, now set the subreports parameter to 66' and refresh the subreport.

Thank you all in advance

  • 2
1 Solution
If you definately mean subreport rather than a drillthrough (i.e you can see the business card in the same page as the company sructure) then you can do it using this...

Add an integer parameter to the main report "hSelectedUser", set this to default to 0 and be hidden.
For each cell in the company structure table (or matrix), set the navigation to be the main report, and pass the selected EmployeeID as the parameter for hSelectedUser.

Then, in the settings for the subreport, pass =Parameters!hSelectectedUser.Value as the value for the EmployeeID.

This will then show the subreport for the selected user.

If you get problems with it please shout
SimonThompsonAuthor Commented:
Thanks for that, its nearly what i want but i dont want the tree to reset each time i click a different person. The user could be 5 levels down and just wants to browse all the employees at that level. Any ideas on keeping the view as it was when they selected the employee?
You will have to reload the whole report, as there is no other way of achieving what you want.

However, when you're loading the tree, you know where the user was by the employeeID you have in the hSelectedUser parameter. You can use this to re-create the previous view.

I presume you've used group visibility for the tree functionality? If so, you can set the initial visibility of a group to an expression that checks the parameter. Something like

The only other way you could do this would be to move the sub report out into a standalone drill through report.
You could either load the drill through in the same window (happens by default), the user would have to click back to get to the main report. Or, load the drill through in a seperate window using Javascript.
  (sample code on my blog - http://www.purplefrogsystems.com/blog/?p=7)
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.

Join & Write a Comment

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now