Monday, February 10, 2020

Could not update list of Fields for the query in SSRS

While Connecting to the stored procedure from the SSRS reports getting below error.

"Could not create a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct."

An item with the same key has already been added.



I have added here sample stored procedure created.

create procedure products
as
begin
    with #ProductCat as(
        select DPC.EnglishProductCategoryName,DPC.FrenchProductCategoryName,dpsc.EnglishProductSubcategoryName,DPSC.FrenchProductSubcategoryName
        from DimProductCategory DPC
        join DimProductSubcategory DPSC on DPC.ProductCategoryKey=DPSC.ProductCategoryKey
    )
select PC.EnglishProductCategoryName as Product_Category,PC.FrenchProductCategoryName as Product_Category
,PC.EnglishProductSubcategoryName as Product_SubCategory
from #ProductCat PC
end

Two column names are having in the output with same name.
unfortunately SSMS will not throw any error for this.
Make proper naming while naming the dataset column names.


Sunday, November 3, 2019

Report Snapshots in SSRS

·         A report snapshot provides the ability to run a report at a scheduled time or on demand and save the report in the ReportServer database.
·         You can schedule report snapshots to be run during off-peak hours; this can reduce the load on your database servers
·         When select a report snapshot for viewing, the report server retrieves the stored report from the report server database and shows the data and layout that were current for the report at the time the snapshot was created.
·         Any data source used in the report must store the credentials for connecting to the database
·         Make sure SQL Server Agent is running.

Steps for creating snapshot history report

Step 1 :

Make sure storing the data source credential.


Step 2 :

Step to create Manual snapshot.

Report Name -> Manage -> History Snapshot -> New History Snapshot

It will create new snapshot of the report.
Step 3 :

Step to create using Schedule and setting.

Report Name -> Manage -> History Snapshot -> Schedule and setting

Select both the check box

·         Create History snapshot on a schedule
·         Allow people to create snapshot manually
In Advanced section
          Retain in all history snapshots.



Edit schedule under report specific schedule

Provide the schedule details and click Apply


Click Apply to save your changes

You can see SQL Agent job is created under ob Activity Monitor.

Now you can see reports are created under History Snapshot folders and it ran on scheduled time.