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: