Report Builder Matrix Report Timing out when expanding hierarchies.

Question

Report Builder Matrix Report Timing out when expanding hierarchies.

Brad Rhine

SSC Enthusiast

Points: 105
More actions
December 18, 2007 at 9:00 am

#201864

We have created a fairly complex Report Builder Matrix Report with multiple hierarchies. As we drill down into the detail of the second level the report seems to go into a 2 to 3 minute "Report Running" , then it times out on us.
What is interesting is the Trace by the Administrator is showing that the SQL is completing in mere seconds. So our conclusion is that the issue is with the SSRS server.
Suggestions and/or solutions would be appreciated. A work around is to export it directly to Excel and drill down from there.
Thanks,
Brad

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

It sounds like you are returning too much detail to the report server and it is timing out aggregating and displaying the data. Have you tried reducing the details returned by your SQL Server so that the RS has less to do? Have you tried rendering a report with just the second level data to see if that will render?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Brad Rhine SSC Enthusiast Points: 105 More actions · Answer 2

Hello,

We have tried that and it does return the second level (as a first level) very well. It seems to be a combination of the two together causes issues.

How do we change it to receive more or faster?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Is the drill down a sub-report or a layer in the matrix?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Brad Rhine SSC Enthusiast Points: 105 More actions · Answer 4

Brad Rhine

SSC Enthusiast

Points: 105

December 19, 2007 at 8:14 am

#761419

It is a layer in the matrix.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

Could you use a link to another report instead of bringing all the data down?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Brad Rhine SSC Enthusiast Points: 105 More actions · Answer 6

That is a good suggestion, yet we were testing the limitations of Report Builder and have run into a few similar to this. The users current product gives them this capability and they want and need it.

I am very interested in your original thought : "It sounds like you are returning too much detail to the report server "

How do we set that to allow some type of bursting or expansion to the Report Server?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

Brad Rhine (12/19/2007)
That is a good suggestion, yet we were testing the limitations of Report Builder and have run into a few similar to this. The users current product gives them this capability and they want and need it.
I am very interested in your original thought : "It sounds like you are returning too much detail to the report server "
How do we set that to allow some type of bursting or expansion to the Report Server?

I'm not sure you could do bursting\expansion to the report server. What I meant was that you may be returning a million detail rows to the report server and making it do all the aggregations. For instance:

Select

salesperson,

territory,

orderdate,

orderqty,

salesamt

From

sales

Then in the report you are using MonthName(Month(orderdate)) to create an ordermonth field that is one of your columns. So all your data is going to be grouped on the report server by ordermonth. YOu could leverage T-SQL and do that grouping in the query like this:

Select

salesperson,

territory,

DatePart(Month, orderdate) as ordermonth,

Sum(orderqty) as orderqty,

Sum(salesamt) as salesant,

From

sales

Group By

salesperson,

territory,

DatePart(Month, orderdate)

Now you are returning say 250,000 rows with some of the grouping already done reducing the load on the network and the Report Server. You may even want to do any ordering in T-SQL as well since the problem is on the report server, not in the returning of the data.

Without actually seeing your code that is the best I can do.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question