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:
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:
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 A....
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:
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:
Now we get all the non-empty comments with the same selection:
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):
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.
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 A....
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.
- SAP Managed Tags:
8 Comments
