Report Desk: Global Variables, Parameters and Embedded Functions

Report Desk: Global Variables, Parameters and Embedded Functions

Release Date: TBA
Version 8.6

Using Global Variables
Sometimes it is necessary to access the fields in Elliott's control files, like NSCTLFIL and IMCTLFIL, in order to produce the desired report.  For example, in reports that have item cost data, you need to access the column IM_CTL_COST_METHD in IMCTLFIL in order to know which cost column should be displayed in the report.  In this situation, you can use a CASE statement in a [Formula] to pick the appropriate column:

Case
  When '@@IMCTLFIL.IM_CTL_COST_METHD@@' = 'R' Then COMPONENT.ITEM_LAST_COST
  When '@@IMCTLFIL.IM_CTL_COST_METHD@@' = 'S' Then COMPONENT.ITEM_STD_COST
  Else COMPONENT.ITEM_AVG_COST
End 
The above results in the column name specified on the formula screen with the value from the appropriate column, according to the information in IMCTLFIL.



The way a global variable is represented is as follows:

    @@<tablename>.<columnname>@@

Report Desk  will read the appropriate row in <tablename> (e.g. WHERE IM_CTL_RECORD_NO = 1) and return the value in <column name>.

Note: Surround the variable name by single quotes if it is not numeric.  In the example above, notice the single quotes around @@IMCTLFIL.IM_CTL_COST_METHOD@@, because that is a CHAR field.

Available Global Variable tables:
IMCTLFIL
NSCTLFIL_1 to NSCTLFIL_3
NSCTLFIL_5 to NSCTLFIL_35
NSCTLFIL_99 to NSCTLFIL_100
NSCTLFIL_1000
NSCTLFIL_2001 (supporting AP Control File)
NSCTLFIL_2002 (supporting AR Control File)
NSCTLFIL_2003 (supporting COP Control File)
NSCTLFIL_2004 (supporting PO Control File)
NSCTLFIL_2005 (supporting Company File and GL)
NSCTLFIL_2006 (supporting BM Control File)
GLPRDFIL (see v8.6 DDFs for column names)
COMPANY (following columns only)
        COMPANY_NO
        COMPANY_NAME
        COMPANY_ADDR1
        COMPANY_ADDR2
        COMPANY_ADDR3
        COMPANY_PHONE
        COMPANY_NO_OF_DEC_ACCUM
        COMPANY_NO_OF_DEC_COST
        COMPANY_NO_OF_DEC_PRICE
        COMPANY_NO_OF_DEC_QTY
        COMPANY_GL_LEV_1_DIGITS
        COMPANY_GL_LEV_2_DIGITS
        COMPANY_GL_LEV_3_DIGITS

Using Parameter Values
Sometimes it is necessary to use the user's entered parameter values to produce the report.  For example, on the Customer ABC Analysis Report the user enters the percentage to use to determine the ABC level of the customer. The percentages are not used to select data. The percentage can be computed with a cross join but the ABC rating can't be determined without the parameter values for the case statement.

The way a parameter value is represented is as follows:
@@PARAM.<parametername>.<optionalqualifier>@@

For example, to specify the from Salesman Number in the parameter screen below...

.. specify SLM_NO as the Column name from the first red box below, and, because the operator is BETWEEN, add the optional qualifier ".FROM."

This is specified as @@PARAM.SLM_NO.FROM@@ .  The to salesman number would be specified as @@PARAM.SLM_NO.TO@@ .

NOTE: If the value of a parameter is set to "ALL" when you leave its input field, you must not test for 'ALL' -- you must test for the value of '', which is two single apostrophes with nothing between.

NOTE: You cannot use drag-and-drop from the TreeView on the left for any column other than Column.  If you want to put a formula, for example, in the Column Heading column, you must type it in there instead of attempting to drag-and-drop it there.

Using Embedded Functions


Let's say you want to produce some text like the following in a column:

Period: 1/1/2019 to 1/31/2019

...where the dates come from the GLPRDFIL table.

To accomplish this, you need to go to the Template for the report and create a new function like this:

'Period: $$Format(@@GLPRDFIL.GL_PRD_CUR_START_DTE@@) to $$Format(@@GLPRDFIL.GL_PRD_CUR_END_DATE@@)'

Here is how this works.  First notice the variables that will be replaced by values from the database.  They are in Bold below:

'Period: $$Format(@@GLPRDFIL.GL_PRD_CUR_START_DTE@@) to $$Format(@@GLPRDFIL.GL_PRD_CUR_END_DATE@@)'

But, those values are integers like 20190101 and 20190131.  These numbers need to be formatted.  To accomplish this, you can embed a function like the bold pieces below:

'Period: $$Format(@@GLPRDFIL.GL_PRD_CUR_START_DTE@@,Date-8) to $$Format(@@GLPRDFIL.GL_PRD_CUR_END_DATE@@,Date-8)'

The embedded function, Format, is identified by the $$Format() syntax, where the two arguments to the function occur between the parentheses, separated by a comma.

Currently, we support the embedded function, Format.  The formats supported match the possible formats that can be assigned to a report column (like AcctNo, CustNo, etc.).  A complete list of display formats and their descriptions can be found here.  

Note: Functions names and format names are not case-sensitive.

In the future we may introduce additional functions for you to embed in a template function

JEG
EL860RD, SYRPTUDR


    • Related Articles

    • Report Desk: Documentation Roadmap

      Release Date: 4/28/23 Version: 8.6 Setup and Configuration The following articles explain initial setup and ongoing maintenance of Report Desk: Report Desk: Setup and Configuration : Initial installation and ongoing maintenance. Elliott V8.6 ...

    • Report Desk: Telerik Functions

      Release Date: 4/28/23 Elliott V8.6 Telerik Functions in Report Desk In addition to the Aggregate functions that are automatically mapped to Telerik functions in Group Headers and Footers and Report Footers, you may also use built-in Telerik functions ...

    • Report Desk: Entering Date Parameters

      Release Date: 4/28/23 Elliott Version: 8.6 Entering Dates Report Desk input parameters that represent dates can be entered in two ways: By using the drop-down calendar --dates can be selected by browsing through the calendar and selecting one. By ...

    • Report Desk Tables

      Release Date: Same as V8.6 general release Modified Date: 05/10/2024 Version 8.6 Elliott Report Desk, initially released in Elliott v8.6, relies on a series of database tables that reside in the Root directory. Following is a list of the files and a ...

    • Report Desk: Enforced Destinations

      Release Date: 9/22/2022 Elliott Versions: V8.6 and Up Background Like Elliott legacy reports, designated Supervisor users are able to specify Disk and/or Email output settings that all other Elliott users are restricted to. This is especially useful ...