nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Ordering bar chart with y series grouping

AdamUK88

I am currently trying to create a bar chart report in eclipse which returns data for the last 3 months based on the top 10 results of last month.

i would like the chart to be organised with each months data above the relevant heading and decreasing from left to right based on last months data.

for example, if i had January, December and November's data, i would like my x axis ordered with the highest january value on the left, decreasing. This should be the case regardless of what is contained in december and novembers data.

however, for each column of data, the 3 bars should be in the order November, December, January.

Here i have another problem. The month's are returned from my SQL query in character format, and when i try to return them in a date format i get an invalid month error.

hope theres enough info here. to summarise:
- each column should have 3 bars, running in chronological order
- the columns should be ordered based on last months data decreasing
- only the required data is returned, so no filtering should be needed

thanks in advance

EDIT: attached pic of what i have so far to show problems
- columns are ordered by december's values, presumably because it is the first alphabetically, but they need to be ordered by January's values
- each column is ordered December, January, November (alphabetically). should be November, December, January

columns are name (char), hours (int), month (char). month is returned as "January 10" or similar.

Find more posts tagged with

Comments

mwilliams

Hi AdamUK88,

So, your date data is passed through to your dataSet as a string? What does the date field look like in your dataSet?

AdamUK88

Thanks for your reply

yeh the dates are returned as strings and appear as they look in the legend of that example, being the month and year (e.g. "January 10")

I've tried using the ordering tools on the y series grouping in select data as well as returning the data in the order i want it to be displayed, but to no avail

EDIT: been trying a few things and managed to return the date as a date rather than a string. however, it is now shown as a full date in the legend rather than the "Month YY" format that i want. Would it be easier to return date format to fix this problem, then modify the legend manually? or keep working with string?

mwilliams

AdamUK88,

You should be able to format your dates in your legend to show what you're wanting through the chart editor. You'd put in a pattern of "MMMM yy". You can find the spot to do this by opening your chart editor, going to the legend section, clicking on the entries button, selecting the format button, then using the custom format code option.

Hope this helps.

AdamUK88

Thanks for the reply

i found the section you explained but the format box was greyed out and couldn't be selected. Is there a setting that can be changed to make this selectable? or some other prequisite?

If i can fix the legend this way then the only problem that remains is that the columns of data are now ordered with the largest november value first, and descending. I would like the ordering to be based on january's values.

mwilliams

AdamUK88,

Is your data simple enough that you only have one line for each month for each name, like:

name, month, value
a, jan, 10
a, feb, 11
a, mar, 12
b, jan, 9
b, feb, 8
b, mar, 7
etc.

??

Let me know.

AdamUK88

yeh thats right, the query returns at most 30 values, with each month having a maximum of 10 values each. there will always be the same number of values for each month (e.g. if january had 7 values, then december and november would have 7 values as well), and the same names will appear in each month (e.g. the 7 names that were returned for january will be returned for december and november as well)

the month's figure is always returned as the first of the month, but the day shouldn't matter as i only want the month and year in my report.

example dataset:

Name hours date
a | 123 | 01 Jan 2010 00:00
b | 245 | 01 Jan 2010 00:00
c | 326 | 01 Jan 2010 00:00
d | 257 | 01 Jan 2010 00:00
e | 212 | 01 Jan 2010 00:00
f | 4 | 01 Jan 2010 00:00
g | 6 | 01 Jan 2010 00:00
h | 3 | 01 Jan 2010 00:00
i | 2 | 01 Jan 2010 00:00
j | 56 | 01 Jan 2010 00:00
a | 776 | 01 Dec 2009 00:00
b | 32 | 01 Dec 2009 00:00
c | 53 | 01 Dec 2009 00:00
d | 56 | 01 Dec 2009 00:00
e | 2 | 01 Dec 2009 00:00
f | 12 | 01 Dec 2009 00:00
g | 4 | 01 Dec 2009 00:00
h | 6 | 01 Dec 2009 00:00
i | 6 | 01 Dec 2009 00:00
j | 66 | 01 Dec 2009 00:00
a | 3 | 01 Nov 2009 00:00
b | 2 | 01 Nov 2009 00:00
c | 2 | 01 Nov 2009 00:00
d | 4 | 01 Nov 2009 00:00
e | 6 | 01 Nov 2009 00:00
f | 67 | 01 Nov 2009 00:00
g | 645 | 01 Nov 2009 00:00
h | 8 | 01 Nov 2009 00:00
i | 3 | 01 Nov 2009 00:00
j | 53 | 01 Nov 2009 00:00

