Comments in SAP BW have always been a painful topic… There were some standard options in BEx, a few custom solutions created by consulting firms, but quite often the comment you created was saved somewhere – connected to some characteristic combination – never to be found back again…
In this post, I am going to show you a simple and pragmatic way to save comments using a planning provider and a characteristic. Besides being easy to implement, the most important benefit of this solution is that the comments can easily be made visible in any query. Compare this with the commenting service in Lumira: the comments you create there are saved in some SQL table on the BI platform. They can only be accessed from the application they have been created with – there is no trivial way to ‘share’ these comments between different applications and queries or print them on reports.
How to create a ‘comment key-figure’
- Create an infoobject of type characteristics and use datatype CHAR with an appropriate length. Make it case-sensitive. You don’t need any master data or text table for the infoobject.
- Add the infoobject to a (planning) aDSO, and do not include it in the key. The aDSO should be of type “Planning on Direct update”. Mark the checkbox “Use Characteristic as Key Figure” in the properties of the aDSO field.
- The aDSO (active) table will contain the characteristic value directly as a field. You can use it like any regular characteristic for reporting scenarios.
- When creating the composite provider and adding the aDSO you will see the characteristic (e.g. ZCOMMENT), and there will also be a key-figure call 1KYF_… (e.g. 1KYF_ZCOMMENT). For reporting-only scenarios, it is enough to add the characteristic, for changing the value during planning you will need to add the 1KYF_… version. (It is OK to add both of them to the composite provider if the composite is used both for planning and for reporting.)
- In the aggregation level – which will host your queries allowing entry of comments – you should add the 1KYF_… field only. Do not add the regular characteristic field.
- In the query: add the key-figure just like you would do it with any other key-figure. Of course, a comment cannot be aggregated or disaggregated, so you need to enable input-readiness by using a unique selection: all characteristics of the aggregation level need to be defined uniquely by single-value selection or by including them in the drill down.
- When creating custom planning functions, things will get a bit tricky: You will see the field with type RSDINT4, which is an integer. This, of course, can not hold a 250 character long comment string – it stores the SID of the characteristic value. You can look up the value in the SID table of the characteristic, e.g. /BIC/SZCOMMENT. If you merely want to copy your comments (e.g. from one planning year to the next) you would simply copy the integer value. The same comment text (stored in the SID table) is ‘referenced’ by both data records. Don’t worry, when the comment is changed manually in a query later, a new SID will be drawn automatically for the changed value. The original record you copied from will not be affected.
Read-only interpretation of the comment within the planning function is also not a big deal, just join the SID table. For changing the comment you might need to create another aggregation level or use the appropriate function module to generate the master data SID.
You should be prepared to accept some limitations: Not any text can be entered, you will typically have problems with linebreaks and some special characters. But for short single-line comments, you should be good to go.
How about planning ‘attributes’ with this technique?
Comments are one specific use-case of ‘characteristic as key-figure’ but they can also be used for planning real characteristics. Why would you want to do that? Why don’t you simply use the characteristic itself, and set its value when you create a new record – by planning function or by using the ‘new record rows’ at the bottom of the planning grid?
Yes, that will indeed work for new records, but for existing records, you can not change the characteristic value anymore in the frontend. BW-IP / PAK / BPC are generally meant for entering and changing numerical key-figure values*, not for recording ‘attributes’ in a flexible way.
With ‘characteristic as key-figure’ planning and changing characteristic values in existing records becomes possible. Just like with the comment field shown above, you can add a characteristic of almost any other type or length. In the screenshot below you see an example with a field of type date: F4 help works, the value entered is checked for validity, and – most importantly – the column in input-ready and thus not part of the ‘key’ but of the ‘data’.
Data entered this way is stored in the aDSO in a quite natural way, and can be used for reporting or for read access by code in a very natural way.
*) Often BW-IP is ‘misused’ for planning data that would ideally need to be done in a transactional system, e.g. investment planning. You could consider creating an own application (in SAPGUI or UI5) and extracting the data into BI or storing the data directly in a direct-write aDSO from the application. Unfortunately, the development and maintenance of such an application is usually costly and hard to support because of being a custom development, so developers are doomed to staying with the ‘standard’ solution. In this case, you could consider putting attribute into the master data and redirecting the user to the standard master data maintenance transaction, or using planning functions to change the master data via API function modules. Or, using PowerApps…
