none
Querying multiple servers within one report

    Question

  • I have a report requirement (SSRS 2012) that produces a summary of the same information gathered from different source systems. These have individual connection strings and include several third party products.

    At this time, the information is being manually input into an Excel worksheet that shows a summary row for each account, with all of the disparate systems on one worksheet.

    I've already explained to the user group that I cannot incorporate the different sources into one worksheet. The question I need to answer is whether or not I could create separate datasets and place different tablixes (same layout, different datasource) after one another with a page break and then export to Excel. The end goal would be to display each source system on a worksheet within a common workbook. Is this a possibility?

    If I can overcome this hurdle the only issue is getting the user to understand that 38 columns wide is not going to translate well into one printed page (per his request). For that, I think I'll let a visual do the work of explaining the problem and let him print out the too-wide report and try to read it without a magnifying glass. :-)

    Friday, September 20, 2013 9:05 PM

Answers

  • Yes, in SSRS you can have multiple tablix' each with a separate data source.  Exporting to Excel would put each on a separate worksheet if you put the page breaks in the SSRS report after each tablix.  If you eliminated the page breaks you should be able to keep the different sources on one page as the users have asked. 

    Another option, which might be useful given that data is stored in multiple disparate systems, is to create a simple data warehouse and keep it periodically refreshed with SSIS packages. You can then do your reporting against the data warehouse rather than have the reporting make all the connections.  This would speed up your reporting considerably, and make everything simpler.

    Cheers,

    Martina


    Martina White

    • Marked as answer by GaryT_Musicman Monday, September 23, 2013 2:11 PM
    Sunday, September 22, 2013 11:25 PM

All replies

  • Yes, in SSRS you can have multiple tablix' each with a separate data source.  Exporting to Excel would put each on a separate worksheet if you put the page breaks in the SSRS report after each tablix.  If you eliminated the page breaks you should be able to keep the different sources on one page as the users have asked. 

    Another option, which might be useful given that data is stored in multiple disparate systems, is to create a simple data warehouse and keep it periodically refreshed with SSIS packages. You can then do your reporting against the data warehouse rather than have the reporting make all the connections.  This would speed up your reporting considerably, and make everything simpler.

    Cheers,

    Martina


    Martina White

    • Marked as answer by GaryT_Musicman Monday, September 23, 2013 2:11 PM
    Sunday, September 22, 2013 11:25 PM
  • Thanks. I suggested the warehouse initially and was rejected for now. There is an initiative pending for an enterprise solution, but this report needs to be completed well  before a solution is in place.

    I was hoping that the multiple data source issue was a n easy fix, and it sounds like my suggestion of separate tabs for each system would be the best solution here. I'm concerned that if I leave it all on one page, the user will want a specific order within the tablix that intermingles the data.

    Thanks again!

    Monday, September 23, 2013 2:11 PM