Search

Advertisements

SSRS - Example for Tablix with Sparkline / Bar Chart / Indicator

This article is about an example for creating table with
i) Sparkline
ii) Bar Chart
iii) Indicator

Create Data source and then Datasets

Dataset name : TablixWithGraph

Dataset Query : 

DECLARE @FY TABLE (Products VARCHAR(20),Q1 INT,Q2 INT,Q3 INT)
INSERT @FY SELECT 'SQLServer2008',1000,200,100
INSERT @FY SELECT 'SQLServer2012',1000,1000,1500
INSERT @FY SELECT 'SQLServer2014',1000,3000,5000

SELECT * FROM (
SELECT Products,Q1,Q2,Q3,Q1 AS Q11,Q2 AS Q22,Q3 AS Q33 FROM @FY) tmp
UNPIVOT
(QData FOR Qs IN (Q11,Q22,Q33)
) AS Unpvt



Dataset name : TablixWithIndicators

Dataset Query :




DECLARE @KPI TABLE (Product VARCHAR(20),CurrentMonthSales INT , PreviousMonthSales INT)
INSERT @KPI SELECT 'SQLServer2008',1000,2000
INSERT @KPI SELECT 'SQLServer2012',3000,3000
INSERT @KPI SELECT 'SQLServer2014',5000,2000
SELECT Product,CurrentMonthSales,PreviousMonthSales,
CASE WHEN CurrentMonthSales > PreviousMonthSales THEN 1
     WHEN CurrentMonthSales < PreviousMonthSales THEN 2
     ELSE 0 END Indicator
  FROM @KPI



I am going to use Dataset TablixWithGraph for Tables with Sparkline & Bar Chart 

and Dataset TablixWithIndicators for Tables with Indicator .


Drag&drop Table from Toolbox or right-click on report body - > Insert - > Table.

and then assign columns  from the respective Datasets to the Table columns .


For Table with Sparkline , click on Products column - > at the bottom - > Row Groups - > 
click the drop down of Details - > Group Properties 


Now follow as shown in below screenshot and click on Chart


And then add Line with Markers chart


After adding the Line with Marker chart , delete the chart title , labels and legends for better visibility of sparklines .

Double - click on the chart area , you will notice Chart Data ,
 Under Chart Data for summation Values add QData column from Dataset and
then for Category Groups add Qs column from the Dataset .


Similarly for Table with Bar Chart , add Row Group on Products column and then insert Column chart and then delete the chart title , labels and legends for better visibility of bar charts  .


Now for Table with Indicator - > right-click on Indicator column - > and insert Indicator (3 Up/Down Triangles ) as shown in below image :


After adding 3 Up/Down Triangles , click on that column - > right-click on Indicator1 under Gauge Data - > Summation values as shown below :


Indicator Properties - > Value and States - >
Under Value (fx) - give this expression - =Fields!Indicator.Value
Under States Measurement Unit - > Numeric 
Under Indicator States (keep only Up triangle and delete other triangles) - Color (fx) - give this expression - =Switch(Fields!Indicator.Value = 2, "Red" ,
Fields!Indicator.Value = 1, "Green",
Fields!Indicator.Value = 0 , "Yellow")



Finally click on Preview pane :


See Also :


1 comment:

Artem Kuzin said...

Thank you so much for this article!
You saved lot of my time.

Advertisements