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

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

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

Mon, 04 February 2013 15:13

Eclipse User

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!

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

Eclipse User

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

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

Eclipse User

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] by 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

Eclipse User

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))

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

Eclipse User

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

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

Goto Forum:

-=] Back to Top [=-

Current Time: Sun Mar 16 05:45:48 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter