The value of the Color property for the textrun which is not a valid Color in SSRS

While setting up background color of a tablix cell or font, below error is generated.

The value of the Color property for the textrun ‘Textbox1.Paragraphs[0].TextRuns[0]’ is “#00ffffff” which is not a valid Color.

Textbox does not support (partial) transparency color.

Need to select valid RGB color values. (instead of selecting " No Color")

Create line break with SSRS expression

We can make line break in SSRS below way

predefined Visual Basic constant can be used

• Using vbCrlf

="Line 1" + vbCrlf + "Line 2"

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.

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.

Linked Reports in SSRS

·         Linked report is a report server item that provides an access to existing report, we can say it is customized short cut to existing report.

·         It derived from existing report and retains original report definition like report layout, data set and data source.

·         security, parameters, location, subscriptions, and schedules can be different from original report.

Example:

I have already created a report and deployed in report server.

Below is the steps to create linked report.

Right click on the report -> Manage -> Create Linked report

Enter Name, Description and Location (if required to change)

Under Advanced tab select appropriate option and then Apply.

You can mark the difference from the below image.

We want to make the change in linked report and wanted to display only red color data by changing in the parameter properties.

Right Click on Linked report -> Manage -> Parameter -> Select red -> Apply

Now run the report.

SSRS Subreports

·         Subreports allow to add one or more related reports to the Data region.

·         It also allow to Nest or Insert One report inside the other.

·         It will allow to pass parameters from the main report to the subreport

Create Details Report:

Create Hidden parameter @Country which will accept the value passed from the main report.

 Create the dataset with below script

SELECT geo.EnglishCountryRegionName,geo.StateProvinceName, sum(Fact.SalesAmount) as SalesAmount

       FROM FactInternetSales AS Fact

              INNER JOIN DimSalesTerritory AS Sales on fact.SalesTerritoryKey=Sales.SalesTerritoryKey

                                                  Inner join DimGeography as geo on geo.SalesTerritoryKey=Sales.SalesTerritoryKey

                where geo.EnglishCountryRegionName in (@Country)

                group by geo.EnglishCountryRegionName,geo.StateProvinceName

Design the report layout, Insert the table and use the columns like below.

You can see how to create databar report by clicking here

Create Main Report

Create dataset with below script

SELECT geo.EnglishCountryRegionName, sum(Fact.SalesAmount) as SalesAmount

    FROM FactInternetSales AS Fact

            INNER JOIN DimSalesTerritory AS Sales on fact.SalesTerritoryKey=Sales.SalesTerritoryKey

                                                Inner join DimGeography as geo on geo.SalesTerritoryKey=Sales.SalesTerritoryKey

group by geo.EnglishCountryRegionName

Design the report layout, Insert the table.

Click Tablix -> Insert Row -> Inside Group - Below

Then Right Click on Textbox -> Insert -> Subreport

Go to subreport properties -> General tab -> Select Subreport Detail report

next parameters tab -> Select parameter (@Country) and select the value.

Run the Report.

The maximum recursion 100 has been exhausted before statement completion

When running the report getting below error while report is trying to execute the SQL script

An error has occurred during report processing. (rsProcessingAborted)

Cannot read the next data row for the dataset .... (rsErrorReadingNextDataRow)

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

max recursion error is also a SQL error. Add below code at the end of the script which will resolve the issue.

Select 

-----------------

From Employee

where

--------------

option (maxrecursion 0)

Create Data bar chart report in SSRS

Follow the below steps to Create Data bar chart report in SSRS

Use the below scripts to create dataset.

SELECT geo.StateProvinceName, sum(Fact.SalesAmount) as SalesAmount

       FROM FactInternetSales AS Fact

INNER JOIN DimSalesTerritory AS Sales on Fact.SalesTerritoryKey=Sales.SalesTerritoryKey

Inner join DimGeography as geo on geo.SalesTerritoryKey=Sales.SalesTerritoryKey

          group by geo.StateProvinceName

·         Right Click table column -> Insert -> Data bar

·         Click on data bar -> Select Values -> Sales Amount

·         Right-click the chart and choose 'Show Data Levels'

·         Save Report and Run the report.

Lookup function in SSRS

Below example will show how to use lookup function in SSRS.

I have created two dataset named DST_ProductDetail and DST_ProductName.

DST_ProductDetail

select ProductKey,UnitPrice,SalesAmount,TaxAmt from FactInternetSales

DST_ProductName

select ProductKey,EnglishProductName from DimProduct

We need to display product name with respective detail in the table. For that need to use lookup function. Here DST_ProductName will be used as reference dataset.

Design the table

Click on the expression of the Product Name column. Enter the below code

