Count rows and group them

Hello there, 
I've encountered a problem while making a good dataset for my pie chart. The table which im making the request to looks like that: 
<img src='http://www.ljplus.ru/img4/s/z/szche/table.GIF' alt='Posted Image' class='bbc_img' /> 
 
And the request i made looks like that: 
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT COUNT(WORKORDER.COMMODITY), WORKORDER.COMMODITY
FROM WORKORDER AS WORKORDER
GROUP BY WORKORDER.COMMODITY</pre>I tested this query and it gives me out the good results i like. It displays a column with commodity name, and a column with number of rows this commodity has been seen (their count). For the sample i provided above it would show 
 
ADM14 - 8 
ADM05 - 3 
ADM01 - 2 
 
That's what i think i need for the pie chart, isnt it? But that query i made just doesnt works at all in birt, showing no errors and returning no data, though if i remove the commodity name selector and grouping counting alone works just fine. 
 
The question is: 
How do i make a pie chart where each slice represents the number of commodities seen across the table? 
 
Thank you in advance, i tried to write the first help request as clear as possible

Find more posts tagged with

Comments

KaiWeing

Hello Ilya,

set the category definition to row["COMMODITY"].

If you have the amounts aggregated like in your select statement, choose the aggregate column as segment size, else put 1 as segment size.

Regards, Kai

KaiWeing

Here is a little example without grouping in the selection. Hope it works in your environment.

Ilya

Hey KaiWeing! 
The problem is that data aggregation doesnt works in birt

At least, for me. This query works fine in database, but birt simply ignores it, so i can't get any aggregated numbers. 
 
And if i place row["commodity"] to categories and 1 to slice size it displays a huge rainbow chat of the whole table

<img src='http://www.ljplus.ru/img4/s/z/szche/mystuff.GIF' alt='Posted Image' class='bbc_img' /> 
 
Dont worry, data is correctly bound, it goes to table very well, i just can't see it in preview because of the stupid library. I can build a 
This is just flat table, without any aggregations. 
 
Sorry, i coudlnt load your report because im using older version of birt, the only compatible with my target system (2.1.2 on eclipse 3.2.2) Maybe you can provide a screenshot of the chart creation dialog?

KaiWeing

Hello Ilya,

you are right. I created my report with the actuate designer and it showed the correct chart. However, the same settings in the eclipse designer (2.3.1) gave me the messed up chart you described.

Which BIRT Version are you using? The Actuate designer seems to be based on 2.3.2. There is an eclipse birt 2.3.2 downloadable by now, perhaps you can try that one if you haven't.

KaiWeing

Me again,

I downloaded the newest BIRT RCP 2.3.2 (designer only installation) and it worked!

Ilya

Can you please post a screenshot of chart designer page?
Are you using aggregated table for that chart?

KaiWeing

Here it is.

I'm using the flat file from my upload earlier, so no aggregation. Are you using BIRT 2.3.2? It won't work with anything less as it seems.

Ilya

I'm using 2.1.2 and not really sure if i can use anything higher, since the installed viewer system is 2.1.2 =(

KaiWeing

Well, it could be just a problem with the preview. Have you tried to deploy the chart to see it in the real viewer?

And the alternative with aggregated values does not work as well?

Apart from that, I don't know any "workarounds" for older versions, sorry.

Kai

Ilya

Problem solved.
I decided to go the other way and made that all via created SQL View table

Thank you for help!
I really hope they will update birt server part soon...