Simple Inquiries Explained
5/26/2006 7:04 AM
Copyright 2006, R. James Holton. All rights reserved.
One of the benefits of an automated expense report process
is the wealth of information that can be mined from the database. You can create custom reports to improve your
processing, enforce your travel policy or just provide you with information for
decision making. While using a
full-blown report writer that you are familiar with is preferable, ESS does
come with a miniature report writing tool that you can use as an
alternative. It is the “Simple
Inquiries” option that is located in the Audit module.
Simple Inquiries are generated by combining paramenters passed into the ReportGenerator.jsp from reportGenerator.html with specifications
defined in the reports.xml file.
You can create custom reports by modifying the reports.xml file and/or by using an alternative to reportGenerator.html.
To create a Simple Inquiry, you need to modify the reports.xml file
that is located with other configuration files in the xmls folder. A Simple Inquiry consists of a uniquely named
element in reports.xml with the following subelements
defined:
- title – enter the title of the report. The title will appear in the report
selection dropdown and at the top of the report. Sometimes the title element will include a quick description of what the
date fields mean.
- sql – enter the SQL statement
that will be used to retrieve the basic rows and columns for the report. Any SQL statement supported by your
database can be used. There are
macros for beginning date and ending date, as well as, four general macros
that will be explained below.
- sqlcheck1plus
– A secondary SQL statement that must evaluate as true for the row to be
included on the report. Not required.
Information from result rows, columns one and two, returned by the
SQL statement, specified in the sql element, is passed into this statement. If blank, this check is not performed
and the condition is true.
- sqlcheck2plus
– see above.
- sqlcheck3plus
– see above.
- sqlcheck1minus
– A secondary SQL statement that must evaluate as true for the row to be
included on the report. Not required. Information from result rows,
columns one and two, returned by the SQL statement, specified in the sql element,
is passed into this statement. . If
blank, this check is not performed and the condition is true.
- sqlcheck2minus
– see above.
- sqlcheck3minus
– see above.
- encrypteditems – List of
items in the SQL statement that are scrambled with the key specified in
the SQL select of the system.xml file.
At most sites this will be blank.
- ignoredaterange – A “Yes”
ignores the date range passed into the ReportGenerator.jsp
from the HTML screen. A ‘No’ allows
the first column not to be the date that is checked to be between a range.
- Datetypeoverride – Allows a different date type, from
the one specified in the system.xml SQL
element, to be used to check the date range. Example, the register
table, store the REPDATE
in Web format. Valid entries are
‘YYYY-MM-DD’ (MySQL, MS SQLServer) ‘MM/DD/YYYY’ (xBase), ‘DD-MMM-YYYY’
(Oracle), and ‘WEB’ (Web format).
If left blank will use the format specified in system.xml which covers 99%
of the cases as that corresponds to the date format used by the database.
The following macros are available to the SQL statement
defined in the sql
element:
- $begdate$ - Used to prep the SQL with the date format
string specified in the sql element in the system.xml file. The date
format string generally carries the $date$ macro. Begin and end date integrity are
maintain with execution presidence.
- $enddate$ - Used to prep the SQL with the date format
string specified in the sql element in the system.xml file. The date
format string generally carries the $date$ macro. Begin and end date integrity are
maintain with execution presidence.
The following parameters are for informational purposes only
at this point:
- $date$
- Used internally. If used directly
by an inquiry will resolve as the ending date string.
- $begdatestr$ - beginning date in MM/DD/YYYY format.
(obsolete)
- $enddatestr$ - ending date in MM/DD/YYYY format.
(obsolete)
- $begdatesql$ - beginning date in YYYY-MM-DD format.
(obsolete)
- $enddatesql$ - ending date in YYYY-MM-DD format.
(obsolete)
- $field1$
- any value passed into ReportGenerator.jsp from the calling HTML. Not currently used with reportGenerator.html. (future)
- $field2$$
- any value passed into ReportGenerator.jsp from the calling HTML. Not currently used with reportGenerator.html. (future)
- $field3$$
- any value passed into ReportGenerator.jsp from the calling HTML. Not currently used with reportGenerator.html. (future)
- $field4$$
- any value passed into ReportGenerator.jsp from the calling HTML. Not currently used with reportGenerator.html. (future)
The following macros are available in the “sqlcheck-plus” and “sqlcheck-minus”
elements:
- $column1$
- value of the first column in current row the sql statement specified
result. In most reports, this will
be the date that is check for the range.
- $column2$
- value of the two column in current row the sql statement specified
result.
- $column3$
- value of the third column in current row the sql statement specified
result.
Normally the report.xml is located in the xmls folder. However, since it is specified by a sub-element
in the configuration element of the system.xml folder,
it can be moved to an area that is accessible to alternative personnel.
The default reports.xml comes with several example inquiries.
Alternatives to Simple Inquiries
Simple Inquiries are very rudimentary reporting tools. They do have the advantage in that they come
with some canned reports. However, any
reporting tool can really be used to extract information from your expense
report database.
For example, you can use MS Access for this task if you have
a Windows PC with Access installed.
Here’s how. It is extremely easy
to pull MySQL data into Access if you have the MySQL-ODBC driver in your
Windows’ Data Sources. Here is a link that will walk you through the
steps for getting the driver and using it:
http://www.itc.virginia.edu/desktop/web/mysql/msaccess.html
Like many other processes though, there are a couple
caveats. Here are some things to keep in mind:
- The
DSN you use with Access needs to be installed in the System DSN
area. If you install it in the User DSN area, you'll have
problems.
- Also
note the port that you are using for MySQL. It is part of the JDBC
string in the ConfigurationInfo.txt file (available in the Start menu under Expense Submittal System).
3306 is the default. If you use a different port number, you need to
enter that in the Port field of
the Connection Options tab when
you setup the DSN.
- The
login for the DSN can either be 'root' or 'adisoft'. The password, in both cases, is ‘adisoft’.
- It
might pay to set the DSN up ahead of time by going to Control Panel, Administrative
Tools, Data Sources. We found that the whole import process
seemed cleaner.
As we become familiar with more reporting tools, we be updating this section.
###