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 up an ODBC Connection to LabKey Server and configured your Windows machine.

• Prerequisite: External ODBC and JDBC Connections
• Prerequisite: ODBC: Configure Windows Access
• To use SSRS, you must install both the 32-bit and 64-bit ODBC drivers on your Windows machine. Visual Studio and Report Builder are 32 bit and recent versions of SSRS and MS SQL Server are 64 bit. Important: give both the 32-bit and 64-bit ODBC data sources the same name.

Topics

• SSRS Reporting Services Setup
• Visual Studio
• Notes

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

• ODBC: External Tool Connections
• External ODBC and JDBC Connections
• ODBC: Configure Windows Access