Thanks again

EDIT: made the data a little easier to read . . . just

mwilliams

AdamUK88,

Ok. Check out this report. I brought the data into a table, grouped the table by date and sorted the group in descending order. I also put a sort on the table to sort the hours in descending order. I then used this table as the source for the chart and hid the table. I also went in like I mentioned before and made the date format in a way to show only the month and year in the legend. Hopefully this is what you're looking for and this solution will work for you. Let me know if you have any questions.

You'll need to set the directory location in the dataSource to where you save the .txt file.

AdamUK88

Sorry for late reply, went home for the weekend :P

tried to run your report but got the error "The report file of version 3.2.20 not supported"

would you be able to post a version that's compatible?

eclipse: 3.4.1
birt: 2.3.1

Thanks

mwilliams

AdamUK88

You could try changing the version information in the XML Source to match the version info for your version. If this doesn't help, I'll make one. Let me know.

AdamUK88

nope, no luck. tried changing the version values to what i have in reoprts i made from scratch but still getting the same error

if you can't manage to get a compatible version i'll try to recreate what you've done given what u said. Expect feedback

mwilliams

AdamUK88,

I should be able to get another one for you here before long. No worries.

mwilliams

AdamUK88,

This version was made in 2.3.1. As you stated above, the format option for the legend labels isn't available in 2.3.1. It was available in my first design created in 2.5.1. You'll probably have to manually replace the legend values if you cannot upgrade to 2.5.

Let me know if you have questions.

AdamUK88

works a treat! one slight problem though. the 3 bars in each column of data should appear chronologically, whereas the columns are ordered by last month's data descending (starting to wear thin now isn't it XD)

e.g. if the top 3 names in january were b, c, a in that order, then the bars would be in the order:

column 1: nov b, dec b, jan b
column 2: nov c, dec c, jan c
column 3: nov a, dec a, jan a

the method i used to achieve this before (by applying a sort to the optional y series grouping) is greyed out in the example you sent me

i'll try to recreate what you've achieved so far and see if that will let me use the function, let me know if you find an alternative solution though

Thanks again, hope this isn't bugging you too much :P

mwilliams

AdamUK88,

Ah, I had thought you wanted to see them in reverse order chronologically with the latest month first. I'll see what I can do.

AdamUK88

looking into scripting the legend entries to get them to display as i want them but having a little trouble. found a piece of script elsewhere on the forum to modify each legend entry. any ideas on modifying this with formateDate (i assume anyway, my java is hardly amazing

) 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>function beforeDrawLegendItem( lerh, bounds, icsc )
{
recordId = lerh.getLabel().getCaption().getValue();
newLegendValue = DateFormat.format(recordId, "MMMM YY");
lerh.getLabel().getCaption().setValue(newLegendValue);
}</pre>
 
for the record when i try to import dateformat i get syntax error

mwilliams

AdamUK88, 
 
Try out this code for your legend: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
function beforeDrawLegendItem( lerh, bounds, icsc )
{

recordId = lerh.getLabel().getCaption().getValue();
df = new Packages.java.text.SimpleDateFormat("yyyy-MM-dd");
myDate = df.parse(recordId);
dt = new Packages.java.text.SimpleDateFormat("MMMM yy");
newLegendValue = dt.format(myDate);
lerh.getLabel().getCaption().setValue(newLegendValue);
}
</pre>

AdamUK88

sorry for late reply, home for the weekend then had something else on. 
 
anyway, thanks a lot for your code! had to tweak it a bit (cos of the input of my dates and an additional Y in the new format not being recognised) but otherwise works great 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
function beforeDrawLegendItem( lerh, bounds, icsc )
{

recordId = lerh.getLabel().getCaption().getValue();
df = new Packages.java.text.SimpleDateFormat("E MMM dd hh:m:s z y");
myDate = df.parse(recordId);
dt = new Packages.java.text.SimpleDateFormat("MMMM y");
newLegendValue = dt.format(myDate);
lerh.getLabel().getCaption().setValue(newLegendValue);
}
</pre>
 
decided to go ahead with having the month's ascending on each column. if you're on the verge of finding a solution to that then don't let me stop you, but don't lose sleep over it

thanks so much for al your help mwilliams

mwilliams

AdamUK88,

No problem. I will let you know if I find something on reversing the order of the bars. Let us know whenever you have questions!