Count

Getting your data onto paper - or the web - Discussing the reports features of Base

Count

Postby jd2008 » Wed Jun 25, 2008 12:12 am

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
OOo 2.4.X on Ms Windows XP
jd2008
 
Posts: 2
Joined: Mon Jun 23, 2008 1:12 pm

Re: Count

Postby Villeroy » Wed Jun 25, 2008 1:51 am

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28440
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Count

Postby jd2008 » Wed Jun 25, 2008 10:24 am

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
OOo 2.4.X on Ms Windows XP
jd2008
 
Posts: 2
Joined: Mon Jun 23, 2008 1:12 pm

Re: Count

Postby Villeroy » Wed Jun 25, 2008 10:58 am

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 _.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28440
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Reporting

Who is online

Users browsing this forum: No registered users and 1 guest