Displaying various instances of the same report in one report

Here's my problem. I have a report that displays information for one
office. The users are now asking for a master report where they can
display all of the offices in their group. I'm thinking I can do this
with a sub report but I don't know enough about subreports to set it
the passing of multiple report parameters and the sql server book's
how to is a piece of crap.
Where else can I find some examples of how to set this up?
Is there a better way to do this?
Thanks in advance for the help and if you need something cleared up let
me know.
MathiasHi, Mathias
from what I understand, it seems like you are gonna have the following
scenario:
create a report that will query for all offices, and then group by office
group categoriy.
If so, you can create you query (sproc or adhoc) to take in the office group
identifier/name as a parameter, and filter with a Where clause in the query.
The report rdl will have a parameter for the office group id/name which you
pass into that query, and will retrieve a list of all offices for that
particular group.
On the item that displays, say, the name of the office, you can go to the
textbox properties window and setup a hyperlink to another report (your
existing one), passing the appropriate office id/name, which your current
office report will use
to retrieve detailed information on.
Another way to go about this: in you "master report", drop a sub report
element, and in the properties, set the report rdl of the original report,
and for parameters, pass in the field from the master report containing the
office id into the sub-report (your current report).
so you will have the following layout:
user enters report parameter Office Group: <value>;
query executes filtered to that office group, and returns a list of office
name/ids;
rdl renders the list of office names returned by the query in a table layout;
within table, the detail row will contain a cell with a subreport pointing
to your original "office info" rdl, and passing the office id field to the
office id parameter of the subreport:
<begin table>
<begindetailrow>
pass Fields!officeID.value =>subreport (current report)
parameter Parameters!officeid.value
<enddetailrow>
<endtable>
Note, you could modify your original query so that your grouping and logic
is done on the query side in one stored procedure, and avoid having to deal
w/ subreports, by joining the appropriate tables and building your result set
in the query w/ all of the office details there. This reduces some of the
overhead on the report server having to render subreports.
hope this helps you out.
--
Regards,
Thiago Silva
"Mathias" wrote:
> Here's my problem. I have a report that displays information for one
> office. The users are now asking for a master report where they can
> display all of the offices in their group. I'm thinking I can do this
> with a sub report but I don't know enough about subreports to set it
> the passing of multiple report parameters and the sql server book's
> how to is a piece of crap.
> Where else can I find some examples of how to set this up?
> Is there a better way to do this?
> Thanks in advance for the help and if you need something cleared up let
> me know.
> Mathias
>|||Thiago Silva
Thank you very much for taking the time to respond. I think i'm going
to have to go with the second option and use the subreport. my users
want to be able to see the various reports all at one time. I set up
the query to bring back the office id's in their respective groups
however when I pass that id to the subreport only the first offices'
report is generated.
for example there are 10 offices comming back it will only display the
first office. How do I tell the subreport to move on to the next
offices?
I tried using the value straight from the query by doing this
=Fields!ReportingOfficeID.Value which did not work.
I also tried placing the value returened from the query into a report
parameter this also did not work. I tried with the multi- value box
selected and with the box not selected.
where am I going wrong?
on a side note that I should have mentioned to start with I am using
Reporting server 2005.
thanks once again for all of the help. And If you need me to clear
something up let me know.
Mathias|||Mathias,
could you provide a sample of the data that you're using for the report, and
how you want the report layout to be? I am trying to understand exactly what
you want versus what you're getting right now.
--
Regards,
Thiago Silva
"Mathias" wrote:
> Thiago Silva
> Thank you very much for taking the time to respond. I think i'm going
> to have to go with the second option and use the subreport. my users
> want to be able to see the various reports all at one time. I set up
> the query to bring back the office id's in their respective groups
> however when I pass that id to the subreport only the first offices'
> report is generated.
> for example there are 10 offices comming back it will only display the
> first office. How do I tell the subreport to move on to the next
> offices?
> I tried using the value straight from the query by doing this
> =Fields!ReportingOfficeID.Value which did not work.
> I also tried placing the value returened from the query into a report
> parameter this also did not work. I tried with the multi- value box
> selected and with the box not selected.
> where am I going wrong?
> on a side note that I should have mentioned to start with I am using
> Reporting server 2005.
> thanks once again for all of the help. And If you need me to clear
> something up let me know.
> Mathias
>|||Thiago Silva
I figured out how to solve my problem. I ened up following the steps
laid out in this forum post.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=299810&SiteID=1
what I did was place the subreport in a list and then pass the list of
10-15 office id's to the list / subreport. this allowed the master
report to display all 10-15 different subreports.
Thank you very much for all of you help with this.
Mathiassql