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

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

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

Below are steps to show Row headers in every page of a report when using a Row groups.

Generally when there is a static report if we make RepeatOnNewPage property to True. Which will solve the purpose.

But when Row groups or column groups are there we need o set few more property  to make header visible on each page.

Click on the Tablix properties and  Click on Repeat header rows in each page and Repeat Header Columns on each page to true.

Click on Advanced tab on the Row Group pane.

Go to Row Group-> Static-> Properties-> RepeatonNewPage->True

Go to Row Group-> Static-> Properties-> KeepwithGroup->After

Now Run the Report. You can view the header in every page.

For details video visit here:

How to Create Drillthrough report in SSRS

A drillthrough report is a report that a user opens by clicking a link within another report. Drillthrough reports commonly contain details about an item that is contained in an original summary report. The data in the drillthrough report is not retrieved until user click on the link in the main report.

Step by Step process to create Drill through report.

Two reports are required to create, Summery and Details report

Step 1:

First Create the Details Report.

 I have used the example from AdventureWorksDW2017 database.

Create the Parameter: Product Name . 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.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)

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.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

              group by Prod.EnglishProductName

Insert table into the report and format it as below.

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 : Product name from summery report.

You can change the product Name text box to hyperlink format so that easily noticed during run time.

Run the Summery report::

Click on an of the product name which will Go to the details report.

Find Video below: