Sorting X-axis Series in a Line Chart With Y Series Grouping

I have a line chart with Y series grouping that I'm having an issue with. I have duplicated in the attached rptdesign, which uses the Classic Models database. Birt version in the example is 2.6

I have a count set for the Y series, and Y series grouping is set to a 'Customer Name'.

The X series is named "Monthname Yr". What I would like to do is have "Monthname Yr" display in the chart, while the sort is actually by another field "YRMONSTR"

In the chart settings, if one clicks on the 'Sorting and Grouping' button for the X axis, and set the sort to Ascending, then the only option is to sort by the "Monthname Yr" field.

Is there a way to override this and sort by another field, perhaps by script? See attached for an example of the issue.

Thanks,
A.C.

Find more posts tagged with

Comments

chavez_ant

I found that in most cases, not all, you can set your X-axis as unsorted and the chart will pick up the sort set in the database query. It'd still be useful to get a workaround for this issue for the cases where this does not work. Please reply if you know of one!

I also found this bug for the issue in the url below, so visit the url below and vote for the bug to be fixed if you're also running into this issue:

https://bugs.eclipse.org/bugs/show_bug.cgi?id=327253

Thanks,
A.C.

mwilliams

In your report attached above, does the sorting work out as you expect if you do unsorted? Let me know. I thought I'd give finding a workaround a try on this as I know many people run into this.

chavez_ant

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="82591" data-time="1315855764" data-date="12 September 2011 - 12:29 PM">
In your report attached above, does the sorting work out as you expect if you do unsorted? Let me know. I thought I'd give finding a workaround a try on this as I know many people run into this. </blockquote>
 
Here's what's helped in many cases in my environment: update the query to sort in the order you want. Under 'edit group and sorting', Set the chart to 'unsorted' and also disable grouping. The issue is, if your chart is doing any sums or grouping this workaround will result in your chart values not coming out the way you would like. But if the data is displaying in the chart is 'as is' and there's no sums it seems to sort the way we want in most cases. 
 
I tried doing this same workaround in the example attached and it did not work - I got the x-axis in random order, even though I had the addition of an "order by" clause in the query. I'd assume it must be the difference in the databases, oracle vs mysql. So if someone's on oracle it looks like this might be a good suggestion to try, but even then still not 100% 
 
Thx, 
A.C.

chavez_ant

It seems maybe you can customize the x-axis label using script. I looked over this article: http://birtworld.blogspot.com/2010/08/birt-charting-scripting-overview.html , under the "Axis Title and Axis Label Events" section. I'm seeing if there's a way to override the x-axis label to another field, but am unclear how. If anyone in the community has done this or has any tips, give a reply. Thanks!

mwilliams

Sorry for the delay. How about this? This is what I envisioned from the beginning. I order the dataSet and make a computed column to count my "groups". I also set a persistent global variable with the name of the count field and the value of the actual "group". In the chart, I use the computed count column as the x-axis value and then in the script, I replace the count value with the group value that I want. Now it's in order. Maybe this will help in these cases. Let me know.

KSTENNER

I am interested in seeing the script to modify the axis values, but am unable to open the attached report. I have version 2.5.0. Could you please copy the script into the forum or possibly save the report in a different version?

Thanks!
Kim

mwilliams

Initialize: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
groupMY = 0;
temp = "";
</pre>
 
Computed Column: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
if (temp != row["MonthName Yr"]){
temp = row["MonthName Yr"];
groupMY++;
reportContext.setPersistentGlobalVariable(groupMY.toString(),row["MonthName Yr"]);
groupMY;
}
else{
groupMY;
}
</pre>
 
Chart onRender: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
function beforeDrawAxisLabel( axis, label, icsc )
{
label.getCaption().setValue(icsc.getExternalContext().getScriptable().getPersistentGlobalVariable(label.getCaption().getValue()))
}
</pre>

mwilliams

Let me know if you need the example.

KSTENNER

Michael,

Thanks for posting the script. I think in my report I don't need the global variable, though. In my data set, I have the field "Rank" which is my sorting field and "Week" which is what I want to display. Both data elements are bindings in the chart and I have "Rank" as the X Axis. How can I change the chart script above to display the "Week" binding instead of the "Rank" binding?

Thanks,
Kim

mwilliams

I believe you'll need to use the PGV still. In the example, both bindings are in the dataSet, but the chart only has access to the bindings used in the chart. If you're not using the week field in the chart, you won't be able to get to it. You'll just assign the week value to the PGV that is named after your rank field, then recall that PGV in your chart script to replace your label.

chavez_ant

mwilliams,

Thanks for the example! I didn't notice your response from 9/14/11 until just recently this week. I been getting by using the workarounds previously mentioned or converting to graphs without the y-series grouping when an option. This does look like a step in the right direction.

I just recently tried this method of chart sorting in my environment, and found that the y-axis is not correct - all values to the left of the y-axis display as 'null'.

If you look at the output graph in your example from sample db, you'll see that the y-axis values are incorrect there as well. First value is 'null', while the remaining values are '03' until top of chart.

The x-axis does look correct, though in my environment the text gets cut off on the bottom of the graph (maybe because it's expecting an integer and being overridden with a longer text). That can probably be fixed by adjusting some chart settings until it looks ok, do you agree? The y-axis appears to be getting cut off as well in the example.

Any suggestions how to correct the y-axis value issue would be greatly appreciated.

Thanks,
A.C.

mwilliams

A.C.

Sorry, I had just been showing how to change the axis value to another value and didn't think of the end result, which is that without checking the axis you're changing, you'll change both.

If you change the script in the chart script in my example to the following, it will work correctly, I think. Let me know if you see differently.

function beforeDrawAxisLabel( axis, label, icsc )
{
if (axis.isCategoryAxis()){
label.getCaption().setValue(icsc.getExternalContext().getScriptable().getPersistentGlobalVariable(label.getCaption().getValue()))
}
}

AlbertYu

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="92874" data-time="1325193387" data-date="29 December 2011 - 02:16 PM">
Let me know if you need the example. </blockquote>
 
 
Hi Michael, 
 
I too am using version 2.5.0. 
 
Would you be able to convert the example to version 2.5.0? 
 
Thank you, 
Albert