Once a user has been given rights to modify Elliott Report Desk User Design Reports (UDR), they will have access to two additional buttons on the report parameter screens.

SQL: This button will display a screen with the SQL statement that will be used to retrieve data using the currently entered parameters:

Users can temporarily modify the SQL statement and test it with the Test SQL button.

Test SQL: This button will test the SQL statement shown. A window with result information will be shown after the statement is executed.

Copy to Clipboard:  This button will copy the SQL statement to the clipboard for use outside of Elliott. 

Exit: This button will exit the screen.

Modify: This button will take the user to the User Defined Report Designer screen.

There are two pieces of information that are required to create a UDR:

1.       A template. The template defines the primary table(s) for the report, the basic SELECT statement for the report, any predefined SQL formulas that can be used, etc.  The primary purpose of the template is to define the join relation of multiple tables which is critical for good performance. For this reason, only Netcellent or your developer can define a new template. A template can be used by multiple reports that can share the same basic SELECT statement. A template can have more than one variation -- joining different tables, for example. A template consists of a name and a sequence number, like APVENLST.1 or APVENLST.2.

2.       A report definition. The report definition defines the parameter input criteria for the report, the columns in the report and, if allowed by its template, an ORDER BY clause for the report. The report definition may also define group headers and footers, a report footer and any subreports. The format of the report name is defined at the end of this document.

Modifying the Report Definition

From the User Defined Report Designer screen, users can create their own version of the report with the information that is most important to them.

Title: This is the title shown on the report when it is rendered.

Where: The columns specified in this section are used to generate the criteria on the report parameter screen. They are also used to generate the where statement used to retrieve the data.

• To add a Column, drag a table column from the Available Columns TreeView below.
  
• To delete a Column, right click on the row and select Delete Row.
  
• Rearrange the order of the Columns by dragging within the grid. Note that the order of these WHERE clauses can affect performance.
  
• To change the Operator, click on the value and use the drop down to select an operator. This is a list of SQL-supported operators for WHERE clauses.
  
• To change the Prompt, click on the value and type a new value.
  
• To change the Type, click on the value and use the drop down to select a new value. In addition to String, Date and Number, there are some special types, like CusNo. These special types provide additional functionality, like right-justify and zero fill if numeric. 
  

Order By / Grouping: This specifies the sort order for the report and specifies any desired groups. If no order by is specified, the report will print in order of the primary key.

• Add a Column by dragging a table column from the Available Columns TreeView below.
  
• Delete a Column by right-clicking on the row and selecting Delete Row.
  
• Rearrange the order of the Columns by dragging within the grid.
  
• Change the ORDER BY Sequence (ASC or DESC) by using the drop down list.
  
• Add a group number if desired.  This will generate a GrpHdr and a GrpFtr tab in the area where the Body tab is.  You may specify multiple lines in any group header or footer.

Available Columns: The Available Columns TreeView is populated by all the tables specified in the SELECT statement of the template along with [CheckBox], [Constant], [Date], [System], [Formula] and [Text} nodes. Expand a node to see what columns are available. You can drag an entry from Available Columns to the Report Columns and change the column heading. You can also drag an entry to the Where grid (for specifying report input parameter criteria).

Report Columns: Next to the available columns TreeView is a collection of lines for the selected kind of report section selected (Body, GrpHdr, etc.  Each row in each line represents a column shown on the report when it is rendered.

For customers, this information is read-only, because only Netcellent or your developer can change this information.

Show SQL: This button will show an approximation of the SQL statement that is used to retrieve the data including the where clause created by the fields included in the Where grid.

Test: This option will allow the user to test the report changes without saving the design.

Save and Save New: These buttons will save the current report design or create a new one, respectively.  If you press Save on a report with a non-zero revision number, it will save your changes keeping the current revision number.  If you press Save New, it will create a new report with an incremented revision number.

After the design is saved, the report will re-display, possibly with an incremented revision number. 

For example, if you change the design for ARSHPLST.B.1.2.0, it will be saved as ARSHPLST.B.1.2.1. Revision number 0 specifies the standard report definition provided by Netcellent or your developer when Elliott is installed or updated. Non-zero revision numbers represent custom versions created by users. 

Import can be used if Netcellent or a developer sends you a changed report for your use.

Export is reserved for Netcellent and developers.

Delete: This option deletes user defined reports. Base reports provided by Netcellent cannot be deleted.

Exit: Choose this option to exit the User Defined Report Designer screen.

After exiting the screen, the new custom report definition will be available.

Additional Information on Report Name:

For each UDR report , it has a unique name with following format: NAME.X.1.2.3 where:

• NAME is the name of your UDR report, in this case APVENLST.
• X can have the value of B (Base) or E (Enhancement). Base means this report was originally created by Netcellent, and Enhancement means this report was created by your developer.
• 1 - the first numeric digit represents the template ID. A template usually represents a unique way of joining the tables. As an end user, you can't create your own template because the joining must be done by Netcellent or your developer to ensure best database performance.
• 2 - the second numeric digit represents the different types of report options derived from the same template. This can be different sorting sequence, different input selection options, or different columns to be included on the report.
• 3 - the third digit is the variation of each type of report. The value zero means this is the original report developed by Netcellent or your developers. Other values (greater than zero) are revisions of the report made by you. You can, for example, change to different sorting, selection and columns options and save your own version of the report.   Netcellent or your developer may change the reports that end with zero in the future, but your derived reports that do not end with the zero will never be overridden. 

CLS/JEG