Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ambi6088
Starting Member
4 Posts |
 Posted - 2011-07-19 : 08:56:15
|
| I have an issue with SSRS report and can you please share your thoughts on this?Issue:One of our report is having multi levels (7levels) of grouping and now the report execution takes long time because of large DB and timeout is happening/taking long time. We identified this is the problem with the levels of grouping. Is there any server level settings available which automatically picks the SSRS groups to SQL server? (In crystal report we have a similar kind of option called "perform grouping on server")Do you have any idea in SSRS for these kind of problem how to handle? kindly share your thoughts on this. Regards,Ambi |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-07-20 : 07:42:59
|
| nope. the only way you can do it is to do grouping in backend query and bring data as grouped format in report itself in procedure. You can chose to do it inline in backend query or add an intermediate table to store grouped results which you'll populate once daily by means of nightly jobs, depending on how volatile your data and how update you want the data to be displayed.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
ambi6088
Starting Member
4 Posts |
Posted - 2011-07-25 : 04:41:49
|
Thanks for your reply. We cannot move all the groups to sql side as it is required for the report output. Based on these groups we display the report formatting. Yes, we can find out and reduce few if we can move to SQL. Any other thoughts?quote:
Originally posted by visakh16
nope. the only way you can do it is to do grouping in backend query and bring data as grouped format in report itself in procedure. You can chose to do it inline in backend query or add an intermediate table to store grouped results which you'll populate once daily by means of nightly jobs, depending on how volatile your data and how update you want the data to be displayed.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-07-25 : 11:43:07
|
| The reason why its taking so long is because you're bringing the whole data onto report server and then asking it to group for you in different levels. By moving the grouping at sql end you reduce amount data retrieved a lot and then only display it.Didnt understand the below. can you explain?We cannot move all the groups to sql side as it is required for the report outputonce you bring data grouped upto various levels directly from db how do you lose any data?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
ambi6088
Starting Member
4 Posts |
Posted - 2011-07-26 : 04:22:03
|
Hi,Based on grouping we display the heirachy in the report,so in some cases we can't move the grouping to sql. Yes, we will not loose the data but to present data in specific format can be done with grouping.Regards,Ambikaquote:
Originally posted by visakh16
The reason why its taking so long is because you're bringing the whole data onto report server and then asking it to group for you in different levels. By moving the grouping at sql end you reduce amount data retrieved a lot and then only display it.Didnt understand the below. can you explain?We cannot move all the groups to sql side as it is required for the report outputonce you bring data grouped upto various levels directly from db how do you lose any data?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-07-26 : 13:12:08
|
| you can still present data in desired format i think. you need to just add a set of filters based on your level in various rows of the report.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
SSRS Issue