Search code examples
reporting-servicesssrs-2012ssrs-2016

Stacked/Grouped SSRS Chart - Issues with Axis Labeling

I'm tasked with taking a manual process from creating Excel charts and making it work with SSRS.

What I need to do is be able to group based on a month number and a status.

Here is a snippet of the data:

NumberOfCases   Subrogation_Type_VL_Name    Case_Status_VL_Name  MonthNumber
4               MVA                         Closed               1
1               Tort                        Closed               1
6               Work Comp                   Closed               1
6               MVA                         Open                 1
2               Tort                        Open                 1
4               Work Comp                   Open                 1
8               MVA                         Closed               2
3               Tort                        Closed               2
4               Work Comp                   Closed               2
7               MVA                         Open                 2
1               Tort                        Open                 2
6               Work Comp                   Open                 2

Here's how it is supposed to look:

enter image description here

I cannot get the chart to group based on Case_Status_VL_Name and MonthNumber.

Is this possible with SSRS 2016?

EDIT

My main issue was having one axis not display all of the labels, which made me think my chart or data was being shown incorrectly.enter image description here.

If your labels aren't showing correctly, click on the chart and set the LabelInterval and Interval properties to 1 which fixed the problem

enter image description here

Solution

  • Set your type to be a series group...

    I've just recreated your chart, ignoring colours and a few other minor things....

    I created a dataset to match your sample but changes the columns names to save typing! 

    DECLARE @t TABLE(Cases int, SType varchar(256), CaseStatus varchar(256), MonthNumber int)

INSERT INTO @t VALUES
(4, 'MVA', 'Closed', 1),
(1, 'Tort', 'Closed', 1),
(6, 'Work Comp', 'Closed', 1),
(6, 'MVA', 'Open', 1),
(2, 'Tort', 'Open', 1),
(4, 'Work Comp', 'Open', 1),
(8, 'MVA', 'Closed', 2),
(3, 'Tort', 'Closed', 2),
(4, 'Work Comp', 'Closed', 2),
(7, 'MVA', 'Open', 2),
(1, 'Tort', 'Open', 2),
(6, 'Work Comp', 'Open', 2)

SELECT * FROM @t

    I then created a new report and added a stacked column chart.

    I then set the chart up as shown in the image below .

    enter image description here

    I changes the sort order of the CaseStatus grouping to be reversed to match your order of "Open" then "Closed"

    I then changed the MonthNumber group's label property to show the month name

    =LEFT(MONTHNAME(Fields!MonthNumber.Value),3)

    That's it...

    This gives the following result.

    enter image description here