Count

[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

[Villeroy]

Make a report based on this query:

Code: Select all

SELECT "date", "Region", Count("type") AS "Count Types" FROM "Table" GROUP BY "date", "Region"

Adjust the table name.

[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

[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 _.