Custom ordering of series field for stacked chart in SSRS08 TFS

Question

Hi all--I'm having some issues with a report I'm trying to create for TFS work items that creates a stacked chart based on work item state and the cumulative count. I have Sum(Cumulative_Count) as the data field, System_State as the series field and Date as the category field.

The problem I'm having is that for this stacked chart to be immediately useful to us the System_State needs a very specific order--namely Not Done should be on the top, followed by In Progress, Ready for Review etc and I can't for the life of me figure out how to define this ordering. The series sort options only allow A to Z and Z to A and I am very unfamiliar with MDX/expressions.

I have tried using the custom code section to manually define an array of strings in the order I need but I can't seem to figure out how to make use of it as the series or as a comparator.

I don't really mind how static/hacky this ends up for now so any suggestions would be very much appreciated.

Josh

Answer 1

I guess I spoke to soon--after spending almost 4 hours trying to wrap my mind around the OLAP cube I came up with a much simpler solution. I use the following as the expression for the sort constraint (set to Z to A):

=Switch(Fields!System_State.Value = "Not Done", "A", Fields!System_State.Value = "In Progress", "B", Fields!System_State.Value = "Ready for Review", "C", Fields!System_State.Value = "Ready for Promotion", "D", Fields!System_State.Value = "Promoted", "E", 1 = 1, "Z")

It's fairly hacky but works a dream. I should of thought of this ages ago--KISS, I guess.