Yesterday our team faced a small but interesting requirement from our client. The thing is pretty simple for intermediates & experts, but novices like me had to learn the hard way through the sappy oceans.
Business Context
In BEx we had designed a report which gave a sample output something like this below.
Fig.1 - Issue
The issue was pretty simple. There was no data in the fields for APR (Group E, Group D2 and Group A1). The real thing is even the variables used for calculating the month values were not having any data, and displayed blanks. But, on the contrary, for a small data the client(s) can understand limitations of BEx and start adding 0s to blank values in their workbook, but the cases listed as below we can’t compromise.
No-Compromise Cases
Case 1: Suppose, if our clients don’t use BEx Analyzer, wherein usual process is to download the data into Excel format, make charts out of it, but rather shift to a newer Web based BEx? In case of Ad-hoc reports, what if the data looks odd & as in Fig.1.1
The data analysis can’t be made without the help of good charting tools/options. But the simple loss of data can be a potential setback for the company. If interpretation is not proper, maybe we can go on to continue with legacy XLS excel CSV etc, but at what point is it right to use the tools, when we can float freely on BEx’s OLAP.
Case 2: Suppose, in some scenarios as listed below in Figure 1.2, what if there’s no data for certain fields, and makes report data look all messed up before the stakeholders?
Fig.2 - The data part
Rather than beating the bush, my client’s requirement was simple. They require to display 0.0% in the data output, which has been explained how-to on different scenarios. Consider the scenario as in APRIL month, which takes data from Attrition Nos., Mp Opening and MP Closing. Here this BEx report explains all about Grade wise Attrition.
Attrition is nothing but loss of personnel by Withdrawal (which can be both Voluntary & Non-Voluntary) Attrition Nos. describes how many employees move out from a particular domain/organization/location based on their Grade. Attrition is not bad always if it happens in a controlled manner. And, Opening strength describes how many employees are from a particular domain/organization/location at the beginning of Month-Year. The toughest concern for an HR manager is however the high attrition rate. Closing strength describes how many employees are in the particular domain/organization/location at the end of Month-Year the user has selected. You can plainly say that attrition is reduction in the number of employees through retirement, resignation or death.
Now, we will look into the specs of the formula for the input month (say April). Here, we need to understand that the value April is a dynamic value, which is captured by a variable say Enter Period (Z_SOMEVARNAME). This selection is used for user input of the corresponding month. Thereafter all data that follows are like MONTH+1, MONTH+2, MONTH+3 etc till MONTH+11. So, as a whole we have similar data sets for April (XXXX) to March (XXXX+1), which is pretty obvious because it details a whole fiscal year data. Now that we have the details ready, we’ll start the trouble shooting.
Trial Solution 1: We have assumed that all data is either present or 0
- Replace Formula say (Test1*12/Test2 )) by (Test1*12/Test2 ))* COUNT(Test1)* COUNT*(Test2); i.e. this must display Values if and only if Test1, Test 2 <> 0
Trial Solution 2:
- But there are clear limitations for Solution1, as COUNT(SomethingXX) can be equal to NULL; not 0, which is often displayed as blanks. In this case when there’s no data for the var SomethingXX, then there’s option but to choose Solution 2
- Consider the formula like Existing formula = 0.3 * SomethingXX
- Now we need to present the values on such a pattern to display a %, which is done by %A 100.
- Now we need to add an extra 0 to the existing formula, which is the best trick in BEx
- [Existing formula + 0] %A 100 which is [003 * SomethingXX + 0] %A 100
Sample screenshot of the formula variable after changing the details will be something like this,
Fig. 3. - Sample Screen shot
Need to test this for different cases (Not very sure if will work for all cases, but its effective solution!)
Trial Solution 3:
- Don't suppress '0' values.
- SAP Managed Tags:
1 Comment
