For many reasons, we may feel the need of bringing additional fields to the SSRS dataset. A common issue faced when using SSRS shared datasets is when we change the backend query to bring additional fields for reporting, the dataset fails to recognize this change. Let us try to regenerate this issue to understand this better.
Here we have a simple Reporting Service Project setup:
We have a shared data source ‘Dev.rds’, pointing to one of the databases in the SQL Server instance. Then we have a Shared Dataset ‘Jobs.rsd’. The dataset simply calls a stored procedure, which returns 2 fields JobName and StartTime as shown below. This dataset is then referred to in the report ListJobs.rdl.
Now, let’s say we want to bring another field in the report. To do so we will modify the stored procedure dbo.GetRecords, which is used by our shared dataset. Let’s assume we modified the stored procedure from this:
ALTER PROCEDURE dbo.GetRecords AS BEGIN SELECT JobName, StartTime FROM Job END
ALTER PROCEDURE dbo.GetRecords AS BEGIN SELECT JobName, StartTime, EndTime FROM Job END
Now, that our procedure is returning three fields, we will go to our shared dataset and click refresh fields. After doing so we are able to see the additional field in the field list:
However, when you try to refresh the fields from your report dataset, it does not detect the additional field we just added. Even recreating the dataset entirely (on the report level) or adding the fields manually will not solve this issue:
To solve this, open your shared dataset from solution explorer, click on query designer and simply execute your existing query:
Once the query is executed, you can then open your referenced dataset from your report data panel, simply click on refresh fields, and now the SSRS will detect your additional fields.
I hope this simple trick will save you a lot of energy which I could not.
© All Rights Reserved. Inkey Solutions 2020