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:

How to store image in SQL Server database table column

Below are the steps to import image file to sql server database

Step 1:

Create table

CREATE TABLE ImageUpload (

   ImageName NVARCHAR(40) PRIMARY KEY NOT NULL

   , ImgFileName NVARCHAR (100)

   , DataFile VARBINARY (max)

   )

GO

Step 2:

Insert record

insert into ImageUpload (ImageName, ImgFileName, DataFile)

select 'NewYearCake','Newyear.jpg',BulkColumn

FROM Openrowset( Bulk 'G:\Image\DSC02319.JPG', Single_Blob) as img

select * from ImageUpload

You can verify whether image got uploaded correctly by using SSRS.
Please visit the below link for more info.

Display Database Images in SSRS Report

SQL Server - Different types Formatting of Date/Time with Example

MM/DD/YYYY to YYYY/MM/DD

Declare @DateFormat varchar(20)

set @DateFormat='02/21/2019' --MM/DD/YYYY

Select @DateFormat as SourceFormat, CONVERT(Date,@DateFormat,101) as TargetFormat

SourceFormat   TargetFormat

02/21/2019         2019-02-21

DD/MM/YYYY to YYYY/MM/DD

Declare @DateFormat varchar(20)

set @DateFormat='24/02/2019' --DD/MM/YYYY

Select @DateFormat as SourceFormat, CONVERT(Date,@DateFormat,103) as TargetFormat

SourceFormat   TargetFormat

24/02/2019         2019-02-24

YYYY/MM/DD to DD/MM/YYYY

Declare @DateFormat varchar(20)

set @DateFormat='2019/05/24' --YYYY/MM/DD

Select @DateFormat as SourceFormat, CONVERT(VARCHAR(10), CAST(@DateFormat AS DATETIME), 103) as TargetFormat

SourceFormat   TargetFormat

2019/05/24         24/05/2019

DD/MM/YYYY to MM/DD/YYYY

Declare @DateFormat varchar(20)

set @DateFormat='20/05/2019' --DD/MM/YYYY

Select @DateFormat as SourceFormat, convert(varchar, convert(date, @DateFormat, 105), 101) as TargetFormat

SourceFormat   TargetFormat

20/05/2019         05/20/2019

MM-DD-YYYY to DD-MM-YYYY

Declare @DateFormat varchar(20)

set @DateFormat='05-24-2019' --MM-DD-YYYY

Select @DateFormat as SourceFormat, convert(varchar, convert(date, @DateFormat, 101), 105) as TargetFormat

SourceFormat   TargetFormat

05-24-2019          24-05-2019