Nested report?

I want to create a report that retrieves data when I click on different
items on the same report page.
What I want to do is to first get an employee, and the hours registered for
this person at this period.
When I click on the person's name, I want to show a new report, that shows
all the clients that this person has registered hours working with this
period.
When I click on a client, I want to show a new report, that shows all the
projects that this person has worked on for this client during this period.
The projects should preferably be showed inside the table showing the
clients, so you can see what client the projects are connected to.
I've managed to create this with 3 different datasets in one report.
Unfortunately, the report will get all the data at once, but I want it to
get the project data when you click on a client, not when you load the
report. Is this possible?
All help appreciated!
Kaisa M. LindahlYou are describing a scenario where RS shines. From what I can tell you what
to do what is called drill through (open up new report) and drill down.
First drill through. Create a new report called Client. The client should
have a query parameter that accepts employee. When you create a query
parameter RS automatically creates a report parameter. Test this report
standalone and make sure it works. Now, in your employee report click on the
employee field and change the text to blue and underlined (so your users
know to click on it). The right mouse click, properties, advanced
properties, navigation. Jump to Report. Pick the client report and then map
the parameter for the client report to your employee field value.
Drill down works by have a single dataset with all the information. So it
would contain both client and project information. Then you add groups and
change row visiblity appropriately and what you end up with is where there
will be a plus sign on the row showing the client. When clicked on it
expands and shows all the projects for that client. Read up in help on how
to do this.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
What you are describing is called drill through reports. You should have
three reports: employee, clients, Instead of having all the datasets in one
report,
"Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
news:uWSLa3v0EHA.1860@.TK2MSFTNGP15.phx.gbl...
>I want to create a report that retrieves data when I click on different
> items on the same report page.
> What I want to do is to first get an employee, and the hours registered
> for
> this person at this period.
> When I click on the person's name, I want to show a new report, that shows
> all the clients that this person has registered hours working with this
> period.
> When I click on a client, I want to show a new report, that shows all the
> projects that this person has worked on for this client during this
> period.
> The projects should preferably be showed inside the table showing the
> clients, so you can see what client the projects are connected to.
> I've managed to create this with 3 different datasets in one report.
> Unfortunately, the report will get all the data at once, but I want it to
> get the project data when you click on a client, not when you load the
> report. Is this possible?
> All help appreciated!
> Kaisa M. Lindahl
>|||There are two ways in which you can achieve this, either using drill-down or
using linked reports. Or you could do a combination of both methods. Take the
drill-down method first:
Write a single query that returns employee, client and project information.
For simplicity, if we assume there is one table for each, the query will look
something like:
select col1, col2,.. from employee join client ... join project ...
Next create a grouped report with employee information in the top level
group, client information in the second group and project information in the
details. highlight the entire detail row (not just the textboxes) by clicking
on the row header on the left (it has three dashes). In the properties
window, make the Hidden property true (the property is a subproperty of the
Visibility property), and specify the ToggleItem as a textbox in the second
group row. This means that initially all detail rows containing project rows
will be hidden until you click a textbox in the group above.
You want to do this for the second group row too. Highlight the entire row
by clicking on the row selector. make the Hidden property true and specify
the ToggleItem property to be a textbox in the outer group row.
Now when you open your report, initially only employee information will
show. There will be a + icon to the left of each employee name to show that
there is hidden information. Click on the + and you will see all the clients
for that employee. Click on the + to the left of a client and you will see
all the projects for that client.
The second way is to create a separate report say for the projects
information. It should have a parameter that takes ClientID. In your first
report, just have the employee and client rows. In the first report, go to
the row with client information, click on a textbox and access the Action
property. Click on the ellipsis button to the right of it, and specify Jump
to Report option. Specify the report with project information, and specify
the parameters. This time the project information will appear as a popup
report.
HTH
Charles Kangai, MCT, MCDBA
"Kaisa M. Lindahl" wrote:
> I want to create a report that retrieves data when I click on different
> items on the same report page.
> What I want to do is to first get an employee, and the hours registered for
> this person at this period.
> When I click on the person's name, I want to show a new report, that shows
> all the clients that this person has registered hours working with this
> period.
> When I click on a client, I want to show a new report, that shows all the
> projects that this person has worked on for this client during this period.
> The projects should preferably be showed inside the table showing the
> clients, so you can see what client the projects are connected to.
> I've managed to create this with 3 different datasets in one report.
> Unfortunately, the report will get all the data at once, but I want it to
> get the project data when you click on a client, not when you load the
> report. Is this possible?
> All help appreciated!
> Kaisa M. Lindahl
>
>|||Thank you for answering, but at least the first method doesn't do what I
want to do. It makes a splendid report, I've already made one that does
this. My problem is that the report is a really big OLAP-based report, so
having a query that returns ALL data from all projects and clients takes a
few minutes. That's why I want a report that doesn't retrieve the project
data before you click on a link, to shorten the time for the time it takes
to open the report and see the top level data.
The second method looks interesting, so I might try that one out.
Also, Bruce L-C has made some suggestions in another reply, so I've got even
more things to try out.
Thanks, guys!
Kaisa M. Lindahl
"Charles Kangai" <CharlesKangai@.discussions.microsoft.com> wrote in message
news:8EC66727-246F-48B9-B2DC-E2E521B45804@.microsoft.com...
> There are two ways in which you can achieve this, either using drill-down
or
> using linked reports. Or you could do a combination of both methods. Take
the
> drill-down method first:
> Write a single query that returns employee, client and project
information.
> For simplicity, if we assume there is one table for each, the query will
look
> something like:
> select col1, col2,.. from employee join client ... join project ...
> Next create a grouped report with employee information in the top level
> group, client information in the second group and project information in
the
> details. highlight the entire detail row (not just the textboxes) by
clicking
> on the row header on the left (it has three dashes). In the properties
> window, make the Hidden property true (the property is a subproperty of
the
> Visibility property), and specify the ToggleItem as a textbox in the
second
> group row. This means that initially all detail rows containing project
rows
> will be hidden until you click a textbox in the group above.
> You want to do this for the second group row too. Highlight the entire row
> by clicking on the row selector. make the Hidden property true and specify
> the ToggleItem property to be a textbox in the outer group row.
> Now when you open your report, initially only employee information will
> show. There will be a + icon to the left of each employee name to show
that
> there is hidden information. Click on the + and you will see all the
clients
> for that employee. Click on the + to the left of a client and you will see
> all the projects for that client.
> The second way is to create a separate report say for the projects
> information. It should have a parameter that takes ClientID. In your first
> report, just have the employee and client rows. In the first report, go to
> the row with client information, click on a textbox and access the Action
> property. Click on the ellipsis button to the right of it, and specify
Jump
> to Report option. Specify the report with project information, and specify
> the parameters. This time the project information will appear as a popup
> report.
> HTH
> Charles Kangai, MCT, MCDBA
> "Kaisa M. Lindahl" wrote:
> > I want to create a report that retrieves data when I click on different
> > items on the same report page.
> > What I want to do is to first get an employee, and the hours registered
for
> > this person at this period.
> > When I click on the person's name, I want to show a new report, that
shows
> > all the clients that this person has registered hours working with this
> > period.
> > When I click on a client, I want to show a new report, that shows all
the
> > projects that this person has worked on for this client during this
period.
> > The projects should preferably be showed inside the table showing the
> > clients, so you can see what client the projects are connected to.
> >
> > I've managed to create this with 3 different datasets in one report.
> > Unfortunately, the report will get all the data at once, but I want it
to
> > get the project data when you click on a client, not when you load the
> > report. Is this possible?
> >
> > All help appreciated!
> >
> > Kaisa M. Lindahl
> >
> >
> >