Centering a group label in a chart in SSRS

Question

I have a chart in a report in SSRS to display ticket counts. There are 3 groups: Submitted, Closed - FCR, Open. Each group has a 4 ticket types of Submitted, Closed, FCR, Open. For the Submitted and Open groups I want the labels on the graph to be centered over the bar. When the counts are 0, the type is not shown. This is what the chart looks like now:

This is the SWITCH statement I added to the ticket type Series group to order the types:

=Switch(
Fields!TicketGroup.Value = "Submitted" AND Fields!TicketType.Value = "Submitted", 1,
Fields!TicketGroup.Value = "Submitted" AND Fields!TicketType.Value = "Closed", 2,
Fields!TicketGroup.Value = "Submitted" AND Fields!TicketType.Value = "Open", 3,
Fields!TicketGroup.Value = "Submitted" AND Fields!TicketType.Value = "FCR", 4,

Fields!TicketGroup.Value = "Closed" AND Fields!TicketType.Value = "Open", 1,
Fields!TicketGroup.Value = "Closed" AND Fields!TicketType.Value = "Closed", 2,
Fields!TicketGroup.Value = "Closed" AND Fields!TicketType.Value = "FCR", 3,
Fields!TicketGroup.Value = "Closed" AND Fields!TicketType.Value = "Submitted", 4,

Fields!TicketGroup.Value = "Open" AND Fields!TicketType.Value = "Submitted", 1,
Fields!TicketGroup.Value = "Open" AND Fields!TicketType.Value = "Closed", 2,
Fields!TicketGroup.Value = "Open" AND Fields!TicketType.Value = "Open", 3,
Fields!TicketGroup.Value = "Open" AND Fields!TicketType.Value = "FCR", 4)

The Closed - FCR centering is correct. The Submitted and Open centering needs to be corrected.
Is my syntax wrong in the switch statement? I don't see why it is not centering

UPDATE Adding a column to sort in each group still does not work. The order is sorted using the first group. Also, I tried using the same value for each ticket type. So that Submitted, Closed and Open are set to 2 and FCR is 3 but that does not work either. The values are identified as Submitted = 3, Closed = 1, FCR = 4, Open = 2.

UPDATE I am trying to use the Lookup Function to concatenate columns to get the correct sort order for each group. It is still not working. The Submitted is , Closed = 2, FCR = 3 and Open = 4. Do the sort values have to be different? Can they not be the same sort value in different groups?

UPDATE Is there a way to vary the distance in the x-axis labels?

Unfortunately, the SORT is only done once for the chart - you can't have it sort separately for each group. Maybe separate charts pushed together to look like one? You can remove the lines (and the axis) if you have the numbers on the bar. Less is more is the current trend (and DimGray instead of black). — Hannover Fist
Could you add a new column to your dataset that sets the sort order, then just order by that colum. Essentially replicating your switch statement with a case in SQL (assuming it's coming from SQL Server). — Alan Schofield
I tried @AlanSchofield suggestion but it is only selecting the sort order from the first group. I tried adding the first sort expression to be ticket group and the second to be ticket type but that does not work either. How can I create an expression for the ticket type to use the ticket group then the ticket type? — Gloria Santin

Alan Schofield Alan Schofield · Accepted Answer · 2017-04-06T20:34:22

I've put together a chart and it seems to work, assuming I've understood you correctly that is!

I built a dummy dataset using the following query

declare @data table(TicketGroup varchar(20), TicketType varchar(20), Amount int)

insert into @data
VALUES
('Submitted', 'Submitted', 10),
('Submitted', 'Submitted', 11),
('Submitted', 'Closed', 9),
('Submitted', 'Closed', 12),
('Submitted', 'Closed', 13),
('Submitted', 'Open', 8),
('Submitted', 'FCR', 14),
('Closed - FCR', 'Open', 7),
('Closed - FCR', 'Open', 6),
('Closed - FCR', 'Closed', 10),
('Closed - FCR', 'Closed', 11),
('Closed - FCR', 'Closed', 12),
('Closed - FCR', 'FCR', 3),
('Closed - FCR', 'Submitted', 8),
('Open', 'Submitted', 4),
('Open', 'Closed', 5),
('Open', 'Open', 6),
('Open', 'Open', 7),
('Open', 'FCR', 9),
('Open', 'FCR', 10)

select *
    ,   CASE 
            WHEN d.TicketGroup = 'Submitted' THEN 1
            WHEN d.TicketGroup = 'Closed - FCR' THEN 3
            WHEN d.TicketGroup = 'Open' THEN 3
        END AS SortLevel1   
    ,   CASE 
            WHEN d.TicketGroup = 'Submitted' and d.TicketType = 'Submitted' THEN 1
            WHEN d.TicketGroup = 'Submitted' and d.TicketType = 'Closed' THEN 2
            WHEN d.TicketGroup = 'Submitted' and d.TicketType = 'Open' THEN 3
            WHEN d.TicketGroup = 'Submitted' and d.TicketType = 'FCR' THEN 4

            WHEN d.TicketGroup = 'Closed - FCR' and d.TicketType = 'Open' THEN 1
            WHEN d.TicketGroup = 'Closed - FCR' and d.TicketType = 'Closed' THEN 2
            WHEN d.TicketGroup = 'Closed - FCR' and d.TicketType = 'FCR' THEN 3
            WHEN d.TicketGroup = 'Closed - FCR' and d.TicketType = 'Submitted' THEN 4

            WHEN d.TicketGroup = 'Open' and d.TicketType = 'Submitted' THEN 1
            WHEN d.TicketGroup = 'Open' and d.TicketType = 'Closed' THEN 2
            WHEN d.TicketGroup = 'Open' and d.TicketType = 'Open' THEN 3
            WHEN d.TicketGroup = 'Open' and d.TicketType = 'FCR' THEN 4
        END as SortLevel2
from
    (SELECT TicketGroup, TicketType, SUM(Amount) as Amount FROM  @data GROUP BY TicketGroup, TicketType) d

I then created a simple column chart.Here's the design.

Then for the two Category groups, Click the drop down in the designer and click Category Group Properties.

Go to the Sorting tab and set the sort as SortLevel1 or SortLevel two respectively.

The final output (in need of some tidying!) looks like this.

Centering a group label in a chart in SSRS

1 Answers