question

db042190-2983 avatar image
0 Votes"
db042190-2983 asked ·

ssrs parameter caching and refresh

Hi we run 2014 developer in one of our qa environments.

I was talking to a peer recently about a report she is testing. It has a large number of parameters relative to other reports in our system, 15 in all.

each of the first six params are enterable by the user representing different high and low amounts, years, number of days and number of times billed. only the last of those 6 has a default of billed 2 times. The first 5 are nullable.

the 7th param has only 2 choices in its drop down, is required, allows only one selected value, and both choices are physically listed in the parameter's available values.

the 8th parameter only lists 3 items currently but they come from a query that traverses millions of records to arrive at those values and 1 or more choices are required.

the 9th parameter is a multi select from 3 items physically listed in the parameter's available list.

the 10th parameter's available list is dependent on what was chosen for params 8 and 9. At most it lists 50 choices, it is a multi select and is required.

the 11th parameter is a multi select location that is sourced by a query and lists over 300 choices. it is required.

the remaining 4 parameters are 2 dates, sort order (just 2 choices) and account encoding (just 3 choices). none of these uses a query to populate available values. all of these are required and have a default. .

my peer is saying that when she changes parameter choices (even before param 10), she sees an unnatural or unexpected delay before she can even pick the next parameter.

we are wondering what could explain this and if based on the profile i've given if the order of params was to be changed if that might improve this experience she is describing?



sql-server-reporting-services
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Joyzhao-MSFT avatar image
0 Votes"
Joyzhao-MSFT answered ·

Hi @db042190-2983 ,

Did you get any error or warng from the website or SSRS log ? Did you notice the delay from SSDT preview or SSRS web portal?

How long does the delay exist? Did you notice the delay for same duration from differant machine, it could be caused by the browser engine and UI performance .
Frankly it is hard for us to diagnose or optimise the report that has this kind of delay issue with forum support. We could not audit the server here, when there is no clear error or warning messege, it is hard to locate the real issue.

If this issue really bothers you, I would suggest you to open ticket with Microsoft Support : https://support.microsoft.com/en-us/assistedsupportproducts They could support better with testing and auditing on the real server for troubleshoot this.

Regards,
Joy


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


· 2 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thx Joy. thats appreciated and we dont know of any warnings etc but we are asking if there is some not so well known behaviors with ssrs parameters where order might cause this kind of behavior. from what you wrote it sounds like no.

0 Votes 0 ·

one of the things my peer wonders is if it might improve if optional params pushed to bottom

0 Votes 0 ·