I have data like below:
select * FROM [SaleOfVeheicle]
ID Year Month Bike Car Bus
-------------------------------------------------------------------
1 2015 Jan 12 11 1
2 2015 Feb 11 3 5
3 2015 Mar 12 12 12
4 2016 jan 12 23 4
5 2016 feb 5 23 1
6 2016 Mar 4 34 7
The idea is to show the Sale of Bike or Car or Bus as a Matrix report in SSRS 2017 and show the total percentage on top of the Stacked barchart.
Note: The targeted sale is 150.
I'm getting the Matrix report like this after writing below script:
SELECT [Year],
[Month],
Bike
FROM [SaleOfVeheicle]
UNION
SELECT 'Total' as [Year],
[Month],
SUM(Bike) AS Bike
FROM [SaleOfVeheicle]
GROUP BY [Month]
From SSRS:
In My “Series label Properties” , “Label Data” expression is as below:
=IIF(Fields!Year.Value = "Total",Fields!Bike.Value,"")
Im My “Series Properties” ->“Fill”->”Pick color”-> “Color expression” is as below:
=IIF(Fields!Year.Value="Total","Transparent","Automatic").
For Percentage calculation, below is my expression: “Series Properties” -> “Configure Series data” -> “Value field”
=(Sum(Fields!Bike.Value)/150)*100
EDIT: after adding an expression [series group properties label expression],
=IIF(Fields!Year.Value = "Total"," ",Fields!Year.Value)
and
=IIF(Fields!Year.Value = "Total",(Sum(Fields!Bike.Value)/150)*100,"")
getting my output Stacked bar chart as below:
What is expected:
- ** it should show percentage, 2 decimal digit with % symbol.**
From note: The targeted sale is 150.
Hope I have explained this correctly and if not please let me know if the question required edit.
What am I doing wrong? How can I achieve this?
Also, let me know if there is any quicker and easier way to do this in SSRS-2017 Please help. Thanks.
