Eclipse Community Forums: BIRT » How do I do a count on a list of items?

Help

Home

Home » Eclipse Projects » BIRT » How do I do a count on a list of items?

Show: Today's Messages :: Show Polls :: Message Navigator

How do I do a count on a list of items? [message #1007184]

Mon, 04 February 2013 15:13

Missing name Mising name is currently offline

Missing name Mising name
Messages: 2
Registered: November 2010

Junior Member

Hi, I'm pretty new to BIRTH. I was able to create a report that has a list of names from a users table in my database. The table name is "User" and the column name is "Name". What I want to do now is to have a list of the number of users that begin with each letter of the alphabet. So My list would look something like this:

Letter    Count
A         10
B         12
etc

How would I go about doing this?

Thanks!

Report message to a moderator

Re: How do I do a count on a list of items? [message #1007206 is a reply to message #1007184]

Mon, 04 February 2013 19:08

Brad M
Messages: 20
Registered: September 2009

Junior Member

In the footer of your main table you could add 26 aggregations, one for each letter. Each operation would be COUNT and each would have Filter Expression of row["name"].charAt(0).toUpperCase() == "A", for example.
Niftier than that would probably require scripting and then you have to weigh up which one the person coming after you is likely to understand better

Brad

Report message to a moderator

Re: How do I do a count on a list of items? [message #1007325 is a reply to message #1007206]

Tue, 05 February 2013 09:37

Missing name Mising name
Messages: 2
Registered: November 2010

Junior Member

Ok thanks! I will try this out.

The other thing I though of was to use SQL and create a view in my database with the aggregations, then use that view as my dataset in the report.

[Updated on: Tue, 05 February 2013 09:38]

Report message to a moderator

Re: How do I do a count on a list of items? [message #1007355 is a reply to message #1007325]

Tue, 05 February 2013 10:39

Tomas Greif
Messages: 52
Registered: September 2010

Member

I think using SQL is much better approach. You do not have to create view, you can write sql directly when defining datasets, like:

select
 left(upper(name)) as first_letter,
 count(*) as users
from 
 users
group by
 left(upper(name))

Report message to a moderator

Re: How do I do a count on a list of items? [message #1007471 is a reply to message #1007355]

Tue, 05 February 2013 22:18

Brad M
Messages: 20
Registered: September 2009

Junior Member

SQL is a great idea. I've only ever used BIRT for XML datasets

Report message to a moderator

Previous Topic:	BIRT-Viewer DB connection error
Next Topic:	New IQuery instead of org.eclipse.datatools.connectivity.oda.IQuery

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 24 07:15:35 EDT 2013