List of values Support for IFS Reporting
This page provides information about how to support dimension related List Of Values in IFS Reporting.
To make List of values support work properly in IFS Reporting it is necessary to have a view for each item in a dimension that should support this.
By default there is only one view defined per sources type and dimension; for Online execution there is only one view available for e.g. the dimension DIM_ACCOUNT. Since IFS Reporting reads the dictionary to be able to handle List of values, it will be necessary that there are unique dimensions with associated view comments for each and every List of values item.
In e.g. IFS Business Reporter the Business Reporting & Analysis services functionality is used to provide List of values for ALL items in a dimension. Only one source view is needed. The framework handles the necessary select statements based on the source view, to provide information for each item.
Since the same approach is not possible in IFS Reporting, there is a problem explaining that the IFS Business Reporter client has List of values support for all items in a dimension, while this functionality is not available automatically in IFS Reporting.
General about IFS Reporting support:
- Support is generally supplied only for the code item, the visible dimension item is marked with CodeKey=TRUE
- Dimension views for Data Mart and Online must contain view comments.
A dimension item has some special attributes for List of values support in IFS Reporting:
- Published Lov Dimension
- Published Lov Item Reference
View Considerations Related to List of Values:
Each dimension has ONE view per data access type and only one item can use this view as List of values source. To support List of values for other items it is necessary to reference to other views that has its own view comments. One example is the supportive dimensions. One other possibility can be to use a reference to an existing standard dimension that supports the necessary source views. The select order of the items from the dimension view is important:
- First parent items
- Next the key item (typically the code key item)
- Other attributes
Each view must have view comments according to standard guidelines, and it is important that the comments are defined in correct order:
- First parent items
- Next the key item
- Other attributes
Consider the following example.
Here we see that the parent column
company is selected before the key column code and after that the description. The view comments are also defined accordingly.
Note: It is very important to refresh the F1 dictionary after having created a new dimension view that is supposed to act as List of values view in IFS Reporting.
To enable List of values support in IFS Reporting for a dimension item, a REFerence string has to be created that looks like a REF instruction in a view column comment. A REF string looks like:
It is however not possible to pre-define the string as in standard views. The string has to be built e.g. when publishing an IFS Business Reporter report, using the Meta Data instructions and then insert the string into IFS Reporting framework.
To exemplify this, consider the two dimensions DIM_COMPANY and DIM_ACCOUNT.
DIM_COMPANY dimension acts as a parent dimension to DIM_ACCOUNT.
If the data access type is Online then the following source views apply:
- View DIM_COMPANY_OL for dimension DIM_COMPANY
- View DIM_ACCOUNT_OL for dimension DIM_ACCOUNT
We want to make sure that if a parameter is defined in IFS Business Reporter, referring to DIM_ACCOUNT.CODE or DIM_COMPANY.CODE, then the Meta Data should be pre-defined to handle List of values support in IFS Reporting.
Assume the following parameters in IFS Business Reporter:
- Parameter MyCompany, referring to DIM_COMPANY.CODE. We want to make sure that the reference string is built as "DIM_COMPANY_OL" (for Online based execution).
- Parameter MyAccount, referring to DIM_ACCOUNT.CODE. We want to make sure that the reference string is built as "DIM_ACCOUNT_OL(COMPANY)".
We also want to make sure that even if the company parameter is named MyCompany, it should get the name
COMPANY in IFS Reporting.
So to summarize:
|Item Id||IFS BA Parameter
Name in IFS Reporting
To achieve this we first define Meta Data for DIM_COMPANY.CODE according to:
For DIM_ACCOUNT.CODE the following Meta Data is defined:
Remember the following Meta Data definitions:
- Published_Lov_Dimension Identity of the dimension that has a view that will serve as List of values view reference for List of values support in IFS Reporting for the current dimension item.
- Published_Lov_Item_Ref Dimension item reference to an item in the Published_Lov_Dimension for which the column name will be used in IFS Reporting as the internal parameter name. Only to be definedif the current dimension item can act as a parent item to another dimension.
- A dimension acting as a child dimension, e.g. DIM_ACCOUNT acting as a child to DIM_COMPANY, must have parent information defined in Meta Data such that the Own Id refers to a column with the same name as the corresponding parent dimension item.
In the above example for DIM_ACCOUNT, the parent item is DIM_COMPANY.CODE with column name COMPANY, and the own item DIM_ACCOUNT.COMPANY has the same column name ( COMPANY ).
In IFS Reporting this means:
- A company related parameter will get the internal name COMPANY.
- A parameter based on the account code will, for Online, get the reference string "DIM_ACCOUNT_OL(COMPANY)", where COMPANY is the name of the own item id column.
- When List of values is performed in IFS Reporting for account, the string "DIM_ACCOUNT_OL(COMPANY)" is found. The parent COMPANY (in the ref string) is now used to find a parameter named
COMPANY, and that will be the parameter referring to DIM_COMPANY.CODE. The company parameter supplies the value, e.g. '900'.
- The last thing will be to build a select statement where List of values items, according to the view comments, are selected from DIM_COMPANY_OL with the condition "WHERE COMPANY = '900' " So it is important that the parent column gets the correct value.