=Lookup(Fields!ProductKey.Value,Fields!ProductKey.Value,Fields!EnglishProductName.Value, "DST_ProductName")

Now Run the report. You can see all the Product Name will be appeared in the table.

Document Map Label in SSRS

A document map provides a set of navigational links to report items in a rendered report. When you view a report that includes a document map, a separate side pane appears next to the report like PDFs. A user can click on links in the document map to jump to the report page that displays that item.

The output of report will look like below.

 

Steps to create document map in a report

Create a report with below dataset.

SELECT Prod.EnglishProductName AS ProductName,Fact.SalesOrderNumber, Fact.SalesAmount,

              Fact.TotalProductCost TotalProductCost,Fact.TaxAmt,Sales.SalesTerritoryCountry

       FROM FactInternetSales AS Fact

              INNER JOIN DimProduct AS Prod ON Prod.ProductKey = Fact.ProductKey

              INNER JOIN DimSalesTerritory AS Sales on fact.SalesTerritoryKey=Sales.SalesTerritoryKey

              Where Prod.EnglishProductName in (@ProductName)

Report is grouped on Product name.

@ProductName is a multivalued parameter with below dataset.

Select distinct Prod.EnglishProductName

from DimProduct Prod

 INNER JOIN FactInternetSales AS Fact ON Prod.ProductKey = Fact.ProductKey

Insert a table. Click on the properties of the table.

Set property of Other->DocumentMapLabel

PrductName (Group) properties -> Advanced tab->

Select the document map column name to Productname.

Now run the report. Now you can see the navigation link appears left side of the report where it can be jumped to respective report page.

See the video for more details:

Display Database Images in SSRS Report

Follow the below steps to display database images in SSRS Reports.

I have used AdventureWorksDW2017 database for the below example.

Create the dataset using the below sql script

select FirstName+' '+LastName as EmployeeName,Title as EmployeeTitle,EmailAddress,DepartmentName,EmployeePhoto

from DimEmployee

Create a table in report body and do the formatting.

Click on the text box property-> Insert->Image

Select Image source->Database

User this Field->Column Name (EmployeePhoto)

For detailed video click below:

How to Pass Parameter from one report to another in SSRS | Drillthrough Report

Step 1:

First Create the Details Report.

 I have used the example from AdventureWorksDW2017 database.

Create the multi value Parameter: Product Name . This will accept the value passed from the summery report. Make the visibility property to Hidden.

Similarly Create the Single value Parameter: Color . This will accept the value passed from the summery report. Make the visibility property to Hidden.

Create the data set which contains details of Product, Sales Order, Sales amount and sales country details.

SELECT Prod.Color,Prod.EnglishProductName AS ProductName,Fact.SalesOrderNumber, Fact.SalesAmount,

              Fact.TotalProductCost TotalProductCost,Fact.TaxAmt,Sales.SalesTerritoryCountry

       FROM FactInternetSales AS Fact

              INNER JOIN DimProduct AS Prod ON Prod.ProductKey = Fact.ProductKey

              INNER JOIN DimSalesTerritory AS Sales on fact.SalesTerritoryKey=Sales.SalesTerritoryKey

           Where Prod.EnglishProductName in (@ProductName) and Prod.Color in (@Color)

Insert table into report and format it as below. I have created a row group on product name.

 Step 2:

Create the Product Summery report.

Create the data set which contains the product and sum of sales amount and product cost.

SELECT Prod.Color,Prod.EnglishProductName AS ProductName, sum(Fact.SalesAmount) As SalesAmount,

              sum(Fact.TotalProductCost) TotalProductCost,sum(Fact.TaxAmt) TaxAmt

       FROM DimProduct AS Prod

              INNER JOIN FactInternetSales AS Fact ON Prod.ProductKey = Fact.ProductKey

       where Prod.EnglishProductName in (@ProdName)

              group by Prod.EnglishProductName,Prod.Color

Insert table into the report and format it as below.

Create another dataset Product Name with below script

Select distinct Prod.EnglishProductName

from DimProduct Prod

 INNER JOIN FactInternetSales AS Fact ON Prod.ProductKey = Fact.ProductKey

Create Multi Value parameter ProdName and link the available value from above product Name dataset

Go the text box properties of Product Name highlighted above

Go to Action->Go to Report->Select Details report

Finally configure the parameter as mentioned below.

Name : Parameter name of the details report

Value : Color from summery report and Product Name from Parameter.

Run the Summery Report

Click on color it will go to details report

Go to the Next page and so on you can see the list of product Name which are selected only on summery page.

You can see :

Display Row and Column Headers on Every Pages For Row Groups in SSRS