Reference

Net.Data Report Variables

These variables help you customize your reports. Each variable has a default value. You can override the default value by assigning a new value to the variable.

• ALIGN
• DTW_DEFAULT_REPORT
• DTW_HTML_TABLE
• RPT_MAX_ROWS
• START_ROW_NUM

ALIGN

Purpose

Controls leading and trailing spaces used with the table processing variables NLIST and VLIST.

Performance Tip: Use ALIGN only when necessary as it requires that Net.Data determine the maximum column length for all columns in the table to calculate padding requirements. This process can impact performance.

When set to YES, ALIGN provides padding to align table processing variables for display. If you want to embed query results in HTML links or form actions, use the default value of NO to prevent Net.Data from surrounding report variables with leading and trailing spaces.

Specify the value of this variable using a DEFINE statement or with the @DTW_ASSIGN() function.

Values

ALIGN="YES"|"NO"

Table 2. ALIGN Values

Examples

Example 1: Using the ALIGN variable to separate each column by a space

%DEFINE ALIGN="YES"
<P>Your query was on these columns: $(NLIST)

DTW_DEFAULT_REPORT

Purpose

Determines whether Net.Data generates a default report for functions that have no REPORT block. When this variable is set to YES, Net.Data generates the default report. When set to NO, Net.Data suppresses default report generation. Suppressing the default report is useful, for example, if you receive the results of a function call in a table variable and want to pass the results to a different function to process.

Specify the value of this variable using a DEFINE statement or with the @DTW_ASSIGN() function.

Values

DTW_DEFAULT_REPORT="YES"|"NO"|"MULTIPLE"

Table 3. DTW_DEFAULT_REPORT Values

Examples

Example 1: Overriding the default report generated by Net.Data

%DEFINE DTW_DEFAULT_REPORT="NO"

DTW_HTML_TABLE

Purpose

Displays results in an HTML table instead of displaying the table in a text-type format (that is, using the TABLE tags rather than the PRE tags).

The generated TABLE tag includes a border and cell-padding specification:

<TABLE BORDER CELLPADDING=2>

Specify the value of this variable using a DEFINE statement or with the @DTW_ASSIGN() function.

Values

DTW_HTML_TABLE="YES"|"NO"

Table 4. DTW_HTML_TABLE Values

Examples

Example 1: Displays results from an SQL function with HTML tags

%DEFINE DTW_HTML_TABLE="YES"
 
%FUNCTION(DTW_SQL){
SELECT NAME, ADDRESS FROM $(qTable)
%}

RPT_MAX_ROWS

Purpose

Specifies the number of rows in a table that are processed in a function REPORT block or during the generation of a default report if a REPORT block is not specified.

The database language environments use this variable to limit the number of rows returned, which can subtantially improve performance for large result sets. Use this variable with START_ROW_NUM to break queries with large result sets into smaller tables, each on its own HTML page.

OS/400, Windows NT, OS/2, and UNIX users: To pass this variable to the language environment, include it as an IN parameter in the database language environment's ENVIRONMENT statement in the Net.Data initialization file. To learn more about the database language environment statement, see the configuration chapter of the Net.Data Administration and Programming Guide for your operating system.

OS/390 users: RPT_MAX_ROWS is implicitly passed to the database language environments when it is defined in the macro.

Specify the value of this variable using a DEFINE statement or with the @DTW_ASSIGN() function.

Values

RPT_MAX_ROWS="ALL"|"0"|"number"

Table 5. RPT_MAX_ROWS Values

Examples

Example 1: Defines RPT_MAX_ROWS in a DEFINE statement

%DEFINE RPT_MAX_ROWS="20"

The above method limits the number of rows any function returns to 20 rows.

Example 2: Uses HTML input to define the variable with an HTML form

Maximum rows to return (0 for no limit):
<INPUT TYPE="text" NAME="RPT_MAX_ROWS" SIZE=3>

The lines in the above example can be placed in a FORM tag to let the application users set the number of rows they want returned from a query.

START_ROW_NUM

Purpose

Specifies the starting row number in a table that will get processed in a function REPORT block or during the generation of a default report if a REPORT block is not specified.

The database language environments use this variable to determine the starting row in the result set to begin processing. To subtantially improve performance for large result sets, use this variable with RPT_MAX_ROWS to break queries with large result sets into smaller tables.

