ssrs parameter caching and refresh

db042190 1,516 Reputation points
2021-03-03T12:27:10.153+00:00

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
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,793 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Joyzhao-MSFT 15,566 Reputation points
    2021-03-04T08:04:21.123+00:00

    Hi @db042190 ,

    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.