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 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:
SELECT DISTINCT CODE, <other columns>, <parent columns> FROM <dimension_view> WHERE company = '<parent_value>'
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 Applications 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.
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.
This section gives a short description on how a parameter LoV reference to be published to IFS Reporting is handled.
Assuming a case where we have two parameters in an IFS Business Reporter report:
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.
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.
NULL
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.
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: