By Ghulam Nabi Shah | August 20th, 2015 |
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.
