Using SRS with SQL databases on EPMonDemand

Posted By Posted by: EPM Partners on April 29, 2013

EPMonDemand includes SQL Reporting Services 2012 allowing for creation and publishing of SSRS reports using the familiar tools such as Report Builder or BIDS / SQL Data Tools, however due to the need for direct encrypted SQL connections for Report Writing some additional requirements must be met before you can start building reports.

However once done, all licensed users will be able to view, download and print reports using just a web browser as expected.

Requirements

In summary you will need the following which can be requested from the EPMonDemand support team:

  1. SQL connections made to EPMonDemand use encrypted SQL connections via TCP port 1433. For your security we require that you provide us with your public Internet Address from your location, this can be obtained from your IT department.
    • Note: Many organizations restrict outbound connections and will block the connections to our SQL servers, please contact your IT department to ensure that you will be able to make these connections.
  2. Report Writers are granted access via a separate set of credentials which must be used to when building reports.
  3. As SQL Report Builder (and BIDS / Data Tools) do not support Microsoft Account authentication, certain workarounds must be taken.

Assuming the above is done, the remainder of this how-to describes the steps required to use SQL Report Builder to create SQL based reports and deploy them to SharePoint. These steps can be easily adapted for use with BIDS or SQL Data Tools.

Using Report Builder

Please review the common problems section at the end of this article if you have problems with any of the following steps.

  1. Log into PWA with an account that has permissions to the Business Intelligence Centre.
    EPMonDemand SQL Reports
  2. Click on the Reports tile on the PWA home page.
    EPMonDemand SQL Reports
  3. Click on the Reports link.
    EPMonDemand SQL Reports
  4. Activate the Documents ribbon by clicking on File tab.
    EPMonDemand SQL Reports
  5. Click on New Document and select Report Builder Report from the drop-down menu.
    EPMonDemand SQL Reports
  6. A pop-up information dialogue box will appear indicating the Report Builder application is launching.
  7. A security pop-up dialogue box appears requesting permission to run the application.
    Click on the Run button.
    EPMonDemand SQL Reports
  8. The first time the program attempts to launch, Report Builder is downloaded and installed on the local machine. This will not occur in subsequent attempts to launch Report Builder.EPMonDemand SQL Reports
    EPMonDemand SQL Reports
  9. Click on Blank Report.
  10. Right click Dataset and select Add Dataset…
    EPMonDemand SQL Reports

    The Dataset Properties dialogue box will be displayed.

  11. Choose Use a dataset embedded in my report and then click on the New… button in the Data source field.
    EPMonDemand SQL Reports
  12. Choose Use a Connection embedded in my report, select Microsoft SQL Azureas the connection type and finally click on the Build… button. EPMonDemand SQL Reports
  13. Fill in the Server Name and SQL Server Authentication details. Once those details are added, select the database name or just type it in the Select or enter database name field. Click on Test Connection to verify the settings are correct and then click OK. Use the server and user account details provided to you by the EPMonDemand Support team or your Administrator.
    EPMonDemand SQL Reports
    EPMonDemand SQL Reports
  14. Click on Credentials located in the left hand pane.
  15. Choose the Use this username and password option and enter the same SQL Server authentication credentials as entered in step 13. Click OK. EPMonDemand SQL Reports
    EPMonDemand SQL Reports
    EPMonDemand SQL Reports
  16. Click Query Designer… to build you query. Once complete, click on the OK button.
    SQL Reports
  17. Optional settings are available in the left hand side pane. Click OK when done.
  18. Click on the round Report Builder file button (or Ctrl + S) to save a local copy of the .rdl file on your computer.
    EPMonDemand SQL Reports
  19. To upload the document to the Reports library, follow steps 1 to 5 to navigate to the Reports library folder and then click on the + new document link. EPMonDemand SQL Reports
  20. Click on the Browse… button and located the .rdl report file saved locally to your computer previously. Click on OK.
    EPMonDemand SQL Reports
  21. The document will now appear in the Report library. Click on the report name to run the report.
    EPMonDemand SQL Reports


Common Problems

Cannot Run Report Builder

If you have problems running Report Builder in the steps above you can manually install it from the following Microsoft download site:

http://www.microsoft.com/en-au/download/details.aspx?id=29072

Cannot Edit Report in Report Builder

Due to the lack of support for Microsoft Account login the reports must be saved locally to disk then uploaded to SharePoint manually. As a result when you select Edit a Report in Report Builder from SharePoint Report Builder will open but it will not open the selected report.

In order to work-around this, save the report locally then open it from Report Builder from the File menu, then once you have completed your changes upload the local report (RDL) file to SharePoint.

Cannot Create SharePoint List Data Source

Once again due to the lack of support for Microsoft Accounts in Report Builder the use of SharePoint data connections requires additional steps to be undertaken when building reports (however they do display normally once built and published to SharePoint).

Please contact EPMonDemand Support for a summary of these requirements.


Blog Posted In Blog Posted In: Blog, How to, Reporting
Blog Posted In Comments Off on Using SRS with SQL databases on EPMonDemand