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

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

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

Drill through --relative path

I'm trying to use the drill-through feature to link to a report in different solution, on the same server, but in a different project. Local help files say: "The list of report names includes all reports in the current report server project. If the drillthrough report is on the report server but is not in the project, type the name of the report. The report name can contain a relative or absolute path to the report."

The report will be passing a parameter to the target report through drill-through. The target report is located on different solution and project but on a same server (report manager). Any help will be be appreciated.

Can someone give an example of a relative path? Nothing I've tried has worked, and I've posted this on sqlteam to no avail.

Thanks,

Lalon

if your base directory is "c:/SomeProgram/"

relative_path -> absolute path
"dir1/dir2" -> "c:/SomeProgram/dir1/dir2/"
"../dir3" -> "c:/dir3/"

i don't know what and how you are using the relative paths, but if you are using paths in directly in code, in some programming languages you have to double the '/' ("../dir3" -> "..//dir3")

Amaral

Drill through in Olap Cube and implement at Share Point

Hi,

I want to integrate a Olap cube to sharepoint in which we can achieve drill through and multilingual also.

Please help me out to get solution of 2 problems.

Thanks

For the OLAP cube drillthrough from SharePoint, I'm using 3rd-party OLAP Web Parts, which expose cube-defined Reporting Actions. When the user selects such an action, a Reporting Services report is launched, which executes the MDX drillthrough query (using the OLE DB for OLAP Provider) and displays tabular results. Relevant current cube co-ordinates for the drillhrough are passed as URL paramters to the report.|||Deepak,

Which 3rd party tools which expose cube actions have you had the best luck with?

Thanks,
Todd
|||

Hi Todd,

I'm using DSP Portal Edition 4.3 right now, but perhaps ProClarity for SharePoint Portal Server will also work...

Drill through in Olap Cube and implement at Share Point

Hi,

I want to integrate a Olap cube to sharepoint in which we can achieve drill through and multilingual also.

Please help me out to get solution of 2 problems.

Thanks

For the OLAP cube drillthrough from SharePoint, I'm using 3rd-party OLAP Web Parts, which expose cube-defined Reporting Actions. When the user selects such an action, a Reporting Services report is launched, which executes the MDX drillthrough query (using the OLE DB for OLAP Provider) and displays tabular results. Relevant current cube co-ordinates for the drillhrough are passed as URL paramters to the report.|||Deepak,

Which 3rd party tools which expose cube actions have you had the best luck with?

Thanks,
Todd
|||

Hi Todd,

I'm using DSP Portal Edition 4.3 right now, but perhaps ProClarity for SharePoint Portal Server will also work...

drill down report using Report sevices help me to get a solution

Hi all

I am new in Sql Server reporting services ..So please help me to clarify my doubt

Please help me to know whether it is possible to generate a drill down report using Report sevices. If it possible please let me know how it can be generated

For Example

Measures

Quarter1 Quarter2 Quarter3#

Profit Profit Profit

====================================================================================

Dimension

Product1 200.50 1500.75 -200.40

Product2 5000.00 4000.00 6000.00

==================================================================================

In the above example Quarter 3 for product 1 shows los in such cases I want to drill down the transaction to get the detailed inormation and to find out the reason how the loss came..

Please help me to get such type of drill down is possible in measures to get the detailed report

With regards

Polachan

In your sample report, I assume these are aggregated values that are summed by some sales? You will want to use a table that holds the sales. That table can be collapsed by the quarters to show the sum of the product for that quarter and the expanded to show the details of the sales.

I know that this reply does not go into detail on how this is done, but it might be a start.

|||

Dear guyinkalamazoo3

Thank u for ur reply to my quries

is it possible to drill down the measure object. ?...

For example in quarer 3 for product1 shows -200.40 loss.

So I want to drill down

Profit

-200.400
--> Customer Product Date Invoice Amt
- -
x Product1 01/10/2007 001 -100
y Product1 01/10/2007 001 -100.40

Can I give this type of drill down is possible in Measure attributes Profit. So I can find in which transaction occure loss to my organisation

Please help me to get a solution for this...

|||

Unfortunately, I hope somebody else might be able to help you with this problem as I am not that familiar with using cubes and OLAP in reporting. This is something that I am just learning myself. I am sure that the technical aspects for drilling down into the actual data in the reports is the same, but I am unfamiliar with data from OLAP.

Sorry I could not be of any further help.

|||

I am just starting on Reporting Services myself, and just finished my first drill down report. What you describe sounds like you should be able to do it.

There is a series of tutorials for Reporting services. The link below is to part of one. This section has 6 lessons that build up to lesson 6 - a drill down report.

http://msdn2.microsoft.com/en-us/library/aa337432.aspx

Hope this helps point you in the right direction.

Shirley