Drilldown question

Hi,

I've been playing around with SSRS 2005 to see if it might be the solution for some reporting requirements I have been given, but I still haven't found a way to do exactly what I need. Basically I've been asked to provide a report which allows drilling down multiple levels of a client hierarchy to show some sales figures, sounds pretty simple right? The report will look like this (not showing sales figues here just client hierarchy)...

+ Client A
- Client B
- Client C
- Client D
+ Client E
- Client F
- Client G
- Client H
- Client I
- Client J
+ Client K
+ Client L

As you can see they want to be able to expand & collapse rows to drill down through their client hierarchy. The hierarchy will be 3 or 4 levels deep.

I have seen that RS2005 supports a simple way to drill-down via grouping, but I don't want to do it this way because it means that I will have to have a query which retrieves all of the data right away which would take several minutes.

What I'd really like to do is to lazily load the hierarchy so that the data is loaded as the tree is expanded so that it won't take too long to render the report initially. I.e. when they click on "Client B" I'll hot the DB and get the sales figures for all children of Client B, in the case above thats Clients C & D.

Can this be achieved using sub reports or report linking? If anyone knows of some tutorials or examples that might be able to help me with this I would really appreciate it.

Thanks.

Lachlan

Hi

I am facing the same problem what you have stated. If you have got or found a solution please share it. It is quite urgent.

Thanking you in advance

regards

Sai

|||well, you could use several single reports i think
1. report all collapsed but root-rows and a link to another report (2. report) on each row
Query: WHERE hierachie level=1
2. report with the root rows and the rows below the row you clicked on 1.report
Query: WHERE hierachie level=1 or parent=<clicked row>
and so on ...

and all reports with the grouping you would usually have

but its very complex and wont be easy to implement i think

i also have to say that i've never tried it, its just an idea

greets
gerhard
|||

Hello Gerhard,

Can you please explain in detail. I have just started working on sql reporting services 2005 and I need a solution for a similar issue.

I need a drilldown report in the model given below.. Main values will be clientname, clientcode and total number of products purchased. When I expand details for each client I should see the SalesOrder #, Date, ProductPurchased , Qty and Price

I'm able to get this output:

ClientCode (MainGrouping)

SalesOrder# Date ProductPurchased Qty Price (DrilldownDetails )

But I'm not able to get this as output:

ClientCode ClientName Total# Purchased (MainGrouping)

SalesOrder# Date ProductPurchased Qty Price (DrilldownDetails )

I need it to really group by ClientCode. But the grouping line should display other fields related to that client..

How do I do this ?

Note : I have another question : I have tried the same with a Drill through report. The problem I'm facing here is, the drill thro detail report for each client opens in the same window, which I don't like. I want it to open in a new window. Is there a way to open a drill thro report in a new window ?

Thanks for your help in advance,

vidash