IFP Function Reference

Overview

When creating a Crosstabulation Definition it is possible to include columns which contain either the code or description for any classification variables used in the data for the level of detail which is selected, or comments which have been entered using the Forecast Editors at that level of detail.

Crosstabulation definition Text field (type 28) can take a simple string (e.g. "my text") or one of the following functions, shown with their parameters:

IFPClassVar

In your Crosstabulation Definition you can show the codes and descriptions for any variable under the item code / level of detail. The required code to create this in the Crosstabulation Definition is split into 4 parts, each of which are separated by a comma:

1. Classification Variable Name or Global Id e.g. Therapy Group
2. Code or Description
• Anything beginning with 'D' will indicate description
• Anything else indicates code
3. Values Output - All/First/Distinct
• All (all values in the order of the items for the row)
• First (first selection/value found when working through items for the row)
• Distinct (shows every unique value for the selected items, sorted alphabetically or numerically)
4. Separator character (this can now be empty, even for multiple values)

IFPClassVar("Therapy group","Code","All",";")

An example Crosstabulation Definition setup showing the different Therapy Group Codes to return can be seen below:

Below is the output produced by this Crosstab Definition in a report using restricted Item Codes, and Channel as the level of detail:

An example Crosstabulation Definition setup showing the different Therapy Group Descriptions to return can be seen below:

Below is the output  produced by this Crosstab Definition in a report, using the same criteria as above. As you can see this returns the descriptions for the codes previously shown which are taken from the Item Code Classification Variables Maintenance:

IFPExchangeRate

Note: This feature is only available in version 3.5.5 and later.

IFPExchangeRate takes data file and month numbers and returns that data file’s exchange rate for the specified month, between the report currency and the planning currency for the item(s) in the row. The month number can be relative to the database environment settings, using the CM, RM and PM codes. Where there are multiple planning currencies for items in the row, no rate can be given.

IFPExchangeRate("1","CM")

Where 1 is the file number (first file in Data File Selection) and CM is the month code / number (Current Month in this case).

IFPComment

IFPComment allows you to show the comments which have been added in the Forecast Editor. The code required in the Crosstabulation Definition for this is split into 5 parts, each of which are separated by a comma:

1. A single File number to use (eg "1") or "All" for all files
2. The Data Type/s to show
1. U or Units
2. V or Value
3. A or P for ASP/Price
4. All for all data types
3. Item code level/Reporting level
1. "" will show comments from the reporting level chosen
2. "Item" will show comments from Item Code level only, regardless of reporting level chosen
4. Values Output - All/First/Distinct
1. All (shows all values in the order of the items for the row)
2. First (shows the first selection/value found when working through items for the row)
3. Distinct (shows every unique value for the selected items, sorted alphabetically or numerically)
5. Separator Character (this can now be empty, even for multiple values)

IFPComment("All","All","Item","First","; ")

An example Crosstabulation Definition setup showing the different Comments to return based on the Reporting Level can be seen below:

Below is the output demonstrating this Crosstab Definition in a report using firstly the Product Group level of detail and then Therapy Group as the level of detail:

You can see from these two screenshots how the first Item Code Comment in the level of detail output remains the same whichever level is chosen, while the specified Classification Variable comments change dependent on the variable being selected.