locked
multiple result sets from stored procedure+3 pges in report RRS feed

  • Question

  • Hi,

    I have requirement that would be

    stored procedure  having 

    below queries like 

    select * from t1

    select * from t2

    select * from t3

    above result sets each having almost 100,000  records.

    I have to display 3 restult sets in 3 pages.

    when exporting to excel sheet should be display with 3 tabs.

    what is the best way to handle (without impacting performance.).

    First page or table should have all 100000 records.

    second page should have all 100000 records.

    Third page should have all 100000 records.

    Please suggest me best way to implement.(can i combine all result sets using union operator in stored procedure and fill the dataset in report )or( i have to use 3 diffrent data sets and 3 diffrent stored procedures) or (1 dataset and filter dataset based on the page).

    or plese let me know your suggesion.



    • Edited by prasad127 Sunday, November 18, 2018 6:02 AM
    Sunday, November 18, 2018 5:58 AM

Answers

  • Hi prasad127

    According to your description , if you do not want to impacting the performance .

    It is better to do divide the export to excel and render .

    For export to excel:

    For my side , I think , it is better to use 3 report ,each one use one dataset which from the (t1,t2,t3)

    Then ,export each report to excel , then merge 3 excel files into one file with 3 sheets.

    That way , it could divide a huge process into 3 small one , then each take less time .

    (while , if you just want use one report to render and also to export , you need to use the 3 sp with 3 datasets for report)

    For render:

    Form here , we know it would take a long time when filter the huge table in ssrs.

    So ,it is better to create 3 datasets (t1,t2,t3) with 3 sp, then put the 3 datasets in 3 tablix in report .

    If you want to get different sheet when you export report to excel , just set the page break for each Tablix.

    See:

    Export SQL Server Reporting Services Report Data into Multiple Excel Worksheets

    You could try the different types  and get the performance data in report server database ,ExecutionLogStorage table.

    Hope it can help you.

    Best Regards,

    Eric Liu


    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.

    • Proposed as answer by Mitarai Queen Monday, November 19, 2018 6:49 AM
    • Marked as answer by prasad127 Tuesday, November 20, 2018 1:50 AM
    Monday, November 19, 2018 2:11 AM

All replies

  • Hi prasad127

    According to your description , if you do not want to impacting the performance .

    It is better to do divide the export to excel and render .

    For export to excel:

    For my side , I think , it is better to use 3 report ,each one use one dataset which from the (t1,t2,t3)

    Then ,export each report to excel , then merge 3 excel files into one file with 3 sheets.

    That way , it could divide a huge process into 3 small one , then each take less time .

    (while , if you just want use one report to render and also to export , you need to use the 3 sp with 3 datasets for report)

    For render:

    Form here , we know it would take a long time when filter the huge table in ssrs.

    So ,it is better to create 3 datasets (t1,t2,t3) with 3 sp, then put the 3 datasets in 3 tablix in report .

    If you want to get different sheet when you export report to excel , just set the page break for each Tablix.

    See:

    Export SQL Server Reporting Services Report Data into Multiple Excel Worksheets

    You could try the different types  and get the performance data in report server database ,ExecutionLogStorage table.

    Hope it can help you.

    Best Regards,

    Eric Liu


    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.

    • Proposed as answer by Mitarai Queen Monday, November 19, 2018 6:49 AM
    • Marked as answer by prasad127 Tuesday, November 20, 2018 1:50 AM
    Monday, November 19, 2018 2:11 AM
  • Hi Eric Liu,

    Thanks for your reply.

    regards,

    Prasad.

    Monday, November 19, 2018 6:36 AM
  • Hi prasad127

    if you have resolve your problem.

    You could also mark the appreciated reply or share your solution as answer ,so that other members in this forum would benefited from it .

    Best Regards,

    Eric Liu


    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.


    Monday, November 19, 2018 6:42 AM
  • Hi Eric Liu,

    I have marked as answer.Thanks for your suggestion,its worked for me.

    I have made pagination by using grouping and enable page breaks. But when exporting to excel all 10 pages coming as 10 sheets in excel .

    Here my requirement is i can have 10 pages in ssrs report,but those 10 pages should display as one excel sheet when exporting to excel.

    Please suggest me.

    Regards,

    Prasad.

    Tuesday, November 20, 2018 1:55 AM
  • Hi Prasad

    Seems the ssrs report default regard one page in preview mode as one sheet when export to excel.

    It was by design , and seems could not change .

    If it possible (the data is small ), try to add an extra table with the same table structure and with no page break ,just one page.

    then ,add a parameter to control the two tables visibility .

    when export with sheets , show the table with page break , hide the table without page break.

    When export without sheets , show the table without page break ,hide the table with page break.

    BR,

    Eric Liu


    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.


    Tuesday, November 20, 2018 2:13 AM
  • Hi Eric LIu,

    I have large data set having 50,000 records.

    Sp giving results in 0.04 sec but for rendering its taking almost 1 minute.

    I have used paging concept, records per page 5000.All together report having 10 pages.even though i have applied paging for report ,its taking lot of time for rendering.

    whenever i am scrolling or maximizing  the report ,it got stuck for  40-50 seconds.

    Can you please suggest me how to solve this issue.

    Regards,

    Prasad.

    Tuesday, November 20, 2018 11:12 PM