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.


Wednesday, October 30, 2019

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.


Tuesday, October 29, 2019

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.


Wednesday, October 23, 2019

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)


Tuesday, October 1, 2019

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.



Sunday, September 29, 2019

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:



Saturday, September 28, 2019

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