Aggregating values across rows and columns on a Bex Query

[Former Member]

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

[Former Member · Accepted solution]

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,

[Former Member · Accepted solution]

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.

[pavel_afanasiev · Accepted solution]

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

[KodandaPani_KV · Accepted solution]

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.

[Former Member · Accepted solution]

Thankyou for your help. I created a RKF with constant selection and that was able to meet my requirement.

[KodandaPani_KV]

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.

[Former Member]

I don't see this option in query properties -

select the result rows -> browse and select always display ->

I am using 3.X - what am I missing ?

[KodandaPani_KV]

Hi,

is there query properties or check object properties.

in 3.x it will appear in down please check the one time all options.

i am working 7.3 version.

[Former Member]

there are query properties on 3.x  but not the options you mentioned. Please see the screenshot attached and please point me to the selections you mentioned.

[KodandaPani_KV]

Hi,

i am checking the RSRT for query same option is there in 3.X version.

create the new formula -> go to edit mode -> then check the properties.

please cross check all options.

[Former Member]

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

[KodandaPani_KV]

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.