Overview

With proper licensing, Elliott developers can follow these three steps to create a new UDR:

1.       Modify the program that calls the screen input program for an existing report to do a drill down to that program instead of a CALL.

2.       Add information to EL850S.Cfg that assigns the called screen program to a new UDR (User Defined Report.)

3.       Develop one or more new UDRs to replace the existing report.

Licensing

You must have a special license from Netcellent to create a new User Defined Report (UDR) yourself. But even if you do not have the special license, you still can use an existing UDR report as a basis to create a new report to fit your purpose. This article is about how to create a new User Defined Report from the developer point of view. If you have the proper license from Netcellent, before you can develop, you will need to create the String Value ELIRD in following registry key path: \\HKEY_CURRENT_USER\Software\Netcellent\Elliott\8.0\Report Desk\Debug.  See sample screen below:

Contact NETcellent for the possible values.  The purpose of this key is to identify the user as a licensed developer.

The following details assume the user will be replacing the Numeric Vendor List in AP100 with a UDR.

Modify the Calling Program

Instead of using the "CALL" convention of the screen program, you need to change it to use the drill down API call.  

Follow this link to the article on how the drill down API works:

    http://support.elliott.com/knowledgebase/articles/1854790-dd-api-changes-v9-0

Take the following example, in A/R -> Maintenance -> Load A/R Open Item -> List. Let's say we want to change the List to allow User Defined Reports. We need to first change AROLAD.CBL which is the "Load A/R Open item" program that we call "List."  We need to convert the following lines of code:

        MOVE "O" TO PASSED-BYTE
        CALL "AROSCRN" USING PASSED-BYTE,
        CANCEL "AROSCRN"

With the normal drill down API, you would use something like

      MOVE "DD,AR,MNT,03,AROSCRN,N" TO SCREEN-PARAMETERS  

But there is no need to specify the "MNT" and "03" portion, so just leave them as blank.  So your final code would look like:

MOVE "O" TO SCREEN-MENU      
MOVE "Legacy Open Item Edit List" TO SCREEN-RD-DESCRIPTION
MOVE "DD,AR, , ,AROSCRN,N" TO SCREEN-PARAMETERS      
PERFORM SCREEN-ROUTINE

Note: If you need to pass data to the called program, move it to SCREEN-MENU. SCREEN-RD-DESCRIPTION is the description that is shown in the Elliott Report Selection screen for the legacy report.

Now compile and install the program.

Modify EL850S.Cfg

In the [Menu-Override] section, add a line like the following:

AROSCRN=AROSCRN,AROPNLST

This line instructs Elliott, when it encounters a drill down API call "DD" on Program ID AROSCRN, to present two possibilities: the original AROSCRN and a new report ID called AROPNLST.  When choosing a new program name, follow this convention:

IDXXXXXX, where

ID=Two digits module ID like GL, AR, AP...etc.

XXXXXX=The rest of the report ID. You are free to name this part as long as it does not duplicate with existing Elliott program ID or new user defined report ID.

            ID = Two Digits Module ID like GL, AR, AP...etc.

            XXXXXX = The rest of the report name. You are

In the [Menu-Override-Description} section, add a line to indicate which report is a UDR report like the following:

AROPNLST=UDR

This line indicates that there may be one or more UDRs for listing vendors.  In particular, “=UDR” is what triggers the software to look for UDRs in the database to add to the list. So this line, in this format, is required.

Specifying a description for the legacy report will override the description passed in the drill down (DD API) call in the application code.

AROSCRN=Legacy Open Item Edit List  

UDR Basics

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

1.       A template.  The template defines the primary table for the report, the basic SELECT statement for the report and, optionally, any predefined SQL formulas that can be used.  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.  A report consists of a template name, template sequence number and a report sequence number, like APVENLST.1.1 or APVENLST.2.1.

So the process of creating a new UDR is to create a new template and then a new report that uses the template.

Creating a New UDR

