SSRS: Adding Calculated Fields To Data Sets

This blog post covers an example of how to add a simple calculated field to a Dataset in SQL Server Reporting Services using Report Builder 3 (against SQL Server 2008R2). It isn't a massively complicated process except that Microsoft seem to be assuming absolutely no-one does this as all the "nice" features of expressions when working with Reports (i.e. being able to select fields by double-clicking a list) seem to be unavailable.

The easiest thing to do is to start Report Builder 3 and select "New Dataset" from the Getting Started wizard;

Report Builder 3: Getting Started Wizard

Select and active data source connection (for the purposes of this example I'll be connecting to an Oracle data source). Once you've selected a data source you'll be presented with the editor window;

Report Builder 3: SQL Editor Window

Now enter the following simple SQL;


SELECT 1 AS VALUE1,
 2 AS VALUE2
FROM DUAL

This is a very simple piece of SQL that will just return a single row with two columns called VALUE1 and VALUE2 which contain the values 1 and 2;

Report Builder 3: Sample SQL with Result

Now we are going to add two Calculated Columns;

• VALUE3 = VALUE1 + VALUE2, and
• VALUE4 = VALUE1/VALUE2

Click on "Set Options" (in the ribbon bar at the top of the window);

Report Builder 3: Shared Dataset Properties

Click "Add" and select "Calculated Field" from the drop down that appears;

Shared Dataset Properties: New Field

Enter the values;

• VALUE3 (Field Name)
• =Fields!VALUE1.Value + Fields!VALUE2.Value (Field Source)
• VALUE4 (Field Name)
• =Fields!VALUE1.Value / Fields!VALUE2.Value (Field Source)

And click "OK" to apply the change.

Re-run the query and you'll notice that your two new fields are NOT being displayed. This is slightly unexpected but if the expression you have entered is incorrect you will see an error message like;

Report Builder 3: Invalid Expression Error

The actual text of the error message is;

The Value expression for the field ‘=Fields!VALUE1.Value1 / Fields!VALUE2.Value’ contains an error: [BC30456] 'Value1' is not a member of 'Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Field'.

You'll notice that I just added a "1" after the Value property name.

Now you've saved the dataset close and re-open Report Builder and this time from the wizard select "New Report" and then "Table or Matrix Wizard", select the dataset you've just saved, click "Next" and you will see;

Report Builder 3: New Table or Matrix Wizard Screen

Which shows the four available fields. Select them all (i.e. move them into the Values box) and then click "Next" all the way through to the generated report and then run it;

Report Builder 3: Successfully Showing Four Fields

Now, as a final check, if you go through into the Dataset Properties of the Dataset you've just added and look at the fields;

Report Builder 3: Dataset Properties

You can see that the new fields have been added from the Dataset and that the calculation is being done in the dataset (otherwise Field Source would be an expression).