VERSION in FORMS

Note: The documentation below describes how Bizview handles VERSION in FORMS when you are using Bizviews standard procedure for retrieving data to BV-columns. If you are using another table or view defined in BV_SOURCE, then the initial SQL query will not be the same since standard functionality is not used (and it is in these cases problems occur when defining values in column D in PARAM).

The VERSION parameter is handled in forms in a special way compared to other objects and other parameters. The reason for the special handling of VERSION in forms is because VERSION must be unique for each form. For example it is not allowed to store multiple versions in the same form. As we have this limit, designers cannot specify VERSION in the BV columns in the template, but this must always be entered in PARAM in column C.

VERSION in PARAM is thus completely linked to the BV columns, both those listed in column C in PARAM and possibly in column D. If you have other help columns in the template that are also linked to VERSION, this is stated in column A in the template itself. The idea of ​​BV columns and the management of VERSION is built so that Bizview via its standard plandata proc takes care of this, even if you have both VERSION in the template (help columns) and VERSION in PARAM (BV columns). Bizview then selects all these versions in the first SQL query running against the database. With this, performance does not deteriorate since all VERSIONs contained in the book are added to the initial SQL query to reduce the amount of data.

Note: This is then a difference to handling other objects and parameters. Other parameters and objects are handled so that in the initial SQL query only values ​​from PARAM  are included (ie, no values ​​from the columns in the template, selection towards these columns will occur later).

VERSION in PARAM:

This topic contains the following sections:

• Column C
• Column D
• Example 1
• Example 2
• Conclusion
• Consideration

Column C

Access the Version drop-down by selecting Properties > Parameters > Frequently Used > Version.

Only ONE unique value. The version specified here is the version that is linked to the data records stored in the form (BV columns). If you only specify VERSION in column C (NOT in column D), this value is also used when retrieving data back to the BV columns when opening the form.

Column D

Access the Value for fetching drop-down in Properties > Parameters > Frequently Used > Version.

One or more values used for retrieving data to the BV columns. This column should be used ONLY if you want to retrieve multiple versions to the BV columns. For example, if you have a staff budget stored in a separate form and then you want to retrieve these records in a cost budget. Then you may want to pick up two different versions to the BV columns, where the staff budget records are linked to one version and the other costs records to another version that you probably also store (by entering in column C). Summary: The records connected to the versions specified in column D are inserted into the BV columns, which means that there may be a summary of, for example, two versions in the BV columns if you do not have any other dimension that separates version data (i.e  different account, meaning different account rows)

Example 1

In a form you have:

• VERSION in column C i PARAM: VERSION = BU2017
• VERSION in column A in template: 2 different version in help value columns, BU2016_01 and BU2016_02.

The first thing Bizview does is by using dat.p_data_cells_populate create the following SQL:

INSERT #dim_sel_clusters
    SELECT DISTINCT dim_cluster_id
    FROM   dat.data_cells dc
    INNER JOIN #timevalues tv on tv.timeunit_id = dc.timeunit_id and tv.timeval_id = dc.timeval_id
    WHERE client_id  = '1'
               and row_status = 0 
              and (version_id = ' BU2017' OR (version_id = BU2016_01') OR (version_id = ' BU2016_02'))
               and (currcode_id = 'USD')
               and valtype_id = 'AMOUNT'

ALL versions that exist in the whole workbook (both in PARAM and column A in template) are added to the initial SQL query, in order to reduce data records and thus improve performance.

When Bizview in a later stage retrieve data to the BV columns it will addVERSION=BU2017to the where clause.

Example 2

In a form you have:

• VERSION in column C in PARAM:VERSION = BU2017
• VERSION in column D in PARAM: VERSION = BU2017#BU2016_01#BU2016_02
• VERSION in column A in template:2 different version in help value columns, BU2016_01 och BU2016_02

In this example, the first initial query that Bizview runs DOES NOT differ from example 1

INSERT #dim_sel_clusters
    SELECT DISTINCT dim_cluster_id
    FROM   dat.data_cells dc
    INNER JOIN #timevalues tv on tv.timeunit_id = dc.timeunit_id and tv.timeval_id = dc.timeval_id
    WHERE client_id  = '1'
               and row_status = 0 
              and (version_id = ' BU2017' OR (version_id = BU2016_01') OR (version_id = ' BU2016_02'))
               and (currcode_id = 'USD')
               and valtype_id = 'AMOUNT'

However, the where clause in the SQL query run in later stage will be different when Bizview specifically retrieves data to the BV columns because it will then retrieve version from column D: VERSION = BU2017 # BU2016_01 # BU2016_02

(and in most cases, it will be the wrong values in the BV columns if, you as designer, use column D to enter all versions to use in the form, although some versions are only used in help columns and not BV columns)

Conclusion

In our standard handling of version (data cells populate procen), both versions in  PARAM and column A (template) are included in the first initial SQL query, thus reducing the amount of data. With this version management, when Bizview retrieves both from PARAM and the template initially, there is no need to enter all versions used in the whole worksheet in column D of PARAM.

Note: This assumes that you do not use BV_SOURCE and waive the default handling of retrieving data to BV columns.

So in other words, ONLY use the D column in VERSION in PARAM if:

• You want to merge multiple versions into another version (in the BV columns)
• You want to retrieve data from another version for some rows in the form and save them to a new version
• You want to retrieve data from another version for some rows in the form and then make sure that these rows are not DATA rows, which means they are not stored in the new version but just retrieved  for viewing (such as the example of retrieving personnel budget rows in a total cost budget )

Considerations

Below are a list of considerations with the above VERSION handling:

1. If you use your own procedure or view inBV_SOURCE, the logic will not work with initially retrieving version from both PARAM and column A to keep down the data amount (data_cells_populate procedure makes this investigation)
   1. This result in designers trying to enter all versions in column D to reduce the amount of data, but this will cause problems at a later stage when data is collected specifically for the BV columns
2. Performance is affected when using own tables/views. The only way to include VERSION & other objects in the where clause query towards the database and thus reduce the number of records returned to BV temptables is to add them in PARAM. Is that only a subset of the versions used in the template workbook, designers want to make a rough selection in column D in PARAM.
   1. Since you are not allowed to use VERSION for the BV columns in the actual template worksheet, those who use column D in PARAM (to reduce data amount) get too much data in the BV columns
3. Managing VERSION is different from other handling of objects and parameters, which makes it confusing for designers. The reason it is handled differently is because it is the ONLY parameter that must be unique for all BV columns (storage columns) in the template and therefore must be entered in PARAM for these particular columns. The reason why the handling is done differently is all because you can only save on ONE version in a form.
4. Only storing one version in a form and thus having to enter this in PARAM and not in the template is also a constraint which makes it special and probably problematic in some cases.