SSRS is good with parameters, various data sources, subscriptions, and linked reports. But highly responsive and interactive reports (especially on a mobile device) is not what SQL Server Reporting Services is good at. The online BI tool, Roambi, however is very good at interactive charts, etc, .. on mobile devices.
So, how to refresh Roambi reports with SSRS subscriptions?
Roambi has made this straightforward by providing two crucial items.
- A delivery extension for SQL Server Reporting Services that generates Excel files and calls …
- A Java program that connects to Roambi’s servers to upload the Excel files
The setup steps are enumerated in Roambi’s documentation (found here) but here are some things I found that you should watch out for.
- Java must be installed on the SSRS server.
- Roambi does not tell you exactly what you should enter into the Redirect URI field of their API Setup, nor do they tell you exactly what you should enter into the server URL field for setting up the Java program on the SSRS server. Here are the values I entered and they worked:
- Redirect URI = roambi-api\://client.roambi.com/authorize
- Roambi server URL = https\://api.roambi.com
- Getting these settings correct is critical. Fortunately, the Java program that does the actual interaction with the Roambi server is a tool that can be used independent of SSRS and regardless of any integration with SSRS. Use the tool by itself to test the settings above according to this documentation here: https://github.com/Roambi/roambi-script
- The extension config files are not located in the exact same path on the server as is mentioned in the documentation. You must (of course) find the files in the path for your own SSRS.
- So, in their case, they show: C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services
- But in my case, I used: C:\Program Files\Microsoft SQL Server\MSRS11.[my instance name]\Reporting Services
- Likewise, in the config files, make sure that the references to the roambiscript folder on your server are correct. I saw that the documentation is inconsistent in the name they gave to the roambiscript folder, using c:\roambiscript but then referencing c:\roambi-script in the config file (which would be wrong).
- The documentation asks you to enter a new code group to rssrvpolicy.config but does not say where exactly to put it. It is critical to place the code group xml in the exact right location in the config file. I tried it in many locations until it worked when I placed it right after the $CodeGen$ code group. Here’s what my rssrvpolicy.config file looked like:
</CodeGroup><CodeGroup class=”UnionCodeGroup” version=”1″
PermissionSetName=”FullTrust” Name=”Roambi Business Delivery Extension”
Description=”This code group grants Roambi Business Delivery Extension code full trust.”>
version=”1″ Url=”C:\Program Files\Microsoft SQL Server\MSRS11.[my instance name]\Reporting Services\ReportServer\bin\Roambi.SSRSDeliveryExtension.dll” />
- The DeliveryUI extension must be added to rsreportserver.config (their original documentation did not include this critical step). I figured out what to put for the class name in the type attribute by referencing the dll in a dummy C# web app and seeing what intellisence had to offer.
This is what my rsreportserver.config file looked like:
<Extension Name=”Report Server Email” Type=”Microsoft.ReportingServices.EmailDeliveryProvider.EmailDeliveryProviderControl,ReportingServicesEmailDeliveryProvider”>
<Extension Name=”Report Server FileShare” Type=”Microsoft.ReportingServices.FileShareDeliveryProvider.FileShareUIControl,ReportingServicesFileShareDeliveryProvider”/>
<Extension Name=”Roambi Business” Type=”Roambi.SSRSDeliveryExtension.RoambiDeliveryUIProvider, Roambi.SSRSDeliveryExtension” />
- Copying and pasting from Roambi’s pdf documentation may not work because the text that gets copied may have hidden pdf characters that break the configuration.
- If you intend for the subscription to upload an Excel file as a data source for a Roambi template in your MY DOCUMENTS folder in the Roambi file system, then when configuring your subscription, you must use the File ID of the template and not the template name.
- If you intend for the subscription to upload an Excel file as a data source for a Roambi template in a folder that you create in the Roambi file system, then before configuring your subscription, you must specifically grant publish permission to yourself on that created folder. You then can include the folder name when setting up your subscription along with the name of the template.
- When I first finished setting up my subscription in SSRS, it would fail and not write to the log file on the server. Upon manually deleting the log folder, it auto-created the log folder as well as the log file and began working.
Note: Roambi’s documentation may have been updated by the time you read this, but I hope this post was helpful to you.