Understanding LoV Functionality

Use this page to learn how the LoV functionality works in IFS Business Reporter and why it is not possible to by default get LoV support for all parameters in a published IFS Business Reporter report in IFS Reporting.

LoV in IFS Business Reporter

LoV works in IFS Business Reporter for any attribute, i.e. for both Fact and Dimension attributes. There is a configurable limitation when it comes to LoV support for Fact attributes. Since a Fact represents transactions, LoV functionality is not of much use for a measure or a light attribute. In most Information Sources the number of selected values for Fact attributes will be limited to 100. This number can however by modified but this is not recommended.

LoV in most cases makes sense only for dimension attributes.

As an example we can look at the Account dimension, DIM_ACCOUNT. The dimension has a set of detail attributes and all attributes refer to a column in the source views.

Above image shows a select from the on-line source view related to DIM_ACCOUNT.

Now, if in a report there is a parameter based on the attribute DIM_ACCOUNT.CODE, the following will happen when running the report:

1. The client will figure out if the dimension has any parent attributes. For DIM_ACCOUNT there is one parent dimension, DIM_COMPANY and the parent information is as follows:
   
   This means that DIM_ACCOUNT requires a parent value to be able to display accounts, which is natural since each company has its own chart of accounts. The information tells us that the parent item is DIM_COMPANY.CODE, which is a visible attribute, and that the corresponding attribute in DIM_ACCOUNT is DIM_ACCOUNT.COMPANY.
2. The client will try to find a parameter value for the parent item, DIM_COMPANY.CODE. If there is no parameter referencing the parent attribute, the client will pop up a dialog where the company has to be entered. The same is also valid if there are more than one parameter referencing the parent attribute; this since the company for that case is undefined.
3. Once the parent value is available, a LoV request is sent to the server that will build a statement similar to the following:

SELECT DISTINCT CODE, <other columns>, <parent columns> FROM <dimension_view> WHERE company = '<parent_value>'

1. If instead there is a parameter referencing DIM_ACCOUNT.ACCOUNT_GROUP, i.e. the Account Group attribute, then the LoV is built in more or less the same way:

SELECT DISTINCT ACCOUNT_GROUP, <other columns>, <parent columns> FROM <dimension_view> WHERE company = '<parent_value>'

The above means that LoVs in IFS Business Reporter are not dependent on other referenced entities except for the parent dimensions.

Also note that the same source view is used to generate LoV for any of the attributes in a dimension. This also means that the values that can be retrieved are only those that exist in the dimension, i.e. if LoV is ordered on DIM_ACCOUNT.ACCOUNT_GROUP the value list will only retrieve the account groups that are currently available in the account dimension. This is one of the big differences compared to how IFS Cloud in general handles LoV functionality.

The core entity Account has a reference to e.g. the core entity AccountGroup that handles account group information in a company. The reference is given like this: AccountGroup(company). This means that when LoV is performed in e.g. the Account page to get available account groups, the framework is using the reference and will generate a SELECT statement that gets data from a LoV view in the AccountGroup entity. This more or less means that one entity can only support LoV for the key attribute in the entity. If an attribute in an entity does not have any reference to another entity, then LoV is not possible.

Special Lov Related Attributes

A dimension attribute has some special properties that have the purpose to support LoV functionality for published IFS Business Reporter reports.

Looking at the details of the item DIM_ACCOUNT.CODE, i.e. the attribute representing the account code (key):

Special LoV properties:

Attribute	Description
Published LoV Dimension ID	Identity of a Dimension that should be used to find the LoV specific access view to be used when building the LoV reference string published to IFS Reporting. For the CODE attribute, this dimension is defined as DIM_ACCOUNT which means that either the on-line or the data mart source view related to DIM_ACCOUNT will be used. A typical LoV reference for an account parameter can thus be built as DIM_ACCOUNT_OL(COMPANY). It will also be the parents of the Published LoV Dimension ID that are used to build the complete reference string (see further down for more info).
Published LoV Item Reference ID	This attribute is used to refer to an item for which the column name should be used as a column reference for parameters referencing Dimension Item ID.