OS/400, Windows NT, OS/2, and UNIX users: To pass this variable to the language environment, include it as an IN parameter in the database language environment's ENVIRONMENT statement in the Net.Data initialization file. To learn more about the database language environment statement, see the configuration chapter of the Net.Data Administration and Programming Guide for your operating system.

OS/390 users: START_ROW_NUM is implicitly passed to the database language environments when it is defined in the macro.

Specify the value of this variable using a DEFINE statement or with the @DTW_ASSIGN() function.

Values

START_ROW_NUM="number"

Table 6. START_ROW_NUM Values

Examples

Example 1: Scrolling with HTML form Next and Previous buttons

%define {
  DTW_HTML_TABLE      = "YES"
  START_ROW_NUM       = "1"
  RPT_MAX_ROWS        = "10"
  totalSize           = ""
  includeNext         = "YES"
  includePrev         = "YES"
  includeLast         = "YES"
  includeFirst        = "YES"
%}
 
%function(DTW_SQL) myQuery(){
  select * from NETDATADEV.CUSTOMER
%}
 
%function(DTW_SQL) count(OUT size){
  select count(*) from NETDATADEV.CUSTOMER
  %report{
    %row{
      @DTW_ASSIGN(size,V1)
    %}
  %}
%}
 
%html(report) {
  %{ get the total number of records if we haven't already %}
  %if (totalSize == "")
    @count(totalSize)
  %endif
 
  %{ set START_ROW_NUM based on the button user clicked %}
  %if (totalSize <= RPT_MAX_ROWS)
    %{ there's only one page of data %}
    @DTW_ASSIGN(START_ROW_NUM, "1")
    @DTW_ASSIGN(includeFirst, "NO")
    @DTW_ASSIGN(includeLast, "NO")
    @DTW_ASSIGN(includeNext, "NO")
    @DTW_ASSIGN(includePrev, "NO")
  %elif (submit == "First Page" || submit == "")
    %{ first time through or user selected "First Page" button %}
    @DTW_ASSIGN(START_ROW_NUM, "1")
    @DTW_ASSIGN(includePrev, "NO")
    @DTW_ASSIGN(includeFirst, "NO")
  %elif (submit == "Last Page")
    %{ user selected "Last Page" button %}
    @DTW_SUBTRACT(totalSize, RPT_MAX_ROWS, START_ROW_NUM)
    @DTW_ADD(START_ROW_NUM, "1", START_ROW_NUM)
    @DTW_ASSIGN(includeLast, "NO")
    @DTW_ASSIGN(includeNext, "NO")
  %elif (submit == "Next")
    %{ user selected "Next" button %}
    @DTW_ADD(START_ROW_NUM, RPT_MAX_ROWS, START_ROW_NUM)
    %if (@DTW_rADD(START_ROW_NUM, RPT_MAX_ROWS) > totalSize)
      @DTW_ASSIGN(includeNext,"NO")
      @DTW_ASSIGN(includeLast, "NO")
    %endif
  %elif (submit == "Previous")
    %{ user selected "Previous" button %}
    @DTW_SUBTRACT(START_ROW_NUM, RPT_MAX_ROWS, START_ROW_NUM)
    %if (START_ROW_NUM <= "1" )
      @DTW_ASSIGN(START_ROW_NUM,"1")
      @DTW_ASSIGN(includePrev,"NO")
      @DTW_ASSIGN(includeFirst,"NO")
    %endif
  %endif
 
  %{ run the query to get the data %}
  @myQuery()
 
  %{ output the correct buttons at the bottom of the report %}
  <center>
  <form method="POST" action="report">
  <input name="START_ROW_NUM" type="hidden" value="$(START_ROW_NUM)">
  <input name="totalSize" type="hidden" value="$(totalSize)">
  %if (includeFirst == "YES" )
  <input name="submit" type="submit" value="First Page">
  %endif
  %if (includePrev == "YES" )
  <input name="submit" type="submit" value="Previous">
  %endif
  %if (includeNext == "YES" )
  <input name="submit" type="submit" value="Next">
  %endif
  %if (includeLast == "YES" )
  <input name="submit" type="submit" value="Last Page">
  %endif
  </form>
  </center>
%}