Page 1 of 1

Count

PostPosted: Wed Jun 25, 2008 12:12 am
by jd2008
I am using Base to connect to a MS Access database.

I have created a query in Base that includes 3 fields; date, Region, type.

I want to in Sun Report Builder, use a function (does one exist?) to report the number of occurances of certain word.
(This should be easier than setting up multiple queries and the count function [F_type_Nb] + 1 )
I have not found the correct function or my syntax is not correct.

For example the field type has 5 diffrent values PL, P, MB, ME, GE, MH for about 100 rows of data.
I would like to know how many times PL occurs for instance.

The sun report builder uses the pentaho reporting engine but does not list any functions for this
http://wiki.pentaho.com/display/Reporti ... xpressions
http://wiki.services.openoffice.org/wik ... /Functions

Thanks

John

Re: Count

PostPosted: Wed Jun 25, 2008 1:51 am
by Villeroy
Make a report based on this query:
Code: Select all   Expand viewCollapse view
SELECT "date", "Region", Count("type") AS "Count Types" FROM "Table" GROUP BY "date", "Region"

Adjust the table name.

Re: Count

PostPosted: Wed Jun 25, 2008 10:24 am
by jd2008
Thanks after a fiddle to make the date non visible I got what I wanted.
Because I am filtering by date can a popup box for entering a specific date be used in Base?
In MS Access if you add a field in the query eg DateAG: [DateAG], using a Parameter DateTime in the sql query, it allows you to enter a date.

Thanks

John

Re: Count

PostPosted: Wed Jun 25, 2008 10:58 am
by Villeroy
Based on my previous suggestion:
SELECT "date", "Region", Count("type") AS "Count Types" FROM "Table"
WHERE "date" BETWEEN :Start_Date AND :End_Date
GROUP BY "date", "Region"

If I remember correctly, BETWEEN includes both values (>=start AND <=end). Try out. You can also use something like WHERE ("date" >= :Start_Date)AND("date" > :End_Date) [exclude end].
Notice that the parameter names with preceeding colon are not double-quoted. They can not include spaces. Use simple alphanumeric chars and underscore _.