Welcome to the Exact Product Blog

On this blog we will cover stories from all around the Exact ecosystem.

Steps to make your reports more dynamic

Written by Michiel Dorjee on . Posted in Exact Globe, Exact Synergy

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.

Division card - 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”).

DDS Report parameters

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).

Embedded Dynamic Data Source

The credentials for the embedded data source should be set to “Windows integrated security”.

Credentials Dynamic Data Source

The visibility of the “ConnectionString” Parameter should be set to “Hidden” so that the user can’t change it manually.

Hidden property for ConnectionString parameter

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”.

Default value for ConnectionString parameter

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.

DDS Report

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.

PS:
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.

 

Tags: , , , , , ,

Comments (5)

  • Joshua Eddy

    |

    Is this only intended for Globe backoffice? I’m trying to use it for Macola ES, but I’m getting stuck near the end.
    I’ve left the parameter for Division and ConnectionString empty in the SSRS designer. Then in the SSRS Management in Synergy Enterprise, I defaulted the parameter to “Data Source=SRV3;Initial Catalog=001”. (I’d like it to pull from the division without me having to specify a default for each company, but I couldn’t even come close to getting that to work).

    I get this error – but your instructions stated that I didn’t need to set up another datasource?

    The back office connection needs to be set up for division Data Source=SRV3;Initial Catalog=001.
    Go to System / Reports / Log / Errors for more details.
    Exception of type ‘Exact.SSRS.Reporting.Business.DivisionNotConfiguredException’ was thrown.

    Reply

  • Naveen

    |

    Will this method be applicable for a person who is trying to view reports on ESE by pulling data from JobBOSS?.

    Reply

    • Sunil Girdhari

      |

      Hi Naveen, yes it is.

      Reply

  • John Straver

    |

    How can I fix this with a subreport?

    Reply

Leave a comment