ODBC Data Sources and SQL Server Reporting Service (SSRS)

Documentation
Premium Feature — Available in the Professional Plus and Enterprise Editions. Learn more or contact LabKey.

This topic explains how to set up SSRS to create reports and perform analyses on data stored in LabKey Server. It assumes that you have previously set an ODBC Connection to provide data to SSRS. For details see External ODBC Connections.

SSRS Setup

PostgreSQL ODBC driver is used to connect to LabKey regardless of the underlying database used by the LabKey server.

  • Download both the 32 bit and 64 bit PostgreSQL ODBC drivers from https://www.postgresql.org/ftp/odbc/versions/
  • Under the msi folder, download zip files for both the 32 and 64 bit drivers.
  • Unzip and double-click on .msi to install (the same if you want to uninstall).
  • You will likely want to set up a LabKey account with the minimum amount of permissions required for your reports.
  • From FireWall settings, set Inbound And Outbound rules for Port 5435 (or the port you set in later steps for your odbc interface).
  • For Inbound rule, click on New Rule.
  • Select Port → Next.
  • Select TCP → Select Specific local ports: 5435
  • Click Next.
  • Select ‘Allow the connection’.
  • Check the appropriate network profile - either Domain and/or Private and/or Public. Click Next.
  • Provide a meaningful name and click Finish.
  • For outbound rule, click on New Rule, and follow steps same as Inbound Rule setup above.
  • You may need to restart your computer for the firewall rules to take effect.
  • Since Visual Studio and Report Builder are 32 bit and recent versions of SSRS and MS SQL Server are 64 bit, you will likely have to set up both the 32 and 64 bit postgres ODBC data sources. ODBC data source setup steps:

32 bit ODBC Data Source

  • Go to the 32 bit ODBC Data Source Administrator
  • You’ll want to create a System DSN, so click that tab
  • Select Add then select the 32 bit postgres driver (ANSI or Unicode), and the following window will appear:

An example of the ODBC driver setup:

  • Data Source: the name of this data source, could be anything you prefer.
  • Database: your LabKey folder or container, ex. /Home or any folder of your LabKey data source since each LabKey folder is setup as its own database
  • Server: Your server host name, for example, "www.labkey.org". "localhost" is for local setup/dev mode.
  • User Name: your LabKey Server user name.
  • Password: your LabKey Server password.
  • Port: 5435 is the default Postgres ODBC port. This value needs to match whatever is set in the Admin Console of your LabKey Server.
  • Go to Admin → Site → Admin Console → Admin Console Links.
  • Under Premium Features, click External Analytics Connections.
  • Clicking on Test should give you Connection Successful if all the setup is correct.
  • Save.
  • Click OK to close ODBC Data Source Administration (32-bit) window.

64 bit ODBC Data Source

  • Go to the 64 bit ODBC Data Source Administrator
  • Go through same steps as 32 bit ODBC data source above.
    • Important: You will want to name the 32 bit and 64 bit ODBC data sources the exact same name.

SSRS Reporting Services setup

  • Go to Report Server Configuration Manager, select Server Name and Report Server Instance: SSRS → Connect
  • In the Reporting Services Configuration Manager, ensure your screens look similar to below:
Report Server Status:

  • b) Service Account:
  • c) WebService URL & Web Portal URL (should be clickable):
  • d) Database (Change Database or Change Credentials to set these values for the first time):
  • e) You may need to setup an Execution Account.
  • The Account will request a format like <Domain><Username>, there is no domain on the LabKey Server so leave that part blank.
  • Use the same account as your ODBC data sources (i.e your LabKey user credentials)

Visual Studio

  • In Visual Studio create a Report Server Project
  • To set up the data source, add a new Shared Data Source
  • Set type to ODBC
  • The Connection string just needs to show which DSN to use. This should be the name that you used for both your 32 bit and 64 bit data sources.
  • Click Credentials and select Do not use credentials
  • Right click on Project → Properties → set properties as shown below:
  • You should now be able to use this datasource to create datasets and reports. Your report -should work in preview mode and when deployed to the report server.

Notes

  • The Query Designer is an optional UI to aid in the creation of report Datasets. Since it is generating and parsing SQL queries using common SQL languages like Transact-SQL, some of the enhanced features of LabKey SQL cannot be generated or parsed using the Query Designer.
LabKey pivot queries must be manually entered and the Query Designer cannot be used on a LabKey pivot query.
  • The Table Query Designer (available when Dataset Query Type is Table) is not available when using an ODBC data source, instead the Text Query Designer will be shown. This is a limitation of the .Net ODBC controller.
  • Parameters in Queries. When wanting to use parameters in Dataset queries, ‘?’ should be used in your query to insert your parameter. Then in the Parameters section you can define the name of the parameters used in the report UI. The parameters will be listed in the order they appear in the query.

Related Topics

Discussion

Was this content helpful?

Log in or register an account to provide feedback


previousnext
 
expand all collapse all