Monday, 14 March 2011

SSRS - dataset element as parameter

Recently I worked on a SSRS (SQL Server Reporting Services) report that used 2 datasets, each using a different data source (see picture below). The dataset "DataSet2" was using a store procedure to retrieve the desired info. The problem was that as the parameter for this stored procedure I needed to use the value of the field "Field1" from dataset "DataSet1".

When trying to compile such report I got the following error:

The expression used for the parameter ‘@param1’ in the dataset ‘@param1’ refers to a field. Fields cannot be used in query parameter expressions.

2 SSRS datasets with different datasources
It came out that such construction is not allowed. I'm guessing it's because you cannot define the order in which the dataset are evaluated.

To achieve such dependency you need to use a sub-report:
  1. Create a new report that will be used as subreport

  2. Move the dataset "DataSet2" to your new report

  3. Move all the report content that depends on "DataSet2" to your new report

  4. Add your new report to the original one as subreport (see how)

  5. Define new subreport parameter that will be using value of the "Field1" from "DataSet1"

  6. Adjust the "DataSet2" in your subreport to use the new subreport param