BPC 10.1 Embedded: Mandatory Short Comments
In BPC Embedded (and also PAK and BW-IP) we have to possibility to enter (short) comments in a query. The short comments are stored in (advanced) DataStore Objects and are treated like key figures. Now we often hear the requirement that a user should be forced to enter a comment in a comment key figure whenever the corresponding key figure itself has changed.
How can we check whether a comment has been entered or not? As validations are usually done by planning functions of type formulas (see BPC 10.1 Embedded – How to Validate your Data) we might think of using a formula here as well. But when you try to do this you will discover that formulas cannot run on comment key figures.
Why is this the case? Let us briefly have a look at how these comment key figures work. The comments are stored in a characteristic in the data part of a (advanced) DataStore Object. Whenever you enter a new comment (or change an existing comment), this text is treated as master data for the underlying characteristic and a SID is generated. In planning (Analytical processor, planning function) the comment is treated as a key figure and thus the system is using the SID internally. The SID is only replaced by the proper text before displaying it on the screen. This means that a planning function cannot receive the proper text but just a SID value. Basically any operation changing this SID value would lead to surprising results. Just imaging adding a 1 to a SID value – as SID value are arbitrary value the result would be another comment that most probably has no connection to the first one. The only operations that make sense are:
- Copy comments: here the value of the comments remain untouched, only the characteristic combinations in the key part of the DSO change
- Delete comments: either set the SID to 0 which is the fixed and predefined SID value for the empty characteristic value (displayed as comment ‘#’) or physically delete the records from the DSO altogether.
Both operations on comments are available via standard planning functions anyway and thus we do not allow to use formulas on comments.
There is one more operation on comments that does not change the SID value but still makes sense – a check whether a certain record (with a specific characteristic combination) does exist in the DSO and whether it is not just blank (SID = 0). And this is exactly the check we need for making comments mandatory. So we need a planning function that runs over all records in a given selection, finds all records where a given key figure is not empty, and checks whether there is a corresponding non empty record (SDI<>0) in the comment DSO.
Luckily SQL-script planning functions are a little less restrictive than formulas. So we create a SQL-script planning function that does perform the checks we just have described. Sure – you could also create a planning function as an ABAP exit but as we want to be in-memory enabled we use SQL-script.
Caveat: Please keep in mind that you should NOT change the values of the comment key figure as this might lead to unexpected results (see above)!
We explain how to proceed with an example. Let us assume we have an InfoProvider for the plan data containing the key figure 0AMOUNT and the following characteristics:
Name | Technical Name |
Year | 0CALYEAR |
Country | TECOUNTRY |
Material | ZD_MAT |
Material Group | ZD_MTLGRP |
Currency | 0CURRENCY |
In addition we have an advanced DataStore Object containing also the characteristics for year, country, material, and material group and a characteristic as key figure for the comment (ZTSC0OCCN). We have defined a CompositeProvider on top of these InfoProviders, an aggregation level, and a query that shows the amount in one column and the comment in the column next to it.
Now we can go ahead and create a planning function type SQL-script using the report ‘RSPLS_SQL_SCRIPT_TOOL’. We use an AMDP for our SQL-script coding. For further information on how to create a planning function type SQL-script (as AMDP) have a look at
How to… Use SQLScript for Planning Functions in PAK and How To…Use Data from Another Aggregation Level in a PAK SQL-Script Procedure – Implemented as an AMDP Example.
Here is one idea how the planning function could be realized:
First of all we have to make sure that we return the correct table for the transaction data – we do not want to change the transaction data. As we have to return the deltas we just return a table of ‘zero’ deltas:
e_view = select
CALYEAR,
INFOPROV,
TECOUNTRY,
ZD_MAT,
ZD_MTLGRP,
CURRENCY,
0 as AMOUNT,
0 as ZTSC0OCCN
from :i_view;
Now we do some preparation for the actual check we want to do. It is possible to further optimize the SQL-script coding but we leave it a little “more explicit” so it is easier to understand.
Here we select all the not empty records in the transaction data:
l_temp1 = Select
CALYEAR,
TECOUNTRY,
ZD_MAT,
ZD_MTLGRP
from :i_view
where INFOPROV = ‘TE13_PL’ and CURRENCY = ‘EUR’
and AMOUNT <> 0;
Now we get all the non-empty comments with the same selection:
l_temp2 = Select
CALYEAR,
TECOUNTRY,
ZD_MAT,
ZD_MTLGRP
from :i_view
where INFOPROV = ‘TE13_COM’ and CURRENCY = ”
and ZTSC0OCCN <> 0;
And finally we send a message for all those materials and material groups (one could add the country and the year as well if necessary) where we do find a record in table l_temp1 (transaction data) but do not find a record in table l_temp2 (comments):
e_msg = Select
‘RSPLS’ as msgid,
‘E’ as msgty,
‘000’ as msgno,
‘Enter a comment for ‘ as msgv1,
ZD_MTLGRP as msgv2,
ZD_MAT as msgv3,
‘ ‘ as msgv4
from :l_temp1 as a
where not exists (
select 1 from :l_temp2 as b where
a.CALYEAR = b.CALYEAR and
a.TECOUNTRY = b.TECOUNTRY and
a.ZD_MAT = b.ZD_MAT and
a.ZD_MTLGRP = b.ZD_MTLGRP );
You can just copy the above coding, eliminate our comments between the pieces of coding and adapt the coding to your situation.
In order to test our scenario we have created an Analysis for Office workbook containing the query and a planning sequence that calls our planning function. The planning sequence (and thus the check) is triggered by pressing a button.
We have entered a number without a comment and thus get error messages specifying which comments are missing.
Once we enter some comments and press the button for the planning function everything is fine.
You can also make the system execute the check without any user interaction and can influence the displayed messages (for example suppress the message that there was an error in the planning function). Please have a look at the following blog BPC 10.1 Embedded – How to Validate your Data again.
Hi,
Why can't we use standard VB to make comments columns mandatory ?
Prat
Hi Prat,
Sure you can use VBA to check this. But this has a number of disadvantages:
- It is locally in a workbook. So you have to create the macro for every workbook. And most probably the positions of the comments in the sheet in each of the workbooks are different so you might even have to adapt the macro.
- VBA cannot be used in the Web, say a design studio application. The proposed solution is also handling this case.
Best regards,
Gerd
To my mind the idea of "mandatory comments" is not related to common sense... Any nonsense character can be entered. Commenting figures is the administrative task that can't be automated 🙂
HI Gerd , i add a characteristic in ADSO , and checked the Use Characteristic as Key Figure , but in the query , can not add this Text Characteristic as key figure , and the planning tab is hide.
Hi Kumar,
Dis you build the query directly on top of the aDSO? You need to create an aggregation level and build the query on top of the aggregation level.
Best regards,
Gerd
Hi Gerd,
the solution works fine. But i have one Szenario when DSO is empty, i can enter dat in DSO but comment column is not editable.
After I save one record in DSO, then Comment can be entered. This behavior is since we have BW 7.5 (SP6) . Before (BW 7.4) this works with empty DSO. Is this a nown behaviour or are there some aditional steps in configuration?
Best regards,
Achim
Currently we have the same problem.
System is not stable. After saving text comments sometimes they are just deleted from DSO.
Hello Gerd, hello BPC experts,
concerning to the above mentioned solution, can you give us more input to consider the crucial export parameters for the message table and declaration of local variables?
I read a lot of SQLScripts and AMDP approach, but I cannot find my own missing object.
In my debugging, I have to return values in l_tepm1 and l_temp2:
In my SQLScript Coding I have not yet considered the declaration, I have to; I don't know the correct way.
Additionally I am not sure if I implement the additional export parameter fro message output.
In the definition & public section I consider the message table:
types:
begin of tn_s_mesg,
msgid type sy-msgid,
msgno type sy-msgno,
msgty type sy-msgty,
msgv1 type sy-msgv1,
msgv2 type sy-msgv2,
msgv3 type sy-msgv3,
msgv4 type sy-msgv4,
END OF tn_s_mesg,
tn_t_mesg type standard table of tn_s_mesg with non-unique default key.
Within the hana procedure methode I consider the message output objects:
e_t_mesg = Select
'RSPLS' as msgid,
'I' as msgty,
'000' as msgno,
'Test Kommentar' as msgv1,
Z_IBSTOT as msgv2,
'' as msgv3,
'' as msgv4
from :l_temp1 as a
where not exists (
select 1 from:l_temp2 as b where ....
Hope someone are able to support me. Thanks a lot in advance.
BR
Markus