SSRS

Bruce Boling
Bruce Boling
Sixth Anniversary Name Dropper Facilitator 1 June 2023 Monthly Challenge
in Blackbaud CRM™ General Discussions

When selecting lots of columns and records from CRM with SSRS like 100,000+ rows to return to SSRS screen then save all 100,000+ in excel takes forever. Is there a technique that can speed with up. Thanks.

Comments

  • Bruce Boling
    Bruce Boling
    Sixth Anniversary Name Dropper Facilitator 1 June 2023 Monthly Challenge

    @Bruce Boling
    I should have also said, the sql procedure runs in about a minute, but you are waiting forever for the 100,000+ rows to display on the SSRS report screen.

  • Crystal Bruce
    Crystal Bruce admin
    Tenth Anniversary Kudos 5 Commented in Discussion First Reply

    @Bruce Boling Hi Bruce! I think you meant to post this in the CRM community, so I am going to move it over there. Thanks!

  • Abel Debela
    Abel Debela
    Tenth Anniversary Name Dropper Participant Facilitator 1

    @Bruce Boling I have that same experience. The resolution I made was to put a disclosure on the top of the report that the output is pulling the top XXX,XXX number of records based on input parameters. The database engine responds to the result from the procedure in a reasonable fashion however, it will take time for SSRS to render the values to the CRM. I have seen timeout errors happening between the time that BB-CRM waits to SSRS render the output. Depending on the app server /environment you are hosted, the response time might vary. In my case, I ended up putting a limit on the rows returned from the procedure side and disclose that on the report when the procedure hits the maximum number of rows returned.

  • Bruce Boling
    Bruce Boling
    Sixth Anniversary Name Dropper Facilitator 1 June 2023 Monthly Challenge

    @Abel Debela
    Specifying a limit sounds good, but my user wants all 250,000 rows in an exel file. If SSRS pulls a set limit of 100,000 how do I know who the other 150,000 are to pull.

  • Rick Root
    Rick Root
    Tenth Anniversary Kudos 2 Name Dropper Participant

    @Bruce Boling IMO, SSRS isn't really meant for that. Neither is BBCRM. Not sure anyone has ever really asked us for a report with a quarter million rows, though certainly some of our marketing efforts output more than 100k rows.

    250,000 rows isn't a report, it's a data extract :)

    If they want it regularly, maybe build an SSIS package to generate the file - guarantee that'd be faster, though you'd have to run it for them… not sure if you can even do that in a hosted environment though.

Categories