Business Scenario
Consider one sample report which is designed in SAP BI by taking the employees data from Project Systems and stored in the Employees Info Cube in SAP BI. In the employees info cube, we are maintaining all the employees’ information like when that employee is recruited, under which cost center he is maintaining, to which project he is assigned, which is the profit center for that employee, what is his status like whether he is project or growth pool and when he is exited.
Report Requirement
Here we are considering only the permanent employee which belongs to ‘10IN’ company code. (Company code = ‘10IN’ and Employee Group = ‘P’).
As of 30.09.2011, the employee’s data under ‘Nokia’ profit center is
As of 01.10.2011, the employee’s data under ‘Nokia’ profit center is
Now, user needs to analyze profit center wise increase and decrease in the number of employees count in the chargeable category. This report is a day wise report, where Date is the input criteria (User entry mandatory field).
When user executes the report, the pop-up will come to allow the date on which he need to analyze the employees count.
Suppose he enters 01.10.2011 and executed the report.
The output need to display is
Number of Employees Incoming Outgoing
Profit Center
NOKIA 6 3 2
Logic to get required output is
On 30.09.2011, under Nokia profit center the chargeable employees are
20, 30, 40, 50 and 60. (Employee number) – Total 5 employees
On 01.10.2011, under Nokia profit center the chargeable employees are
20, 50, 60, 70, 80 and 90. (Employee number) – Total 6 Employees
User enters 01.10.2011 and executes the report. It has to display the total number of employees on that day.
To display the incoming count and outgoing count, at the run time of query always it has to check with the previous day chargeable employees i.e. 30.09.2011 employees according to that it has to calculate and display.
Incoming means – An employee has to be there in the present day chargeable count and not to be there in the previous day chargeable count. Then we can say that employee is an incoming to that profit center.
Outgoing means – An employee has to be there in the previous day chargeable count and not to be there in the present day chargeable count. Then we can say that employee is an outgoing from that profit center.
User needs to analyze the data up to employee level only, means he want to see the employees who moved out from that profit center and who came to that profit center.
So in the free characteristics tab of the report, we place the employee number for further drill down to analyze.
This incoming and outgoing count we will present using a formula which compares present day employees with previous day employees using Boolean Operators in the formula.
Step By Step Procedure to be Followed
In the cube we have characteristics
ZCCODE – Company Code
ZEMPGRP – Employee Group
ZEMPNO – Employee Number
ZPRFT_CTR – Profit Center
ZWBSCG – WBS Category
And time characteristic we have 0CALDAY.
The key figure is ZHDCNT_LAST (Number of Employees)
Here model this key figure aggregate property as it has to give the last value of the employees count based on the 0CALDAY wise.
When comes to report design,
In the Filter Tab, we are restricting the Company code with “10IN” and Employee Group with “P” values. (Say suppose in the cube we have all the company code employees and all the types of employees like Permanent and contract employee. In that we need only Indian permanent employees we want to analyze in the report).
In the rows tab, we are placing the Profit Center and in the free characteristics place the Employee number for further drill down.
When comes to columns tab, we will create new selections under key figures as Chargeable employees on previous day and chargeable employees on present day.(In our scenario say previous day chargeable employees means 30.09.2011 and present day chargeable employees means 01.10.2011 which user will give it as input when executing the query).
Here 0Calday is restricted with Cal day variable which is of process type “User Entry” and entry type is “Mandatory”.
In the similar way create one more selection for chargeable employees on previous day. Here 0CALDAY is restricted with ‘Cal day-1’ using variable offset. And hide this selection in the report, because in the report output we need to present day count only. Previous day selection value will be used in incoming and outgoing formulas to compare.
Under Chargeable employees on present day selection create a formula which shows the incoming value.
And here in the formula, use Boolean operators. (All Boolean operators’ return 1 if condition satisfies and returns zero if fails). Boolean operators are used to create conditional calculations.
In our formula, we are using
Relational Operators like
“Is not equal to” Operator – this operator returns 1 if the condition satisfy, else returns zero.
“Is equal to” Operator - this operator returns 1 if the condition satisfy, else returns zero.
Logical Operators like
“Logical AND” – which is used to return the value if both the conditions satisfies only.
Step 1: Incoming formula
The formula we created to show incoming count is
“(Chargeable employees on previous day <> 1) AND (Chargeable employees on present day ==1)”
And in the formula aggregation tab we are marinating Exception Aggregation as total value as of Employee count consider.
With this formula, we are comparing present day chargeable employees with previous day chargeable employees and if any employee was not there in the previous day count and if he is there in the present day count it will show that employees count total.
According to the above cube data, it has to show incoming count as 3.
Step 2: Outgoing formula
The formula we created to show incoming count is
“(Chargeable employees on previous day == 1) AND (Chargeable employees on present day <>1)”
And in the formula aggregation tab we are marinating Exception Aggregation as total value as of Employee count consider. (like above only)
With this formula, we are comparing present day chargeable employees with previous day chargeable employees and if any employee was there in the previous day count and if he is no there in the present day count it will show that employees count total.
According to the above cube data, it has to show outgoing count as 2.
Query output
So, if you execute the query the output will be display like
As of 01.10.2011, number of chargeable employees under Nokia profit center are 6.
Incoming Employee are 3 and outgoing employees are two.
If user want to see who are those employees moved out and moved in , he can further drill down with the employee number and the output will be like
Like this we can use Boolean operators in the BEX reporting and achieve the required output.
- SAP Managed Tags:
1 Comment
