Improving performance of cascading parameters

  • Hi SSRS Gurus,

    I have reports that require cascading parameters.

    I have an ASP.net app that exposes data via reporting services 2008 over the internet to our customers. Customers are authenticated to the application via a security database that allows customers to see only their data (customer A can't see customer B's data even though it is all in the same database).

    Within the database each customer has a different customer hierarchy. Customer A might have a hierarchy of Corporate, Region, Division, Site. Customer B might have a hierarchy of Headquarters, State, Site Group. This is the reason for the requirement of cascading parameters.

    If customer A logs in they might need to see their region. From the parameters they would choose Region, and select whatever regions they want to see. From that, the associated Divisions display and the choose which ones they want to see. From that they choose the Sites, they want in the report.

    At any rate, the execution time between choosing one parameter to the next takes over 10 seconds plus before the next parameter is available to select. Way too long.

    I've searched the internet and haven't found any satisfactory responses.

    Does anyone have any idea how I can address the speed issue regarding cascading parameters? Has anyone else had issues with cascading parameters taking a long time to populate, and if so, if you were able to fix it, please share.

    Thanks in advance.

  • Hi,

    Did u check the performance of your query how much time its taking?

    And My suggestion is use report parameters and remove sql parameters(Except mandatory parameters)

    Thanks,

    Veeren.

    Thanks & Regards,
    Veeren.
    Ignore this if you feel i am Wrong. 😉

  • veeren4urs (10/7/2010)


    Hi,

    Did u check the performance of your query how much time its taking?

    And My suggestion is use report parameters and remove sql parameters(Except mandatory parameters)

    Thanks,

    Veeren.

    Hi Veeren,

    The queries do not take long to run. I would give the times, but unfortunately I woke up with a cold this morning and am not feeling well. Maybe I will feel better later, and I will post the times. These are very simplistic queries.

    The first is hidden and based on the user logging in and which company they are associated with. So, it's Select customerID, Customer From Customer where CustomerID =@CustomerID. Based on the logged in user a set of parameters is available that displays the hierarchy for that customer. All, parameters are multi-select. So the next level is Site Group Type, it might be Profit Center, Region, State Or Province (it depends on the customer). Most users select all because they want to download the data to excel and pivot etc. And, the parameters cascade from there. They are not complex queries at all.

    Here is a rant: Hello Excel users! If you want a report that does a pivot ASK for it!! I can write it for you so you don't have to pivot the data every time you do a download. If you can write it in Excel, I can write it in a report so you don't have to pivot. Sorry Excel users, Excel has it's place, but, Reporting Services is not meant to be a data dump to Excel, ask for what you need. Problem is the end users don't know how to ask for what they want. And, even when they do, they are addicted to pivoting in Excel. End Rant. Sorry, I told you I didn't feel well.

    However, Veeren, I do have a question for you. What is the difference between a report parameter and a SQL parameter? If I understand, I think in my case I have to use SQL parameters based on the fact that I have different customers logging in and they have different profiles.

    Thank you in advance.

  • When you run the report on the server's Report Manager, how long does it take for the drop-downs to populate? If the time is significantly less than what it takes on your web app, you can try to trim extra overhead from your web app and traffic from the web server to the SQL server (assuming they're on different boxes).

  • Doug Lane (10/7/2010)


    When you run the report on the server's Report Manager, how long does it take for the drop-downs to populate? If the time is significantly less than what it takes on your web app, you can try to trim extra overhead from your web app and traffic from the web server to the SQL server (assuming they're on different boxes).

    Great point that I forgot to address. The reports run much faster through report manager. The web app and the report server are on the same box. But, the sql server is on a different box. It has to be because it is behind the firewall.

    In fact, when I run the reports on my local machine, the cascading parameters are instantaneous.

  • I'm not sure, but I think each cascading parameter causes a post-back when it loads. If that's the case, you might want to try trimming down the page the report viewer is presented on so those page calls aren't wasting time loading fluff.

    Here's an experiment: try putting the reportviewer control in a popup window by itself with no CSS or any other HTML (unless it's absolutely necessary) and see how much faster it is.

  • Doug Lane (10/7/2010)


    I'm not sure, but I think each cascading parameter causes a post-back when it loads. If that's the case, you might want to try trimming down the page the report viewer is presented on so those page calls aren't wasting time loading fluff.

    I thought about the post-back. Can you tell me how to trim down the page in the report viewer. I'll look online, but, if you know, it sure would be helpful.

    Thanks in advance.

  • @Doug Lane:

    From what I have read so far, post back only effects the rendering of the report, not the cascading parameters. Am I missing something?

  • I have opened a Microsoft case to see what they have to say about it.

    Hey people - beat Microsoft!!!

    Sorry for invoking the comptetive nature in anyone, however, I need an answer.

    Bonus: You can add it to your resume!!! 😀

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

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