Search code examples
reporting-servicestfsssrs-2016

ssrs group by field name

In SSRS I have a dataset that pulls metrics from TFS work items like this:

ID  | Time in Ready  |  Time in Active  |  Time in Resolved
-----------------------------------------------------------
1   |       6        |        3         |        2
2   |       2        |        4         |        1
3   |       1        |        7         |        1

I would like to create a bar chart that averages the time in each state like this:

5  |                 |       4.7        |  
4  |        3        |      -----       |
3  |      -----      |      |   |       |        1.3
2  |      |   |      |      |   |       |       _____
1  |      |   |      |      |   |       |       |   |  
-------------------------------------------------------------
   |  Time in Ready  |  Time in Active  |  Time in Resolved

but I cannot figure out what to put in my Category group to get it to group by field names. If I just add the 3 aggregate fields into the Values section, the titles don't show up in the horizontal axis.

Can I produce the expected chart with the given dataset, and how do I setup the chart properties?

Solution

  • If you cannot change the dataset (which is the easiest solution), you could consider using a table and Databars, but it is quite a bit of work.

    Drag a table onto your report and attach it to your dataset. For the example above you will need 4 columns. The left most column will be used to hold the axis labels.

    Delete the header row in the table.

    Create a new Group as a parent of the existing details Row Group. Right mouse click the Details Row Group -> Add Group -> Parent Group. Then in the Tablix Group type "True" into the group by text box. This will group the entire dataset.

    Delete the Details Group (right mouse click on the Details Group then Delete Group), choose "Delete Group only" in the option box that opens.

    Delete the first column that has True in the first row, this is the group by column and not required. Choose "Delete Column only" in the option box that opens.

    If you have not done so at this point add a new column so there are four columns.

    Insert a row Outside Group Above (this will be the title). Merge the right three cells. And enter the name of the chart into this box.

    Insert a row Inside Group Below (these will be the column labels). Enter the three column labels in the bottom row in the three right most cells.

    In the middle row in the first cell enter 0 (this will be the axis), then in the other three cells enter the average calculation for each column. If you want to use data-labels on your bars, I would recommend using a formula like this:

    =Format(Avg(Fields!Time_in_Ready.Value),"##.##")

    At this point it should look like this:

    Set up table

    Then you need to drag and drop a Databar (Column) into the four cells in the middle row. Then right mouse click each one and "Convert to Full Chart". Then delete the chart title, legend and x axis title in each one. It should then look like this:

    Full chart

    Then you need to set the axis max and min to the same for each graph. Right mouse click on the axis in each graph and click on Vertical Axis Properties. In Axis options either set the Maximum to a suitable number or use the following formula to calculate the largest average value, rounded up.

    =ROUND(SWITCH(AVG(Fields!Time_in_Ready.Value) > AVG(Fields!Time_in_Active.Value) & AVG(Fields!Time_in_Ready.Value) > AVG(Fields!Time_in_Resolved.Value), AVG(Fields!Time_in_Ready.Value), AVG(Fields!Time_in_Active.Value) > AVG(Fields!Time_in_Resolved.Value), AVG(Fields!Time_in_Active.Value), True,AVG(Fields!Time_in_Resolved.Value))) + 1

    You also need to set the Interval in this case I would use 1 but for larger averages you might use 5 or 10. Now run the chart to check the results, it should look like this:

    Set up axis

    You could add minor gridlines now if you wanted, (by right clicking on each of the axis' and then Show Minor Gridlines) and add Data labels (right click on each bar and Show Data Labels).

    Now we need to hide the axis on the three right most cells which hold the bars (right mouse click on the axis then Vertical Axis Properties -> Labels -> Hide Axis Labels).

    Then we need to make some changes to the first cell that holds the axis. Right mouse click on the axis then click the Labels tab and click the Hide First and Last Labels (this is needed to make sure the axis' align with the other graphs). Then click the Label Font table and fix the font size at something suitable like 10pt (this is required as we are going to reduce the size of this column and still need the labels big enough).

    Now set the width of the first column (axis) to approx 0.8cm and the other columns to around 1.5cm.

    The final chart in the Designer should look like this:

    Designer

    The final chart in the Preview should look like this:

    Preview

    You could rotate the category labels by using the WritingMode Property set to Rotate270