SimonThompson
asked on
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
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
ASKER
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!hSelectectedUs
This will then show the subreport for the selected user.
If you get problems with it please shout