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 1st record (-ve) get saved to the cube, it will negate that old record. So when the 2nd 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 2nd 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 2nd 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 2nd 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.
- Initially both the queries will remain in sync.
- Tab1 Query where user plans.
Tab2 Query, this tab will be hidden always.
- Assume User changes the values and comment in query1 as mentioned below.
- Macro repost is done to the query in “sheet1” as below,
Before save – existing values in query2.
- Step1 – Existing values are negated
Since all the values are negated only the Input ready row is available.
- Step2 – Only new values are saved.
Values from the “Tab1” query are saved to the above query with corresponding “version for comment” field value 0.1.
- Step3 – Values with comments are saved.
Comments from the “Tab1” query are saved to the above query with corresponding “version for comment” field value 0.1.
Finally when all the above manipulations are done through macros, the query data appears as below.
These comments are again concatenated to the Workbook Tab1.
I have attached the macro codes for Comment Reposting and Comment Refresh as text file with this document.
Hope this will be helpful.