varlena ere: Design a Report




How It Works

Design Structure


Design a Report


Built-in Arguments


Using CSV files



Reports Home

The following technique is the best way to generate your reports. It is recommended that these simple guidelines be used at first. After you feel comfortable designing reports, you can tune the processes to what works best for you.

In order to write reports with ere you must be fairly competent at both SQL and HTML. Do a few small reports with minimal tricks so you can understand the basic workings of a ere report. Then, you can improve your layouts with more sophisticated techniques.

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.

      <DataStreamDef Name=deptemps dbname=:dbname: host=:host: port=:port: user=:user: >
            put your sql query here.
            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.
            A subset, in order, of your group by
            list.  Each element will have it's
            own area.

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>
      		<td colspan=3> <HR> </td>
   	<area name=emp_area datastream=emps break=emp_id>
      		<td><B> _emp_id_ </B> </td>
      		<td><B> _emp_name_ </B> </td>
      		<td><B> _emp_email_ </B> </td>

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: 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.

Copyright 2003 through 2009 A. Elein Mustain, Varlena, LLC