Transcript Extreme Reporting An ACEware webinar February 28, 2008 Agenda • • • • • • Justdoit Justafter Complex expressions & functions Variables & sub totals & totals..oh my Reports about Reports Output as PDF & HTML Justdoit A.

Extreme
Reporting
An ACEware webinar
February 28, 2008
Agenda
•
•
•
•
•
•
Justdoit
Justafter
Complex expressions & functions
Variables & sub totals & totals..oh my
Reports about Reports
Output as PDF & HTML
Justdoit
A JUSTDOIT is a specialized report function that uses SQL
to request and manipulate information from the data
tables.
• change the sort order of data in a report
• filter out unwanted data OR add an
additional data selection criteria
• execute specialized report functions
– gradspec
– gradcred
– Stampint
The JUSTDOIT executes the SQL commands
before the modify report template is displayed
Justdoit
Example...
Changes the order of the records in a cursor
Justdoit(“select *,0 as marker from (cursor5) order
by cobegdate into cursor (cursor5)”)
Parts of the
Justdoit
" ", ' ', [ ]
Delimiters used to enclose the Select statement and
other elements in the JUSTDOIT
Select
A standard starting “verb” for a SQL statement
* (asterisk)
“Tells” SQL to select ALL data from the table that follows
the FROM statement (see below).
0 as marker
Creates a field named Marker with a value of zero (0) in
the cursor – this “shuts off” further JDI actions. The
<value> as <FIELDNAME> can be used to add new data
fields to a report cursor – see next.
<value> As
<fieldname>
Create a new field in the cursor with either an explicit
value or a value from a function
Parts of the
Justdoit
From
(cursor5)
Tells SQL where to get the data that we are
manipulating with the JUSTDOIT. In most cases it will
be from Cursor5 (default cursor created when the report
opens).
Where
States a conditional clause that data must meet to be
included in the report (e.g. where due-paid > 0.00)
Group by
Groups data in the cursor by the field you specify, then
removes all but one record for each group (eliminates
duplicate rows in cursor)
Order by
Changes the sort order of data in the cursor
Into cursor
(cursorN)
Tells the system to place the data back into the cursor
once it has finished executing all the select statements,
making it available for the Report Print Preview
Justdoit
The JUSTDOIT expression
box may be placed
anywhere on the report
template but we
recommend placing it in the
header band of the report.
It's a non-printing element and
will not display on the report
itself.
Justdoit
Examples
Change the Sort Order of Report with "Order By" Statement
justdoit(“select *,0 as marker from (cursor5) order by nmname3,nmname1 into
cursor(cursor5)”)
Use comma to join multiple fields
Eliminating Duplicate records with the "Group By" Statement
JUSTDOIT(‘select *,0 as marker from (cursor5) group by nmid into
cursor(cursor5)’)
Filter the report with "Where" Statement
JUSTDOIT(‘select *,0 as marker from (cursor5) where due-paid>0.00 into
cursor(cursor5)’)
Add a field to the Cursor and then Sort by it
JUSTDOIT(‘select *,addcrse(cocrse,"cobegdate") as date, 0 as marker from
(cursor5) order by date into cursor(cursor5)’)
Justdoit
Tips and Notes….
• JUSTDOIT or justdoit..= Same same SAME
• Mind your “ and ‘ or [ ( bracket matching )
• When editing a JDI, you must save & close & rerun the report to see your results.
• Recovering from a Failed JDI – Tip: Recycle…
• You can “stack” JDI’s - ..Going where mere
mortals fear to tread……
• When in doubt.. Check with a professional!
Justdoit
Check the reference
guide for additional
(and helpful)
information on
Justdoit Errors
and using
Multiple Justdoits
Justdoit
Where can you
go from here??
Any book ( SQL for Dummies ) or web resource that helps you
understand SQL and SQL commands would help you write
JUSTDOITS…
( Google Learning SQL )
http://www.1keydata.com/sql/sql.html
http://www.webdevelopersnotes.com/tutorials/sql/index.php3
Justafter
Allows you to . . .
 stamp a value in a field
 extract fields to an external file
 or execute specialized report functions
AFTER the report runs.
Like the JUSTDOIT, the JUSTAFTER is a non-printing
element that we recommend placing in the page footer
or report summary band.
Justafter
Examples
Stamp the system date into Confirm field on the Registration screen. Use
it to log the date confirmation letters were sent to registrants.
JUSTAFTER('=CONFIRM()')
Export name and address information to a file on your hard drive (dBASE
III format).
JUSTAFTER('=NAMEEXP ("c:\myfile.dbf")')
Start the Mass Email Wizard after the report runs. This is most commonly
used to email everyone in a report (e.g. receipts, confirmation, second
notices, etc.).
JUSTAFTER('=DOEMAIL()')
Stamping Values
(Based on data
in a Report..)
Stamp Functions….
- Confcert
- Confirm
- Stampname
- Stampint
- etc.
NEW!
Report Function
Descriptions
Chuck’s Fab
Functions…
• COUNTIT – Can jump outside (your report) to count anything..
• TRIMDATE – Formats date.. To save space
• WHAZZIS - Stamps query used on the report..
• NAMER – name display
• SHOWPHON – format phone
• QUIKCODE - Shows discriptor of Code Field ie: nmorg
• QUIKINST – Displays first instructor assigned to course
• IIF(<cond>,<show if True>,<show if False>) – conditional display
• COUNTNAME/REG – from Course Level .. Get Name/Reg info
•
• If you want to be a better Report Master Chef.. Spend more time
exploring and studying the Function List…
Think of it as a
Data Bucket
• Variables are used to compare
and manipulate data for display in
a report
• Variables are not saved to the
database
• After you exit the report, the
variables are erased from memory
Variables
Variables
You can use variables to:
• Generate record counts in a report – Get Sub totals or Totals
(e.g. how many individuals took courses during a given semester).
• Generate numbered lists (e.g. 1. First person taking course, 2.
Second person, etc.).
• Calculate values based on report data (e.g. add the payment
amount to the total paid IF the pay type is Cash).
• Create shorthand representations of long expressions (e.g. variable
Due stores the value of the function gtdue(rgcrse,rgid)
• Calculate values then use those values to calculate subsequent
values (e.g. Due-Paid)
Variables
To create a variable:
Select Report | Variables
Name of the
variable
Variables
Field/Expression which should be
stored/calculated in this variable
Initial value of the variable BEFORE any
calculations are performed
Clears the report variable from memory
after the report is printed
Specifies the point at which the
variable is reset to its initial value
Variables
Specify a calculation that the
variable performs. The variable
begins calculating with its initial
value, and continues until it is reset
Reports
About Reports
Run from the Deadbeat area
Reports by Area/Frequency
Reports
About Reports
Run from the Deadbeat area
Reports by Frequency
Reports
About Reports
Run from the Deadbeat area
New / All Reports w/Memo
Output as PDF
1.
2.
Select the appropriate Report area (e.g. Reports | Registrations |
Rosters | Name Rosters).
On the Printing Options window in the Also Output as section,
select the desired output format
We STRONGLY
recommend you save
PDF or HTM to some
folder OTHER than
Manager
3.
Enter the path (location for the saved file) and name for the file.
Output as HTML
If you select the HTML format, the system will create an
HTML page of the report for web viewing. If the report is
more than one page, the system will also create navigation
icons for the web page.
Output as HTML
You must copy the HTML page AND all associated graphics
to your web page folder or users will not be able to move
from page to page
Summary
•
•
•
•
•
•
Justdoit
Justafter
Complex expressions & functions
Variables
Reports about Reports
Output as PDF & HTML
Questions???
Upcoming Webinars….
ACEweb Intro – Everything you wanted to know about Web Registration…
But Were afraid to ask…
March 6th
2 PM CST