SQL2008 SSRS – Problem uploading old reports?

I recently tried moving some of my really old reports to SQL 2008 Reporting Services and encountered a few problems.

One particular problem I encountered was with a report which contained several sub-reports and these sub-reports were using a field from the master report’s data (primary key) to obtain their own data. PK – FK relationships.

This has worked fine on the original report server, the report has been running successfully for years.

The sub reports uploaded to 2008 fine but when I tried to upload the master report I received the following error :  “The value expression for the subreport ‘X’ refers to the field ‘Y’.  Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope. (rsFieldReference)”    I received the error 11 times, 1 for each of the sub reports.

The messages are quite helpful, they give you the subreport name and the field at issue. 

Opening up the report file in a text editor and locating the first subreport I could see what the problem was :

     <Subreport Name=”ModuleEquivalent”>
        <Style />
        <ZIndex>171</ZIndex>
        <DataElementOutput>Output</DataElementOutput>
        <ReportName>ModuleEquivalent</ReportName>
        <Height>0.5cm</Height>
        <Width>17cm</Width>
        <Parameters>
          <Parameter Name=”MODULEID”>
            <Value>=Fields!ID.Value</Value>
          </Parameter>
        </Parameters>

The fieldname was unqualified, it was looking for the field in the datasets used in the subreport, not in the master reports datasets.    All of the the content had originally been generated using the IDE in VS.Net 1.1 and had been untouched.

The solution was to qualify the parameter field name with the dataset it was coming from, i.e. the dataset in the master report :

     <Subreport Name=”ModuleEquivalent”>
        <Style />
        <ZIndex>171</ZIndex>
        <DataElementOutput>Output</DataElementOutput>
        <ReportName>ModuleEquivalent</ReportName>
        <Height>0.5cm</Height>
        <Width>17cm</Width>
        <Parameters>
          <Parameter Name=”MODULEID”>
            <Value>=First(Fields!ID.Value, “mofs”)</Value>
          </Parameter>
        </Parameters>

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s