Browsed by
Category: Exact Synergy Enterprise

Formatting your URL with data from Synergy Enterprise in an SSRS report

Formatting your URL with data from Synergy Enterprise in an SSRS report

Have you already heard that the Microsoft SQL Reporting Services has been integrated in Exact Synergy Enterprise 243, that has been released recently? If not, have a look here on the Exact Product Blog.
If you’ve been creating reports in SSRS to integrate in Exact Synergy already, you must have worked with hyperlinks that lead back to an internal Synergy page. For example, you have a report with a list of customers, and the name of the customer in the SSRS table contains an hyperlink to the account card in Synergy. How do you include the hyperlink in the table cell that it performs the right action?
There are several options that you can use. For the report developer it’s basically a choice, but looking at database performance issues, there is a preferrable way.
Option 1: Include your link in the query
At the query design, you can create a concatenated string to retrieve the full URL to the account card. Your query could look like this:
SELECT cmp_wwn AS AccountGuid,
cmp_code AS AccountCode
/CRMAccountCard.aspx?id={‘ + CONVERT(nvarchar(36), cmp_wwn) + ‘}’ AS AccountLink
In this case, at the “Action” tab in your report design, you would only have to add this to the URL navigation:
=”Fields!AccountLink.Value”
Option 2: Construct the hyperlink in the report
This option leaves the query clean and you will take care of the hyperlink in the report design. Your query would look like this:
SELECT cmp_wwn AS AccountGuid,
cmp_code AS AccountCode
CONVERT(nvarchar(36), cmp_wwn) AS AccountLink
The URL navigation in the “Action” tab would then look like this:
=”/CRMAccountCard.aspx?id={” & Fields!AccountLink.Value & “}”
Option 3: Keep the query as clean as possible
Database developers would be in favor of the following construction because it takes the least database performance. The query is kept as clean as possible:
SELECT cmp_wwn AS AccountGuid,
cmp_code AS AccountCode
As you can see, there is no trimming and converting necessary here. In the URL navigation on
the “Action” tab, the value has to be this:
=”/CRMAccountCard.aspx?id={” & Fields!AccountGuid.Value.ToString & “}”
Notice the “ToString” at the end of the field. In this case this is a mandatory addition, since the AccountGuid field is of type uniqueidentifier. This will not result in an error message when you run the report, but your hyperlink will not work.
So which option to choose? Most of the times it’s a choice of the report developer whether to keep the query clean, or prepare the data in the query already so you’ll only have to add the field values in the different options. The third option is my personal favor, since that one is a lot easier when your work with data sources from an SSAS cube.