Blogs

Microsoft Business Intelligence: SSRS > Shared Dataset > Not detecting NEW fields > a simple fix

Inkey, April 20, 2020 9779 Views

For many reasons, we may feel the need of bringing additional fields to the SSRS dataset. A common issue faced when using SSRS i.e. Microsoft Business Intelligence 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 Microsoft 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

To This: 

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:

 

---

Discover the power of our services:

Microsoft Fabric, Microsoft Power BI Consulting, Microsoft Business Intelligence, Microsoft SQL Server, and Dynamics 365 Business Central. By the power of these services, from advanced analytics to seamless business integration, we’ve got the expertise you need to optimize operations and drive growth. Harness the potential of your data infrastructure with our comprehensive suite of solutions.

---

 

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 (Microsoft Business Intelligence) will detect your additional fields.

I hope this simple trick will save you a lot of energy which I could not.

Happy reporting!