SSRS: Report Generation for Multiple Client Databases Made Simple!

By Ghulam Nabi Shah | Published on August 20th, 2015 | Last updated on June 25th, 2019 |

Make Reports on the Go!

SQL Server Reporting Services (SSRS) provides a fully featured application for the generation of server-based reports. SSRS allows users to create basic reports comprised of tables, graphs, charts, maps and other complex visualizations in a simplified manner. Data for reports may be fetched from SQL Server databases, relational databases such as Oracle, or multi-dimensional and XML based data sources such as Teradata. SSRS provides the ability to create and manage a wide variety of reports in a range of formats. This is made possible with help of its Report Definition Language (RDL) component which contains data retrieval and layout information for a report.
Users interact with Report Server to view, subscribe to reports as well as management of data sources and security settings. Report Server is comprised of report definitions, history, configuration, and security aspects of its deployed reports. Let’s discuss the SSRS report generation workarounds for dealing with a number of different databases from clients.

Do you find it hard to meet timeframes when creating SSRS (SQL Server Reporting Services) reports for multiple client databases? It’s mainly due to the duplicate processes involved in creating SSRS reports. Also, this process will increase the time to run based on the increase in a number of client databases. The challenging part in SSRS report generation is to create one-time development for SSRS report with One database which communicates with ‘N’ number of client databases at runtime.

An easier solution uses “Embedded Data Source” logic to achieve the same with no need to tinker around too much with multiple client databases. A report data source allows users to specify the name and connection information. The main two types of data sources supported by Reporting Services are: “embedded” and “shared.” With a shared data source, you can use the same connection for multiple reports, whereas with Embedded Data Source, we can create individual connections for individual reports.

The solution based on Embedded Data Source logic requires passing the database credentials to the SSRS service at runtime. This allows Report Services to render data from dynamically from the database. This can be achieved by leveraging the power of the Embedded Data Source logic available in SSRS. With Embedded Data Source logic, only one set of reports needs to be created for a client database, which can be dynamically pointed to ‘N’ number of client databases.

Let us know your thoughts on having to connect to multiple data sources for reporting purposes.

Contact Macrosoft for more information in dealing with multiple database formats and application migration services.

Ghulam Nabi Shah

Chief Executive Officer at Macrosoft Inc

Shah is a forward thinking, institutional leader with eighteen years of experience. During his tenure as CEO, Shah has delivered top notch customer solutions in large scale and enterprise environments. His proven abilities as a technology visionary and driver of strategic business systems development allow Macrosoft to deliver best in class software solutions. Shah is the catalyst that brings IT to organizations, enables innovation, and expands the delivery of global capabilities. He also fuels business growth, and optimizes delivery while managing risks and costs. Shah currently holds a compiler patent with the US Patent Office.

Shah holds an MBA (Computer Science), in addition to 20+ professional and technical certifications. While he is proficient in a variety of development languages, his preferred language is Python. His areas of expertise include enterprise-wide architecture, application migration, IT transformation, mobile, and offshore development management. Shah’s ultimate goal, at Macrosoft, is to create a larger offering of product-based services while adopting new technologies.

In that rare instance when he has time for leisure, Shah is an avid cricket and football fan and a weekly racquetball player.