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.
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)
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.
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.