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.