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 :
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 :
1 comment:
Thank you so much for this article!
You saved lot of my time.
Post a Comment