First, your Elliott UserID needs the ability to modify UDRs.  Either run as SUPERVISOR, or go to Utilities, Password Setup, Global Security, User Global Security, and page down to the sixth screen and set item "16. Allow to Modify User Defined TRW Reports" to “Y”:

At this moment, if you start up Elliott and go to AR -> Maintenance -> A/R Open Item File -> List, the system will display the following two entries:

For AROPNLST, since this is a new UDR that has not been defined yet, the description shows "Define new report."  

Select the line with the UDR name you specified in EL800S.Cfg (AROPNLST in this example) and press OK.  When you do that, three windows will appear on top of each other.  The top one allows you to create the first template:

Change the Description from "New Template" to something like “A/R Open Item Edit List” as in this example. 

Click on the Primary Table drop down ComboBox. You will see a list of tables that exist in Elliott.  You could select multiple tables and join the name.  But you need to specify the first table to which other tables will be joined.  In this example, we know we want to print "A/R Open Item Edlit List" and the primary table is "AROPNFIL."  When you tab off that ComboBox, the minimum SELECT clause will appear.  

You may modify the contents of the SELECT clause to suit your needs, like joining additional tables if necessary.  You may include an ORDER BY clause and you may check the Prevent designer from changing the ORDER BY clause if you wish.  If you do not specify the ORDER BY, the reports may be printed by the primary key sequence of the table, or they may be based on the index that appears in the WHERE clause which influences how the records are retrieved from the table. Note that “*” is required in the SELECT clause here.  It does not means all the columns of the table will be returned, which is not efficient.  The actual list of columns that are selected is determined by the columns in the final report definition, not by the template.  In this case, we know it is natural to print customer information with A/R open item edit list.  So we should join the ARCUSFIL table.  See the following example:

If you want to test to see if this SQL statement will work, you will convert them to:

    SELECT * FROM AROPNFIL, ARCUSFIL
     WHERE AR_OPN_CUS_NO = CUS_NO

and test it in the PSQL Control Center.  All strings in side square brackets [ ] will be replaced based on the final report definition.  

You can also define a formula like "CustomerCityStateZip."  It has a value taken from a complex SQL column definition using several CONCAT functions to create a single-line City, State and Zip column. Normally, you will test the formula in the PSQL Control Center to make sure it works. For example:

SELECT AR_OPN_CUS_NO, AR_OPN_DOC_DT, AR_OPN_DOC_NO, AR_OPN_DOC_TP,
AR_OPN_APPLY_TO, CUS_NAME, CONCAT(CONCAT(CONCAT(CONCAT(RTRIM(CUS_CITY), ', '),CUS_ST),' '),CUS_ZIP)
FROM AROPNFIL, ARCUSFIL
WHERE AR_OPN_CUS_NO = CUS_NO;

The highlighted area is the formula being tested that combines city, state and zip in the proper format. Copy the formula portion from the SQL command and paste here. See sample screen below:

The purpose of pre-designing a formula is to make it easier for the users since they may not know how to make a formula themselves.

Finally, press the Save button, then the Exit button, to complete the template and go to the report design form.  The other buttons are grayed out during the initial template definition phase.

Note: If you make changes and press Exit before pressing Save, your changes will be lost.  (This will change in the future.)

Note: We have decided to not support the DDF-defined Views.  To us, the views defined in the DDFs are tables pre-joined together. Then you use the “select … from ..view where…”.  But we do not know how the PSQL engine optimizes the select statement.  Does it do the where first before doing the join in the view, or does it do the join before where?  Sometime we see a select statement like that for big table views that takes forever to execute. Our conclusion is that PSQL, in many cases, does the join condition first before applying the where condition.

We don’t have a lot of confidence that PSQL will be able to optimize join all the time. So select from views with a where condition will give PSQL a challenge to optimize. If we let our developer do the join in the template, we should be able to influence PSQL and optimize it instead of letting PSQL figure out how to optimize it on its own.

The User Defined Report Designer will look something like this:

