AGENCY Software Wiki

Overview

Reports are accessed from the report page

• allows administrators to easily create reports that users can run
• Users can run reports
• Output to screen, spreadsheet or template
• Merges with OpenOffice template for fancy formatting
• [Permissions] control access to reports

Information for Report Writers

• Reports are stored in the database, in the report table
• New reports can be added from the reports page. Click the link at the bottom.

[FILE:report_page.png]

• Create your report by filling out the form

[FILE:report_edit.png]

Some of the report options require syntax that may not be immediately intuitive.  These are documented below:

Variables

• User selects variables at run-time
• Variables can be used in headers, footers and SQL
• Specify a variable with a $.

Example in SQL:

Order by $order

• You can get the label for a variable, by adding _label

Example in header:

Report sorted by $order_label

• Variables can be specified in the following format:

  Type Name Prompt [Default]
  (Default is optional)

• If your items have spaces in them, they should be enclosed in quotes:

  DATE mydate "Enter a date"

Variable Types

• • DATE
  • TIME
  • TIMESTAMP
  • TEXT
  • TEXT_AREA
  • VALUE
  • PICK

Using PICK Variables

• PICK is used to create a dropdown list.
• First line is like other variables (PICK varname prompt [default])
• It should be followed by lines specifying list items. Each line includes a value, followed by a label.
• A list ends with the word ENDPICK on a separate line.

Example:

PICK order "Sort report by"
added_by "Who added the record"
changed_by "Who last changed the record"
dob "Date of Birth"
ENDPICK

You can also populate your PICK list with an SQL query. Place SQL on one line, then your query on the next, and then ENDSQL on the next line. The query should return two fields--value and label.

Example:

PICK program "Report for which program"
"" "All Programs"
SQL
SELECT l_program_code AS value, description AS LABEL FROM l_agency_program
ENDSQL
ENDPICK

You could then use this in your SQL query like so:
SELECT ... FROM ...
WHERE agency_program_code=COALESCE('$program',program_code)

Reports and Report Blocks

Each report consists of a general report record, plus one or more report blocks, which are separately configurable and has its own SQL statement.

SQL Statement

• Each report has an SQL statement
• A report could be as simple as SELECT client_id, dob FROM client, (but can be considerably more complex!)
• Multiple SQL statements are allowed.
• Queries can create temporary tables.
• Currently, only the last query generates output.
• SQL can contain variables that are User-selected at run time.

Exanple:

SELECT staff_id,
       username,
       added_by,
       changed_by
FROM staff
ORDER BY $order

Fields hidden on screen

• Fields starting with _ will not be shown on the screen, but will be available

  for output merges.

• A common use is something like:

  SELECT client_id, client_name(client_id) AS _name FROM client...

The client_id displays on screen as a client link, but gets exported as an id. The _name field will export to a spreadsheet or other merge document

Output Options

Reports by default have options for

• screen output
• generic spreadsheet output

Output allows you to specify additional templates to merge with your report. These templates have been used to create:

• ID cards
• Donor profiles
• Phone lists
• Fancy progress note listings

Each additional template is specified on one line Specify a file name for the template, and a label for the user, separated by a | character:

sample_template.sxw|Choose this template
sample_template2.sxw|Here is another one

Permissions

• You can specify which permissions are needed to run a report
• Multiple permissions can be specified, separated by commas or spaces
• If the user has any one of the permissions, they can run the report.

[Category:User] [Category:Admin]