We have a requirement to create Total overtime hours in a report. This is calculated by a formula as we have the normal regular hours + overtime hours in 2 separate fields. We need to either do it in a Webi report ( report side) or in the BW Query which is sitting on a composite provider.. I choose to do it on the data target end as its much better to do it in the BW query, where the values will be ready before the webi report fetches them.
For this scenario, I am listing down the steps to work on the BW query through HANA studio ( eclipse ) sitting on the composite provider.
This process is to create a CKF cal key figure on the BW query – interface. Earlier we used do the same on a bw query using a Bex query designer, but this is something similar but in a eclipse interface. This article will help you achieve the same on an eclipse interface.
Open the SAP HANA studio- navigate to your BW modeling perspective on the top icon – right side and choose/search your respective composite provider where the Query has to be created or open the BW query if you have already.
After opening the query, open the inforprovider tab on the top left side of the SAP BW Query as shown below- where you will be seeing all the measures/char/CKF and RKF of the data target in the BW query.
Navigate to the CKF section, right click on it and choose. “New calculated Key figure”
choose create new restricted key figure if required – based on your requirement, but here I need only CKF.
This is the place where you need to assign a Technical name and a Description to your CKF/RKF . This will be the final name of the CKF/RKF.
create a transport request when prompted, this transported need to be transported to Quality and Production. Need to deploy through a change management process or through a solution manager. Choose 1 and add a Transport description as per your company naming convention. Click OK.
change properties of the CKF, if required – like scaling factor/ decimal values etc.
This is the main calculation part of the CKF, if you need to divide/multiply or choose another CKF or RKF to do any arithmetic operation. You can select any Key figure existing in the Data target using the below selection and do the final evaluation before submitting OK.
add any other new CKF/RKF or formulas based on your reporting requirement. You can do any currency conversion as well for the CKF properties on the right.
Add any other new Key figure if required using the below feature. – after right clicking on the columns section.
Final CKF after the above steps – which will have the final end result – can be seen in Webi/AFO or which ever front end reporting SAP BO tool – in case if we are accessing this SAP BW query to create a SAP BO report. I have created a SAP Business objects – Webi report on top of it as discussed above and the value displayed accurately. Hurray !! 😀
BW Query results- Final output
Final Webi report – with CKF showing in the Grand Total