Report Desk: Using 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 ITEM_LAST_COST
  When '@@IMCTLFIL.IM_CTL_COST_METHD@@' = 'S' Then ITEM_STD_COST
  Else 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)

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.

Modifying the Report Columns Grid

JEG
EL850RD, SYRPTUDR

Feedback and Knowledge Base