Report Desk: Global Variables, Parameters and Embedded Functions
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:
CaseWhen '@@IMCTLFIL.IM_CTL_COST_METHD@@' = 'R' Then COMPONENT.ITEM_LAST_COSTWhen '@@IMCTLFIL.IM_CTL_COST_METHD@@' = 'S' Then COMPONENT.ITEM_STD_COSTElse COMPONENT.ITEM_AVG_COSTEnd
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
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."
@@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
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