Customizing › Legacy Reports Customization › Custom Report Design › How to Create a Report Template › Example: Report Template

Example: Report Template

The following Affected Contact Report template shows how to create a report template. It produces a report that lists open change orders with the same affected contact:

PAGE HEADER {
                                                     As Of: [CD> 
                                                            [CT> 
} 
PAGE FOOTER { 
                         Page: [PG> 
} 
BLOCK chg (“SELECT \ 
		chg_ref_num, description, priority, \ 
		status, category, assignee \ 
      FROM Change_Request”, 
    “WHERE #Change_Request.status = 'OP' \ 
AND #Change_Request.requestor = #ca_contact.id \ 
AND #ca_contact.last_name = ? \ 
AND #ca_contact.first_name = ? \ 
AND #ca_contact.middle_name = ? “ , $1, $2, $3) 
{ 
	BLOCK st (“SELECT sym FROM Change_Status”, 
		    “WHERE code = ? “, chg::status) {} 
	BLOCK (strlen(category)) cat (“SELECT sym FROM Change_Category”, 
					“WHERE code = ? “, chg::category) {} 
HEADER { 
		OPEN CHANGE ORDERS WITH SAME REQUESTOR/FROM CONTACT 
CHANGE ORDER Summary          Pri   Status    Category           Assignee 
} 
HEADER2 { 
CHANGE ORDER Summary          Pri   Status    Category           Assignee 
-------------------------------------------------------------------------- 
} 
	num = chg_ref_num; 
	desc = description MULTILINE; 
	pr = deref (priority); 
	stat = st::sym; 
	catgry = cat::sym; 
	asgn = deref (assignee); 
PRINT { 
[num      ] [desc            ][pr ] [stat   ] [catgry          ] [asgn ] 
} 
} 

Page Header

Specifies what to print on the top of each page of the report. CD and CT are predefined variables that give the current date and time. They will appear in the header on the top of each page. Each of these fields ends with an angle bracket, which allows the field to expand towards the right margin. Because “As Of:” is outside of a field and because it is on a line after the opening brace, it will appear as literal text on the report output.

PAGE HEADER {                                 As Of:  [CD>
                                                      [CT>
}

Page Footer

Includes the page number with “Page: ” as literal text.

PAGE FOOTER {
                                Page: [PG>
}

Note: Since PAGE HEADER and PAGE FOOTER statements produce global headers and footers, they are not included in a BLOCK statement.

Reporting Section

Creates a reporting section for the main BLOCK statement, along with its nested statements. A reporting section is usually only part of the data in the report, but this report has only one reporting section. The unique name of this block is chg.

The SELECT clause selects the columns to be included in the data for the report FROM three tables, but only where conditions specified by the WHERE clause are met.

The last three AND expressions in the WHERE clause contain question marks, which act as argument placeholders that take the values of the $1, $2, and $3 arguments, in order. Thus $1 is for ca_contact.last_name, $2 is for ca_contact.first_name, and $3 is for ca_contact.middle_name. The $1, $2, and $3 arguments obtain the values of command line arguments.

BLOCK chg (“SELECT \ 
...”, 
“WHERE \ 
...\ 
AND #ca_contact.last_name = ? \ 
AND #ca_contact.first_name = ? \ 
AND #ca_contact.middle_name = ? “, $1, $2, $3)

Reporting Section Headers

Specifies that the opening brace starts the output program part of the BLOCK statement: its statements tell what to do with the data fetched by the SELECT and WHERE clauses. This example has nested HEADER and HEADER2 statements that will apply to this reporting section only. HEADER2 prints only if the report output is on multiple pages.

{
   ...
   HEADER {
              OPEN CHANGE ORDERS WITH SAME REQUESTOR/FROM CONTACT 
CHANGE ORDER Summary            Pri    Status  Category  Assignee
}
   HEADER2 {
CHANGE ORDER Summary            Pri    Status  Category  Assignee
--------------------------------------------------------------------
}

Variable Assignments

Specifies variable expressions that act on the data specified by the SELECT clauses. They assign variables to the values of columns and to the results of expressions. These variables match the fields in the PRINT statement that follows.

The MULTILINE flag on the desc variable causes them to print or display on multiple lines rather than being truncated. The deref function is used to return the string expression contained in the referenced columns.

num = chg_ref_num;
desc = description MULTILINE;
pr = deref (priority);
stat = st::sym;
catgry = cat::sym;
asgn = deref (assignee);

Printing

Contains the fields to be printed. This statement could have also included literal text of lines that could enhance the appearance of the report. The final ending brace matches the opening brace of the output program section of the BLOCK statement.

PRINT {
[num ] [desc         ] [pr]  [stat] [catgry] [asgn         ]
 
}
}

More information:

The Report Command