on 07-23-2014 7:36 PM
Hi,
I am working on a Bex 3.x query. I need to aggregate data in the following manner. Please see the example below
WBS Element | Employee | Hours | Total |
---|---|---|---|
WBS1 | EMP 1 | 10 | 35 |
WBS2 | EMP 2 | 12 | 34 |
WBS 3 | EMP 1 | 15 | 35 |
WBS 1 | EMP 3 | 17 | 17 |
WBS 2 | EMP 1 | 20 | 35 |
WBS 1 | EMP 2 | 22 | 34 |
Basically how I display the totals column is the challange. For every employee , the aggregate hours across any WBS element need to be shown on the totals column for the row that displays that employee.
Please tell me how I can configure this on the Bex query.
Thanks,
Shweta
Hi Shweta,
All you have to do is, create a new CKF "Total", put your KF for hours inside it, Click on the "Enhance" button at the bottom. Create exception aggregation as "summation" for reference characteristic "Employee". It will give you the output just the way you want.
Hope this helps.
Thanks,
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I tried this , I'm afraid it did not help. I got the 7.3 version on my machine. created CKF with exception aggrgn on Summation for ref char employee - the totals column gives me same output as hours column.
Basically I am trying to display the result of summation by employee on all the rows where the employee appears. This is not happening with your solutions I'm afraid.
there is another way of doing it - create a restricted key figure and set the WBS elements to ALL values (or simply exclude unassigned) and set that to be a Constant Selection.
The constant selection key figures are a bit tricky when you start slicing and dicing, but as long as you want to do the sum for the WBS per employee, this might do the trick
Regards
Hi swetha,
why you required last column total hours for employee?
in report level will give total aggregated value.
put the only employee in rows panel and put the wbs element in free char panel.
while performing the drill down and drill across you will get the aggregated values.
please read my replay problem will solve.
Thanks,
Phani.
Hi,
above scenario not working please fallow the below steps.
first put the WBS element and employee
first drag the WBS element, Employee to rows panel
select the WBS element, Employee-> go to query properties -> go to display tab -> select the result rows -> browse and select always display -> then save it query.
then perform the drill down drill acorrs options in RSRT or BEx analyzer.
Thanks,
Phani.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Just to explain - the total hours for EMP 1 = 10 (Row 1) + 15 ( Row 3 ) + 20 ( Row 5 ) = 45
Revising the table as below :
WBS Element | Employee | Hours | Total |
---|---|---|---|
WBS1 | EMP 1 | 10 | 45 |
WBS2 | EMP 2 | 12 | 34 |
WBS 3 | EMP 1 | 15 | 45 |
WBS 1 | EMP 3 | 17 | 17 |
WBS 2 | EMP 1 | 20 | 45 |
WBS 1 | EMP 2 | 22 | 34 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Shweta,
first drag the WBS element, Employee to rows panel
select the WBS element, Employee-> go to query properties -> go to display tab -> select the result rows -> browse and select always display -> then save it query.
check the report in RSRT -> now it will give total count of hours.
it is the good way to do.
we can perform the drill down we can see the values.
or
create the new formulas under column panel restricting the employee hours. ex - EMP1,EMP2,EMP3.
Thanks,
Phani.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.