Looking at DIM_COMPANY.CODE in the above image, we see that the published dimension is DIM_COMPANY and the published LoV item reference is the item itself, e.g. DIM_COMPANY.CODE. If there is exactly one parameter referencing DIM_COMPANY.CODE (i.e. company), then this parameter will get a column name reference based on the published item, i.e. in this case the column COMPANY. If there are more than one parameter referencing an attribute, then the column name reference will be set to the parameter name. LoV on DIM_COMPANY_CODE leads to that the published LoV dimension is used to get the source view e.g. DIM_COMPAY_OL. There are no parents to consider in this case.

The Published Lov Reference Item ID is only useful if the dimension item, e.g. DIM_COMPANY.CODE is referenced as a parent item by the Published LoV Dimension ID for another item, e.g. DIM_ACCOUNT.CODE.

Basic Lov Principles

This section gives a short description on how a parameter LoV reference to be published to IFS Reporting is handled.

1. When publishing a IFS Business Reporter report, all visible and editable parameters are evaluated in order to build a reference string for the parameter to be used by IFS Reporting.
2. Each parameter references a dimension item attribute and the property Published LoV Dimension ID for the attribute defines a dimension that will be used to find the access view to be used in the reference string, since the reference string is built as <view_name>(<parent1_column_name>, <parent2_column_name>, ...,<parentN_column_name>)
3. LoV references will only be created if parent items are referenced by exactly one parameter. If e.g. the two parameters C1 and C2 are referencing the same dimension item,  DIM_COMPANY.CODE, then no LoV reference string will be created for parameters referencing items that have DIM_COMPANY as a parent dimension. E.g. a reference for DIM_ACCOUNT.CODE that is normally built as DIM_ACCOUNT_OL(COMPANY) cannot be built since the parent parameter to be used to represent the company value can not be determined.
4. If the referenced dimension item has a value in the property Published LoV Reference Item ID, then the column name of that item (attribute) will be used as a column reference name for the parameter, else the reference name will be the parameter name.
5. If the dimension defined by the dimension item property Published LoV Dimension ID  has parents defined then the following applies:
   1. Each parent of the Published LoV Dimension ID  is examined
   2. The Parent Dimension Item ID is used to check if there is exactly one parameter related to this item, If yes, then the property Own Item ID for the current Parent Dimension Item ID is used as column name for the reference string.
   3. If no, then a search is done to see if there is another dimension item/attribute that has the Parent Dimension Item ID defined as Published LoV Dimension ID and that has exactly one parameter defined.
   4. If yes, the column name of the found item is used as the column reference. Else the reference will not be built.
   5. Say that we, via the Published LoV Dimension ID, have two parent entries with the Own Item ID defined as <own_dimension>.<attributeX> with hierarchical order 1 and as <own_dimension>.<attributeY> with hierarchical order 2 and that these two attributes refer to the column name ATTRIBUTE_X and ATTRIBUTE_Y. The reference will be built as <view_name>(ATTRIBUTE_X, ATTRIBUTE_Y). But it must be made sure that there is exactly one parameter defined for each Parent Dimension Item ID and if there is no parameter match, a second attempt is made to find a dimension attribute with the Published Lov Referenc Item ID = Parent Dimension Item ID and if so the column name of this dimension item is used as the column name for the reference string.
6. To get the reference string built correctly it is thus important to make sure that parent dimension items of the Published LoV Dimension  ID are used by exactly one parameter. There is also a fallback mechanism that checks if there is a parameter defined for a dimension item/attribute that has Published LoV Reference Item ID = Parent Dimension Item ID

LoV References

Assuming a case where we have two parameters in an IFS Business Reporter report:

1. Parameter Name = Company,  referencing DIM_COMPANY.CODE
2. Parameter Name = Account, referencing DIM_ACCOUNT.CODE