The Report ComboBox and the Title field will be defaulted from the template form.  An initial Where entry is supplied using the first field in the primary table.  If an ORDER BY clause was entered in the template form, it will create an entry in the Order By grid.

The Available Columns TreeView is populated by all the tables specified in the SELECT statement of the template along with [Date], [System] and [Formula] 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.  Most likely the Column Heading will not be a friendly name to the user so it is your job to give a proper column heading.  You can also drag an entry to the Where grid (for specifying report input parameter criteria).  See sample screen below:

Use the Order By grid to change the osrt order of the report (unless prohibited in the template) and the Report Columns grid (to add a column to the report).

When you have made all your changes, press the Save button, then the Exit button to go to the report form:

Enter any desired parameters and press the OK button to run the report.  The first time you run a report, it will default to the Screen output option.

Now you can go back to the Report Designer and Template Designer forms to improve the report until it is done.

Insert Grouping

You also can group the information in your reports by the columns you are sorting on.  See the following KB article about Groups:

    http://support.elliott.com/knowledgebase/articles/1916812-report-desk-groups

Insert Footing

To add totals to your report, you need to use a Footer.  See the following KB article for details.

     http://support.elliott.com/knowledgebase/articles/1909558-feature-report-desk-report-footer-lines  

A More Comprehensive Example

Go to Accounts Receivable, Maintenance, Maintain Customers and select Numeric List:

Select ARCUSLST.1.1:

Press the Modify… button in the lower, right corner.  This brings up the report designer screen:

Press the Template… button on the bottom.  This brings up the Template form:

Notice that the SELECT: field joins two more tables and has an ORDER BY clause.  Also notice that in place of “WHERE” and “AND” there are two “[Where]” words.  “[Where]” is specified here because, after the end user fills out the report parameters screen, the “[UserWhere]” and [“Where]” words will be replaced by “WHERE” and “AND,” depending on which parameters are skipped or specified.

Modifying the Where Grid

·         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 ARCUSFIL.CUS_NO.  These special types provide additional functionality, like right-justify and zero fill if numeric.  These types are defined in the SYRPTTYP table in the root directory. (For now, if you need to add a type, see Jim.)

·         To change other information associated with this column during input parameter processing, click on the Info icon:

Additional Properties

• Allow Blank specifies whether or not an input parameter can be blank. Values are Yes, No, Y=All and Y=AllOrBlank. The default is No. When Y=All is specified, the input parameter field will be changed to “All” if left blank.  When Y=AllOrBlank is specified, a checkbox will appear to the right of the input field where the user can specify that a blank value will be used as an actual blank (instead of "All").
  
• Case and Default Value can be specified here. 
  
• LookupTable is not working at this time. 
  
• LookupList can be a comma-separated list of values like this: "A=Code A, B=Code B".  
  
• Range is not working at this time.
  

Modifying the Order By Grid

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

Modifying the Report Columns Grid

·         Add a Column by dragging a table, date, system or formula column from the Available Columns TreeView on the left.

·         Delete a Column by right-clicking on the row and selecting Delete Row.

·         Rearrange the order of the Columns by dragging within the grid.

·         The Column Heading will default to the most popular heading for the column.  You can use the drop down list to select a different one, or enter an entirely new one.  The headings in the list appear in the order of most-to-least popular from top to bottom.  Each time someone saves a design, the changed column values will be added to the list of popular headings and the popularity usage increased.

·         Length defaults to the database width when a column is first dragged to the grid.  You may change the length depending on how much room you want the column to take on the report.

·         The Format value should be left blank for strings.  You may choose any of the drop-down formats -- they are the formats compatible with the column type.  A complete list of display formats and their descriptions can be found here.

These are a subset of the Microsoft .NET standard format strings that are described here:

Click on the Info column icon, if present, to view or modify the associated formula:

Other Fields

·         The Public checkbox specifies whether or not everyone can run this report.

·         If Public is not checked, only SUPERVISOR, the creator of the report, and Elliott UserIDs listed in Target Users will be able to run the report.  This list must be comma separated.

