1. Draw a picture on paper of what you
want the report to look like. Include
all elements of the report. Title the
report accurately, for example
"Employees by Department"
- Don't skip this step.
- Don't worry about how to gather the data with a query.
2. Looking at the picture, identify the
grouping--usually the things on either
side of the "by" phrase in your title.
Start from the the farthest right noun.
For example the report title "Project by Employee, within Departments"
would have the grouping objects: Departments, Employees, Projects.
Draw boxes around each group area. They should
probably be nested boxes. For example the
box containing department information encloses
the box containing employee info. (The
Structure chapter shows
a set of nested boxes for another report.)
Write the list of grouping objects within square brackets. For example:
[Department, [Employees,[Project]]]
3. Looking again at the report layout, identify all
other information on the report that is not contained in a
grouping object. Include report date and other decoration that
is not part of the HTML.
These will be the SQL target list.
4. Have a copy of your database schema or data model.
Using the database schema, match the grouping objects to the
appropriate table. Note which table contains the grouping fields.
Some items will come from tables not in your grouping tables.
All of these tables will be the from statement in the SQL.
5.
Write a query to produce the table attributes that
provide you with the content that you want.
(The hard part...) Note that you may write two
queries if necessary, for example to show both detail and summary data.
This is explained in detail in Tricks.
If there are aggregrate functions in the report, such as averaging,
summing etc, first develop a query that provides the data without the
aggregate function and add it in later when the base query works. This query
should contain all the fields necessary for the report.
Sort the data according to its natural hierarchy.
For example, the department is sorted before the
employee. Your schema relations should guide you.
The sort list, your SQL order by clause
should be an ordered superset of your grouping list
from step #2. The order by must be in the same
order as the grouping list.
SELECT target list
FROM table(s)
WHERE joining criteria and other criteria
ORDER BY subset of target list;
Test it and run it in psql. You may want to add
a temporary qualification to the SQL in order to
run it quickly, but you don't want to over qualify it
so the data you really want is not available.
6. When you are satisfied with your query,
add the aggregation and a group by clause
for SQL if you had one. The SQL group by clause should be
the list you made from earlier followed by anything
else in your target list.
Test and run your SQL in psql. You may get unexpected
results. Check your order by and group by clauses.
Ask questions and refer to your schema.
7. Now you are ready to build your report.
Start off with a report.xml file.
Use the Example reports
or the layout from Tags
for a template. Start to create the XML file by
filling out the details in the
<ReportDefinition> section.
Each SQL statement usually has
its own <DataStreamDef> area.
The target list of the SQL statement is the list
that you will put into the <TargetList>
section. In the <BreakList> section,
usually you will simply
use your sort list or an ordered subset of it.
The header should look
like this so far, with the descriptions replaced by
the actual stuff and the names set for the report and
datastream and the optional CSV file.
<ReportDefinition>
<ReportName>empsbydept</ReportName>
<DataStreamDef Name=deptemps dbname=:dbname: host=:host: port=:port: user=:user: >
<SQL>
put your sql query here.
</SQL>
<TargetList>
list all of the elements in the
target list in your query. These
should match up to your group by list
plus the other objects.
</TargetList>
<BreakList>
A subset, in order, of your group by
list. Each element will have it's
own area.
</BreakList>
</DataStreamDef>
</ReportDefinition>
8 If you want dynamic reports you can choose
to embed variables Variables
in your SQL or pass in other data for display.
9. Now go back to your
picture. You still have it, right?
You are going to want to have an area or table for
each boxed area and those areas correspond to
your break list.
Don't get too fancy, yet. Test out a
simple version to ensure your query, grouping and
data is what you expect. Then you can experiment
with formatting the data, e.g making dates, etc. look
nice with SQL formatting functions).
A simple example:
<table name=dept datastream=deptemps break=dept_id>
<tr> <td colspan=3> <HR> </td> </tr>
<tr> <td colspan=3> _depttitle_ </td></tr>
<tr> <td colspan=3> <HR> </td> </tr>
<tr>
<th>Id</th>
<th>Name</th>
<th>Email</th>
</tr>
<tr>
<td colspan=3> <HR> </td>
</tr>
<area name=emp_area datastream=emps break=emp_id>
<tr>
<td><B> _emp_id_ </B> </td>
<td><B> _emp_name_ </B> </td>
<td><B> _emp_email_ </B> </td>
</tr>
</area>
</table>
The table area is the department area. A horizontal
rule followed by the department title followed by a
horizontal rule and the column headings are in the
top of that area. This area will be repeated for
each employee.
To repeat a row, enclose it in an area. The
emp_area allows the detail row to be repeated for
each employee in the route.
If you wanted to get fancy, you can repeat sets of
columns and use the maxrepeat option. To get even
fancier you can create columns of tables.
10. Where you want the column values to appear,
use the _columnname_ syntax.
11.
Test the report using:
ere.pl report_name debug=2 dbname=database port=12345 user=$USER
If you have variables, pass them on the command line
as name=value. In this example, database name, port and user are being passed
in as variables. To show the values of variables, enclose them in colons, for example:
Database Name :dbname:
12.You are now ready for
Debugging. When it is
all working well, consider adding the
ability generate csv files
as well. The CSV feature has been deprecated
13. Ask many questions.
|
