Wouldn’t it be great to make your reports more dynamic? An “outstanding items” report which retrieves and shows you this information based on the selected company. Selecting another company triggers the report to dynamically retrieve this information from a different data source and show it to you. How great is that?! This all can be achieved with a report which uses a Dynamic Data Source (DDS). In this blog post I will explain how SQL Server Reporting Services (SSRS) reports with DDS are supported in Exact Synergy Enterprise via the SSRS Integration.
All the Exact Globe Next Dashboards (SSRS reports) are making use of the DDS functionality. To view these Dashboards you don’t necessarily need Exact Globe Next ‘with extended functionality’. Exact Synergy Enterprise with Exact Globe Next is sufficient to enjoy the dynamic power of these Dashboards. You can also view them directly on the Report Server when you only have Exact Globe Next and SSRS installed.
In Exact Synergy Enterprise you can maintain divisions. Each division represents a company. The back office data, e.g. financial transactions, for the company is stored in a database on a server. On the division card you can maintain these values. They are stored in the fields “Database: IP Address (Server)” and “Database: Name” in the “Back office” section.
Now that we know where the “Back office” is located for each division (company) we can use it in our reports. The report requires two fixed named parameters (“Division” and “ConnectionString”).
It also requires an embedded data source which uses the value of the “ConnectionString” parameter to connect to the correct data source (Back office: company).
The credentials for the embedded data source should be set to “Windows integrated security”.
The visibility of the “ConnectionString” Parameter should be set to “Hidden” so that the user can’t change it manually.
The default value of the “ConnectionString” Parameter is the same value as of the “Division” parameter. This is required in order to trigger the refresh data for the possible other report parameters. E.g. the default financial “Year” and “Period”.
In Exact Synergy Enterprise you can select a Division when you are viewing one of the Exact Globe Next Dashboards. The selected Company will be used when you are using Exact Globe Next ‘with extended functionality’ to view them.
The SSRS Integration will not send the value of the “Division” parameter to the Report Server for the Exact Globe Next Dashboards, but it will send the concatenated value. E.g. “Data Source=nl000dem01;Initial Catalog=MacBeanGLB”. The same value will be copied by the Report Server to the “ConnectionString” parameter so that a successful data source connection can be made to the selected Division/Company and data for the report and possible other report parameters can be retrieved.
When you want to view one of the Exact Globe Next Dashboards directly on the Report Server, you just need to provide the concatenated value for the “ConnectionString” parameter via the “Division” parameter.
Of course the Exact Globe Next Dashboards can be adjusted so that user can enter the same value as in Exact Synergy Enterprise for the “Division” parameter instead of the required concatenated value for the “ConnectionString” parameter.
With the above steps you can create your own dynamic SSRS reports.
Now you can view the same report and select from which company you want to see financial information e.g. outstanding items: that’s a powerful and dynamic feature!
Enjoy and don’t hesitate to leave a reply.
Remember that any SSRS report with the above requirements will be recognized by the SSRS Integration in Exact Synergy Enterprise as a report with DDS. Thus there won’t be a data source for you to configure in the tab ‘Data source’ on maintenance page for a SSRS report with DDS. Because there are no credentials stored for the DDS it will use the credentials stored in the SSRS Integration settings. Thus the user configured in the SSRS Integration setting should be able to read the data in the “Back office”; this user should have at least the db_reader role. Similar as for the user (credentials) which you use when creating a new data source. Otherwise the SSRS report with DDS can not retrieve the required data.