SSRS chart labels

Question

I need some help figuring out how to accurately display the labels in a stacked chart in SSRS; I need a single represenation of the upper stack in the below chart.

The chart itself has two states, it can either be based on red or green data, both are in the same data source.

At the moment the chart looks like this (this is based on green data):

As you can clearly see both the labels inside the chart and the legend is absolutely cluttered. The idea is to have a legend where we have two items (Late issues and Not finished issues); one that displays any non-finished issues and one that displays any non-finished issues that are outside the estimated due-date.

For reference: The above chart should have 1 non-outstanding issue and 5 outstanding issues (3 Ongoing and 2 Open, see below for further info about stages).

Inside the chart we want to have a numerical respresentation of above requirements. Basically a number representing the outstanding issues and one that shows any non-finished issues.

This is what a red representation looks like:

At this point I'm not sure what could be wrong anymore. As mentioned they both run on the same dataset, but with slightly different values.

The red tracker has a simple True/False value that it runs most of its data from whereas the green tracker has a numerical representation of three values (5-7). Where the data it represent is: 5 - Open, 6 - Ongoing, 7 - Closed.

I've attempted to only get the green data both when the series is either of the three above mentioned numbers, but also when it's only getting anything that is un-closed (5,6 but not 7).

This is the code to set the labels for the maroon part of the chart (it's only a workable snippet.):

IIF(Count(IIF(Fields!Outstanding.Value = 1 AND 
Fields!TRK_TrackerStatus_LKID2.Value <> 7, 1, Nothing)) = 0, "",
Count(IIF(Fields!Outstanding.Value = 1  AND 
Fields!TRK_TrackerStatus_LKID2.Value <> 7, 1, Nothing))))

Basically what it does is checking if there are more than 0 items that are outstanding and that aren't finished (are not 7). If there are more than 0, it sets the label. If there are 0 counted items, the label should be the empty string.

What is your actual question here? Is it the number in the label, the formatting or the layout that you have issue with and what is your desired result? None of this is clear from your question. — iamdave
Edited the question; basically I need to have a single number that accurately represents the late / outstanding issues. — geostocker
Basically I want 7 to be ignored (7 indicates that the issue has been dealt with) and 5,6 to be shown as a single group. The chart should show items where LKID2 = 5, 6 and where outstanding = 0 on one stack and one where LKID2 = 5,6 and outstanding = 1. Basically I need a stack for outstanding (late) issues and one for non-closed (open) issues. — geostocker

iamdave iamdave · Accepted Answer · 2016-11-07T17:02:18

I think what you are wanting to do is group your Series data based on a status number. You can either do this as a case statement in your dataset query or you can use an expression in your Series Group:

The expression I have used there is as follows:
=switch(Fields!Status.Value = 5, "Group 1", Fields!Status.Value = 6, "Group 2", Fields!Status.Value = 7, "Group 2", TRUE, "Group 3")

This esentially assigns a grouping value to your data based on the values in the field. In this case, a Status of 5 becomes Group 1, a Status of 6 or 7 becomes Group 2 and all other values become Group 3 just to ensure bad data is obvious on the report.

What this does is takes the chart as it would be displayed with the raw data (On the left) and turns it into what I think is how you want to see it (On the right):

You will need to apply the same logic to your chart labels as well. For this reason I would recommend you add a column to your original SQL script that does this grouping for you, so you only have to make changes once.

SSRS chart labels

1 Answers