Stacked Bar - Custom chart sorting/color setting
Question
Answers
-
Ulriks linked blog post deals with adding labels and colours as you requested, and gives a great hint towards the right way to do a chart which looks stacked and is ranked by total value.
Sorting charts by aggregate values does not seem to work in simple ways at all.
If you have a normal single column chart with an option set as a category (groupby) you cannot sort by the value (sum or count), it always sorts by the option set integer values. If the groupby category is a lookup it seems to work just fine to rank by (orderby) total.
If you have a stacked chart then it seems there is no sensible way to get it to sort by total value or by a single field
As an example, I did a Chart showing Cases, aggregated as a count, grouped by Customer, and stacked by Case Type. Adding a groupby on the count alias does not seem to sort on anything consistent at all! It does not sort by total count per customer, nor does it sort by count on any one case type. It is sorted by the count of the casetype which occurs most for each customer. So for the first two bars in the screenshot below, the green bar (Request) is longest, so this value is used to sort these customers first. The third bar has more Problems than Requests so that value is used, putting it third. The next bar has most Requests, so this is used to rank it, putting it fourth. A horrible result:
It seems to make no difference whether I put a groupby inside or outside a link-entity clause, using a standard stacked chart.
So, using Ulrik's ingenious approach, don't start out with a stacked chart, instead use a chart with several series doing the same count, put each inside a link-entity clause then filter each for only one value (in my scenario by Case Type, in your possibly Activity Status, and leave the last unfiltered for the total. Then change the chart types to stacked except the total which needs to be a point (make sure none of them have tried to sneak on to a secondary Y-Axis in the original process of adding the series). In my scenario I chose to make the total have no visible markers or label in its series, but just used it in a groupby tag. Result below:
Obviously this might need some tidying up, legend labels changing and so on, but as a proof of concept I am happy. While I have sorted this by total, as you indicate, there may be situations where you want to sort by any one of the values, for example rank by Customer with the most high priority Cases, or highest value of Opportunities closing this month (with additional bars for later months).
Partial XML of relevant bits for reference:
<entity name="incident"> <attribute groupby="true" alias="customer" name="customerid" /> <attribute alias="Total" name="incidentid" aggregate="count" /> <order alias="Total" descending="true" /> <link-entity name="incident" from="incidentid" to="incidentid" link-type="outer"> <attribute alias="Question" name="incidentid" aggregate="count" /> <filter> <condition attribute="casetypecode" operator="eq" value="1" /> </filter> </link-entity> ...
<categorycollection> <category alias="customer"> <measurecollection> <measure alias="Question" /> </measurecollection> <measurecollection> <measure alias="Problem" /> </measurecollection> <measurecollection> <measure alias="Request" /> </measurecollection> <measurecollection> <measure alias="Total" /> </measurecollection> </category> </categorycollection><Series> <Series ChartType="StackedColumn" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series> <Series ChartType="StackedColumn" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series> <Series ChartType="StackedColumn" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series> <Series ChartType="Point" IsVisibleInLegend="False" IsValueShownAsLabel="False" MarkerBorderColor="Transparent" MarkerColor="Transparent" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series> </Series>
Hope this helps.
Adam Vero, Microsoft Certified Trainer | Microsoft Community Contributor 2011
Blog: Getting IT Right
All replies
-
Ulriks linked blog post deals with adding labels and colours as you requested, and gives a great hint towards the right way to do a chart which looks stacked and is ranked by total value.
Sorting charts by aggregate values does not seem to work in simple ways at all.
If you have a normal single column chart with an option set as a category (groupby) you cannot sort by the value (sum or count), it always sorts by the option set integer values. If the groupby category is a lookup it seems to work just fine to rank by (orderby) total.
If you have a stacked chart then it seems there is no sensible way to get it to sort by total value or by a single field
As an example, I did a Chart showing Cases, aggregated as a count, grouped by Customer, and stacked by Case Type. Adding a groupby on the count alias does not seem to sort on anything consistent at all! It does not sort by total count per customer, nor does it sort by count on any one case type. It is sorted by the count of the casetype which occurs most for each customer. So for the first two bars in the screenshot below, the green bar (Request) is longest, so this value is used to sort these customers first. The third bar has more Problems than Requests so that value is used, putting it third. The next bar has most Requests, so this is used to rank it, putting it fourth. A horrible result:
It seems to make no difference whether I put a groupby inside or outside a link-entity clause, using a standard stacked chart.
So, using Ulrik's ingenious approach, don't start out with a stacked chart, instead use a chart with several series doing the same count, put each inside a link-entity clause then filter each for only one value (in my scenario by Case Type, in your possibly Activity Status, and leave the last unfiltered for the total. Then change the chart types to stacked except the total which needs to be a point (make sure none of them have tried to sneak on to a secondary Y-Axis in the original process of adding the series). In my scenario I chose to make the total have no visible markers or label in its series, but just used it in a groupby tag. Result below:
Obviously this might need some tidying up, legend labels changing and so on, but as a proof of concept I am happy. While I have sorted this by total, as you indicate, there may be situations where you want to sort by any one of the values, for example rank by Customer with the most high priority Cases, or highest value of Opportunities closing this month (with additional bars for later months).
Partial XML of relevant bits for reference:
<entity name="incident"> <attribute groupby="true" alias="customer" name="customerid" /> <attribute alias="Total" name="incidentid" aggregate="count" /> <order alias="Total" descending="true" /> <link-entity name="incident" from="incidentid" to="incidentid" link-type="outer"> <attribute alias="Question" name="incidentid" aggregate="count" /> <filter> <condition attribute="casetypecode" operator="eq" value="1" /> </filter> </link-entity> ...
<categorycollection> <category alias="customer"> <measurecollection> <measure alias="Question" /> </measurecollection> <measurecollection> <measure alias="Problem" /> </measurecollection> <measurecollection> <measure alias="Request" /> </measurecollection> <measurecollection> <measure alias="Total" /> </measurecollection> </category> </categorycollection><Series> <Series ChartType="StackedColumn" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series> <Series ChartType="StackedColumn" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series> <Series ChartType="StackedColumn" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series> <Series ChartType="Point" IsVisibleInLegend="False" IsValueShownAsLabel="False" MarkerBorderColor="Transparent" MarkerColor="Transparent" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series> </Series>
Hope this helps.
Adam Vero, Microsoft Certified Trainer | Microsoft Community Contributor 2011
Blog: Getting IT Right
