Welcome to the Exact Product Blog

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

How to use drill through SSRS reports in Exact Synergy Enterprise release 243

Written by Michiel Dorjee on . Posted in Exact Synergy

One great feature of SQL Server Reporting Services (SSRS) is the ability to drill through to secondary reports. The advantage is that you can show in one report the summarized high-level information and in the second report the detailed information. This can be realized by adding a drill through action in a report. When designing such an action you would normally select the option “Jump to report“, provide the secondary report and if applicable the required values for the report parameters.

In the blog post What’s new on the menu for Reporting Services Integration in Synergy? I communicated that drill through reports will be supported in product update 244, which is currently in controlled release. How you can use the drill through functionality in Synergy Enterprise release 243, which is currently available, will be explained in this quite technical blog post.

Problem

When these reports are being uploaded and viewed in Synergy Enterprise, the click action on the first report will directly navigate to the location (URL) of the second report on the Report Server. This may result in an access denied because you don’t have access rights on the Report Server.

Solution
Just by selecting the option “Jump to URL” for the drill through action in the first report and providing the URL for the second report, which is stored Synergy Enterprise, you can solve the issue. The click action will navigate you to the “Employees” report page in Synergy Enterprise instead of the report on the Report Server.

Example
I have created a “FTE” report (summary) and an “Employees” (details) report for this example. The “FTE” report shows a pie chart with the FTE ratio and a KPI with the average FTE. When the user clicks on a piece of the pie chart it will pass the FTE value to the report “Employees” and this report shows all employees which comply with the FTE value.

When I upload the “Employees” report in Synergy Enterprise and view this report, the URL will be in my case:
http://localhost/Synergy/docs/SSRSRepBrowserParameters.aspx?ID={5171e857-4d81-45aa-8006-ade619f2d7d9}.

Now that I know the URL for the “Employees” report in Synergy Enterprise I can design the “FTE” report to use the drill through action “Jump to URL” and provide the value for the FTE parameter.

I need concatenate the URL with a fixed string (the URL of the “Employees” report) and the value for FTE parameter value via an expression. The expression in my example will be:
=”http://localhost/Synergy/docs/SSRSRepBrowserParameters.aspx?ID={5171e857-4d81-45aa-8006-ade619f2d7d9}&FTE=” & Fields!fte.Value

Now when I upload the “FTE” report and click on a piece of the pie chart, I will drill through the “Employees” report and stay in my Synergy Enterprise environment.

Product update 244
You will be able to configure the option “Jump to report” in the next product update functionally via Synergy Enterprise.

Also in product update 244 you have the possibility of linking the “Synergy URL” value, e.g. http://localhost/Synergy, to a report parameter. With this functionality you can create reports which navigate to web pages in Synergy Enterprise. E.g. navigate from the “Employees” report to the personal card. This way reports can be easily exchanged between different Synergy Enterprise environments without changing the Synergy URL manually. The benefit is that you can create your reports so that they can be used by any customer who is using Synergy Enterprise.

Via this blog we’ll keep you updated about the possibilities and improvements for Reporting Services Integration in the upcoming product updates of Synergy Enterprise. Off course we highly appreciate your comments and thoughts.

Photo credit: Search Engine Land

Tags: , , , , , ,

Comments (7)

  • chantal

    |

    Hi Sunil, this is a very useful tip! Looking forward having the new menu of PU 244 Keep up the good work!

    Reply

  • Richard Benoit

    |

    This is a powerful and useful tip indeed. What makes it more interesting is when the user clicks on a piece of the pie chart that it will pass not only the FTE value, but also the employees who correspond to the size of that piece of the chart.

    Sunil, do you have any experience with this?

    Reply

    • Sunil Girdhari

      |

      @Richard, if I understand your question correct you want pass multiple (default) values to a report parameter, right? I don’t have any experience with that. I found a useful article on the web using the next search criteria “SSRS pass multiple values go to report action”, it’s SSRS 2008: Passing Multiple Values from Drill Down Field in Matrix Report to Linked Report (Drill Through). Hopefully this is what you are looking for. If not just leave another reply with some more background information what you are trying to solve and perhaps I have a solution or a good alternative.

      Reply

  • Laurence Ledford

    |

    great tips Sunil… i do have a question… i didn’t notice anywhere to specify if a parameter can accept NULL. Did I miss it somewhere?

    Reply

    • Sunil Girdhari

      |

      Thanks Laurence, first I need to apologize that I could respond earlier as I was having a short holiday on an Island :-) I suppose you mean that you can’t specify in ESE whether a parameter can accept NULL values, right? This is correct, because this needs to be done in VS or Report Builder. Please have a look at this page to read more articles related to this topic. In order to use it with the controls in ESE you need to specify the parameter to allow empty values as well. In PU 245 there are some reports (e.g. the Actuals vs Budget report) which make usage of this functionality. Please have a look at them. Hope this helps. Cheers!

      Reply

  • Mahesh K

    |

    Hi Sunil
    it will be a great help if you send one or two sample on Drill Through SSRS report to us. we are looking functionality which is like A drillthrough report is a report that a user opens by clicking a link or cell within report. I mean we need relationship of general to the detailed in same report.

    Thanks in advance
    Mahesh K

    Reply

    • Sunil Girdhari

      |

      Hi Mahesh, you can have a look at e.g. the EGN Cash Dashboard which has several drill through reports. But also the logistic price lists report contains drill through reports. Cheers!

      Reply

Leave a comment