·         The Orientation ComboBox specifies the orientation of the report as Automatic ,Portrait or Landscape.   If Automatic is specified, the orientation will be Portrait unless the number of columns exceeds 132.

Button Actions

·         Press the Save button to save any changes you have made.  Note: If you make any changes and press Exit before Save, you changes will be lost.  (This will be changed soon.)

·         Press the Save New button to create a new report based on the current report.  The new report will show up immediately on the Menu Override dialog.

·         Press the Delete button to delete the current design.

·         Press Exit to return to the Parameter Input form.

·         Press Template… to view or change the template for this UDR.

·         Press Show SQL to see the SQL (with variables as placeholders) that could be generated.  Note that the SQL button on the Input Parameters form does the same thing, but uses the input parameter values instead of variable placeholders.

·         Press Test… to test the current report design without saving it.  This starts a separate process to specify parameter input and run the report. 

Font Size Calculation Algorithm

The algorithm for picking the best-fit font size is as follows:

1. Get the first 600 records using the SELECT statement for sample data (about 20 pages).
2. For each column in the report, add its size plus 1 to a total number of characters.
3. For each column less than 9 characters wide, add 1 more to the total number of characters.
4. Divide the total width of the report line by the total number of characters.  That is the average width of a character.  The width of each field will be the its number of characters plus 1 or 2, times this average width.
5. Starting with a point size specified in EL850.Cfg, measure each column in each row of the sample records.
6. If the specified percent (in EL850.Cfg) of the columns fit in their designated space, select that point size; if not, decrement the point size by 1.0 and try again, down to a specified minimum font size (in EL850.Cfg).
7. Add 1.0 to the selected point size and go through steps 5 and 6 again, decrementing by 0.2 this time, until the specified percent of the columns fit in their designated space.  That is the final point size for the font.

Iterative Report Layout Development

The first time you create a report, it is unlikely that you will get its appearance the way you ultimately would like  it to be.  Most of the time, you will need to run the report a few times, tweaking column headings, sizes and formats.  You can use the Test button to run a test without saving the report, or you can save the report, exit to the Input Parameter form and run it from there.

Here are some possible issues and solutions:

• A column heading is a SQL column name -- change the column heading to an English term.
• A column heading takes more than two lines -- shorten the heading or make the column wider.
• A document number has comma separators -- change the format from "N0" to "D0."
• A date column appears as a number -- change the format to "Date-6" or "Date-8."
• Data in a column often takes more than one line -- make the column wider.
• Data in many columns often takes more than one line -- eliminate some columns from the report.

Internal Standards

• Dollar amounts should use format "C" or "C2."
  
• Document numbers should use format "D."
• Abbreviations in column headings should not include the period character.
• Column headings should not use the dash character.
• Specific column types (e.g., ARCUSFIL.CUS_NO) should be used where possible.

Open Issues

1.       Many maintenance programs have an option for a Numeric List and an Alpha List.  Do we want to replace both or just the Numeric List, possibly with one or more reports with an ORDER BY name clause?

2.       We need an analysis process to create a master list of UDRs we want to support initially in Elliott V8.5.  It should produce a spreadsheet something like this, for project management purposes:

Elliott Program	UDR Name	Type of Info	Assigned To	Status
ARNUMSCN	ARCUSLST	Basic Contact Info
ARNUMSCN	ARCUSLST	Customer Attributes
ARNUMSCN	ARCUSLST	Credit Info
AP01S1	APVENLST	Basic Contact Info
AP01S1	APVENLST	Vendor Performance

3.       What additional UDR capabilities should we consider supporting?

a.       Multiple line layout (to show more data per entity, e.g., stacking address info)

b.      Grouping (might be needed for better support reports like Customer Attributes)

4.       Is the font used in the reports OK?

5.       As we develop a few reports, we may want to compare notes and establish some standards.  For example, wherever we can search for a range of codes (like Customer Number) we should also allow to search by LIKE of the names (like Customer Name).

JEG