November 27, 2009 at 4:27 am
Dear all, I have a report with many cascade parameters, meaning, that when I select first parameter value from the list, the second parameter list is being generated according to the value selected in the first parameter, the third parameter list depends on the value, selected in the second parameter and so on…
The thing is, when I’m running this report in Visual Studio everything works fine. When I publish the report to reporting services it starts causing performance issues in the sense, that, after selecting the first parameter the web page is being refresh, and have to wait 40 seconds, until I get the response and can select the second parameter, etc.
How can it be and how it’s possible to solve such kind of issues?
Any ideas are much appreciated.
Thanks.
November 27, 2009 at 4:54 am
How many data sets are you using in the report and how are you generating the dynamic parameters
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
November 27, 2009 at 6:27 am
HI,
I have around 15 datasets(Most of them are populating parameters).
Regarding the parameters: say for example, I have 3 parameters on my report :
Business unit, Sub Business Unit and Cost Center.
I also have the table, which stores this hierarchy,
say:
table1:
BU SBU CC
--------------
BU1 SBU1 CC1
BU1 SBU1 CC2
BU1 SBU1 CC3
BU1 SBU2 CC4
BU2 SBU3 CC5
....
In the first parameter I just give the list of BUs, by doing a distinct select from this table.
The first parameter name is @BU and it's being populated from the dataset:
Select distinct BU from 'table1'
In the second parameter, as it depends on the first selection, I do something like:
select distinct SBU from 'table1' where BU in (@BU), so I'm getting the list all SubBusinessUnits, relevant to those BusinessUnits, selected in the first parameter.
The same for the third parameter and so on.
So, the problem is, when I run the report from reporting services, Business Unit parameter is filled with data by default, the rest parameters are grayed out(as they depend on the one selected before). Once I select business unit, the query populating the second parameter supposed to be executed, but I have to wait for a long time, before reporting services process this stuff.
Actually, the refresh of the web page is taking a really long time.
As I said that's not the case, when I run the report from the development environment(in Visual studio). In that case, the refresh of the parameter is instant.
So, that's the problem.
By the way, reporting services (I have performance issues with) are located closer to the database, than Visual Studio, where I don't have performance issues.
Can it be the case, that it's not enough of memory for reporting services, so the refresh time is so slow, or something like this?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply