Inkey Solution Logo
banner

Blogs

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

, April 20, 2020 9141 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:

SSRS Project

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!

mm

Inkey

INKEY is your solution partner.
Our focus is to deliver you in-time intelligent innovative solutions ("key") for the problems in hand. Maintaining a quality standard right from the inception of a project is our top most priority.

Our team of talented professionals will execute your projects with dedication and excellence. We take ownership and accountability for the effort that goes into meeting our client’s needs.

Years of experience and proven success of delivering innovative custom solutions.

More posts by

One response to “Microsoft Business Intelligence: SSRS > Shared Dataset > Not detecting NEW fields > a simple fix”

  1. I had faced the same issue and Query Designer was greyed out for me. So here is how I solved this issue.

    Created a new shared dataset with the same stored procedure and verified the newly added field is present. Then, renamed the old shared dataset to something else. Renamed the newly created dataset with the original dataset name. It works without doing any other changes.

    Hope this helps someone, who lands here looking for a solution.

    Thanks,
    Rajanand

Leave a Reply

Your email address will not be published. Required fields are marked *

The maximum upload file size: 2 MB. You can upload: image, audio, video, document, spreadsheet, interactive, text, archive, code, other. Drop file here

Would you like to digitize your business and put it on the cloud?
Do you need clear, concise reports for your organization?