How to Implement Comment functionality in B-Ex Workbook with help of Macro’s

Scenario: Achieving comment functionality for the Fixed Input layouts in Integrated planning with help of macro (No input row in the End).

Background: As we know that there is no possibility of changing the characteristic value which already saved through the input queries, but we will be able to modify key figure values.

If we want to change the characteristics value of particular record then we have to add a new record with the same chars with –ve KF value and entering another row with +ve KF value with the required chars value. So my old value will get negated by the –ve value which already saved in the cube. But this will be time consuming process, if suppose user wants to modify for multiple records then there is high possibility of making some mistakes.

If my layout is provided with the input ready row in the last line, then we can follow the same method (entering –ve value of old record & entering +ve with new characteristics through macros).

This scenario can be achieved only when my query is provided with input ready Row in the End.

Query layout with input row in End:

In the above mentioned screen shot we can see that query (right side) is provided with input ready row for entering data. Also we can see that some values were already saved to the cube level through this query. So if you want to change the chars for any of the row, we need to manually enter the chars for that particular row (Normal Excel Columns). When we click on the save button after entering the new chars value then a macro program will be doing the below steps.

Generate one record with –ve value of same record and put it to the last available input row.
Populate one more record with +ve of same selected records with the new chars which selected in the right side.
Save both the records.
Delete values which are entered in the Normal Excel cells.
So when the 1^st record (-ve) get saved to the cube, it will negate that old record. So when the 2^nd record saved to the cube with the new chars value only this will be available for reporting. You will be able to see only the new record with modified chars in the query layout.

Query without Input Ready Row in End:

But our scenario is bit different, the layout is fixed (cell level defined query with structure in the Row), my query will not be giving the free input ready row in the end. So we have to follow different way to achieve it.

My Current query Layout without input row in End:

Even If I add the free chars in the query layout, once the values are saved for chars and then it’s not possible to modify it. It only possible by deleting the record selectively from the cube level and then enter the records again through queries. So it’s not advisable to add the chars in my query.

As my front end is Bex Analyzer, so I can able to insert multiple queries in different tabs of the same workbook.

Query in 2^nd Tab:

In the second tab of the workbook I have inserted one more query which similar to my existing query design but without cell definition with extra four fields of chars for saving the comments as shown below.

Also in the last you can see one more new field (‘Version for Comment’) available in the layout, which is also KF column. which will only contain value for the last saved comments, so only those data will be displayed with suppress zeros enabled in the Query properties. This also enhances the macro performance.

In my workbook 2^nd tab will not visible, it will be hidden to avoid users entering values to that query.

Adding Column “Comments” (Normal Excel Column) to Exisiting query:

Once the user enters values to each period and then they will be entering the appropriate comment to the available Comment column and then they will be clicking on the SAVE button.

As we know that, Comment which entered is in the normal Excel field so it will not be saved to the cube. Here our query in the 2^nd Tab is used.

When the user clicks on save, a macro program written and it will start execute and it will perform the following steps.

The values which are entered Tab1 query gets saved and then our macro program starts working.
It will copy all the available values from the Tab1 query and paste to the Tab2 query after multiplying it with -1 in their corresponding fields.
Populate +ve values to all the fields and then putting the comments which are entered in the tab1 to the respective chars field in the query.
Per SAP std we can save only 60chars values in one info object, so we have added four columns in the tab2 query. Macro code will paste the comment values to the query based on the length.
As macro negated the values which are saved in the tab1 query by saving the negative values and again values are saved with comments. So in the cube level I am having values with comments in the respective columns.
Once the SAVE is completed for tab2 query then the complete workbook gets refreshed, so my tab1 query will populate the KF values which are saved with comments by tab2 query.
So for showing the comments from tab2 to tab1, one more macro will get executed and it will populate the comments from the tab2 which are saved in four different chars column as a single value in the tab1 Comment field.

Example:- How Macro Handles the Comments between two Queries.