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

LVL 3
SimonThompsonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PFrogCommented:
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
0
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?
0
PFrogCommented:
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
  =not(Fields!x.Value=Parameters!hSelectedUser.Value)
etc.

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)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB Reporting Tools

From novice to tech pro — start learning today.