The parameter references to be defined when publishing the IFS Business Reporter report are built as described below.

Starting with parameter Company. The parameter references the item DIM_COMPANY.CODE.

As seen in one of the above images, the item has two properties related to publishing.

1. Published LoV Dimension ID = DIM_COMPANY
   This identity gives the LoV view to be used, i.e. DIM_COMPANY_OL or DIM_COMPANY_DM depending on access type used by the report.
2. Published LoV Reference Item ID = DIM_COMPANY.CODE
   This identity gives the column name to be used a column reference name, i.e. COMPANY

DIM_COMPANY has no parent dimension.

Looking at our parameters so far:

Parameter	LoV View Name	Column Reference Name	LoV Reference
Company	DIM_COMPANY_OL	COMPANY	DIM_COMPANY_OL
Account

Next looking at the parameter named Account.

1. Published LoV Dimension ID = DIM_ACCOUNT
   This identity gives the LoV view to be used, i.e. DIM_ACCOUNT_OL or DIM_ACCOUNT_DM depending on access type used by the report.
2. Published LoV Reference Item ID = NULL
   Since the reference is NULL the parameter name will be used as a column reference.

Now we have:

Parameter	LoV View Name	Column Reference Name	LoV Reference
Company	DIM_COMPANY_OL	COMPANY	DIM_COMPANY_OL
Account	DIM_ACCOUNT_OL	Account	DIM_ACCOUNT_OL

We must also look at the parent information. This is done for the Published LoV Dimension ID, in this case for DIM_ACCOUNT.

The Own Item ID for each parent will be used to get the column names to be used in the LoV reference. There is only one, DIM_ACCOUNT.COMPANY and that attribute refers to the own column COMPANY.

We can now complete the reference information:

Parameter	LoV View Name	Column Reference Name	LoV Reference
Company	DIM_COMPANY_OL	COMPANY	DIM_COMPANY_OL
Account	DIM_ACCOUNT_OL	Account	DIM_ACCOUNT_OL(COMPANY)

When these references are published it will be possible for IFS Reporting to handle the LoV functionality. Important to note is that when performing LoV on Account, IFS Reporting will find the LoV reference DIM_ACCOUNT_OL(COMPANY) and will look for another parameter that has the column name COMPANY, i.e. the same name used in the LoV reference for Account. This means that it is very important to keep track of the column name that will represent a parameter, which is normally given by Published LoV Reference Item ID. And in the parent case, the Own Item ID items refers to column names that must match the public name defined for the parent attributes.

Checking Published LoV References

For a published IFS Business Reporter report it is possible to check the properties that have been sent to IFS Reporting for each parameter.

Use the page BR Report Parameters.

For each parameter the following two columns can be used to check and verify the published reference information.

Column	Description
Published Parameter Name	Published name of the parameter (column name reference). The name is used to find the parameter value to be used as a parent value. If e.g. a parameter P1 has the published name COMPANY and a parameter P2 has the LoV reference <View_Name>(COMPANY), then the value supplied for P1, e.g. the company value, will be used as a parent value, i.e. added to the WHERE condition of the SQL statement that builds the LoV for parameter P2.
Published Parameter Properties	The complete property string defined for a published IFS Business Reporter report parameter.

The parameter properties will show how the reference string is built for each parameter.
E.g. looking at the property string for parameter Period in the previous image more in detail:

• The column name is Period (same as Published Parameter Name )
• The Published Parameter Properties has the reference string defined as: REF=DIM_ACCOUNTING_PERIOD_LOV_OL(COMPANY, CODE)
  • This means that the view used is DIM_ACCOUNTING_PERIOD_LOV_OL
  • Two parent values, COMPANY and CODE, are needed to correctly handle the LoV request.
  • Thus, it is necessary that there are two published parameters that have Published Parameter Name defined as COMPANY and CODE, to make the LoV handling work as expected.