[Odb-help] Interfacing ODB to a Report Engine

[odb-help at lists.democracygroups.org]

Dear ODB Users:

  We have just completed some work on a new, free software
program that can be used to set up ODB reports using an
external report writer -- crystal reports!

  We are looking for some more beta testers.

  You can integrate this with version 0.9.6 of ODB by creating
creating desktop shortcuts and by having these shortcuts
launch the rptview application.

  The next (1.0) major version of ODB will provide an intuitive
interface for setting up these shortcuts internally to ODB.

  Please note:  We are only supporting the widely distributed
version of crystal (version 4.6).  This piece of software is very
basic but it does what most ODB users need.  Version 11 of
Crystal would be overkill, and if you want more sophistication,
it looks like there will soon be open source, free report writers
that will serve nonprofits well.  We are distributing the source
code as well as the Crystal reports runtime (OCX) file.

  Below is the documentation for this connector, which is
actually able to display (on the screen as well as on the
printer) reports for Access database or even remote MySQL
databases.

  Please email me (rc2005.... at   ....   organizenow.net) if
you would like to beta test.  Thanks!

-rich




RPTview.exe: a Windows Commandline Application for
displaying MSAccess or MySQL databases

What it does:  Reads command line parameters to create
an ODBC connection to an Access or MySQL database, and then
show that database using a Crystal Report that was created
for that database using CR version 4.6.

USAGE: rptview.exe [rptpath], [rpttitle], [logininfo],
[startdate], [enddate], [datefield], [formula], [appquery]


Example:

rptview 'c:\my documents\reports\hidonors.rpt', 50 Largest Donors Last Year, embed, 1/1/04, 12/31/04

This will take a report listing donors and make it more specific by
only showing the donors who gave last year.  You can run even run
this outside of ODB (read only) by setting up a shortcut, where
the "target" is just "rptview.exe" followed by the rest of the command
line.   So if you want Sue to run a report, you can do so without
giving her access to ODB.  Note that rptview will figure out the location
of your database automatically by reading it from the .rpt file.



Explanation of parameters:

rptpath - the Windows long filename where the crystal reports
*.rpt file is located.  In this version you may not use a
relative path; you need the entire thing (i.e. starting with c:\ 
)

rpttitle - text you want to appear in the titlebar of the Crystal
Report Previewer, overrides what was there

logininfo - if the word "embed" is passed into this, RptView will
try to extract the location of the access database associated
with the report from the *.rpt file.  And ODB will create a new
ODBC connection "on the fly" and use that.  Otherwise RptView
will assume the logininfo is an ODB connection string, usually
the driver, then the uid, then the password.  i.e. "driver=sql
server;uid=joe;pwd=mypass;"

startdate - a date in mm/dd/yyyy format to limit the query
results

enddate - a date in mm/dd/yyyy format as the upper bound of the
query results

datefield - the tablename and fieldname to be used to limit the
query results.  Obviously, this must be a field included the
crystal reports query.  I.e. "donations.date"

formula - a formula that works with crystal reports, prefixed by
the character "A" or "O" or "R" A  --> logical "and" the formula
with other conditions, such as a date range. O  --> logical "or"
the formula with other conditions, such as a date range. R  -->
replace any existing logic with this formula. here is an example
of a crystal formula syntax: {table.stringfield} > '02111' and
{table.stringfield} < '02199' and {table.datefield} >
date(2002,12,1)

appquery - name of file generated by database application, which
is saved in the temp folder for the currently logged in user. 
Probably we will support the format used in the lastquery.txt
file that ODB generates.  This file has 3 lines.  The sort order
is specified in the first line.  The table is specified in the
second line.  The where clause is specified in the third line. 
It is possible we will change the format for this based on
feedback, before the final ODB 1.0 implementation is done.