Skip to Content

Calculation Context With Merged Bex Queries

Nov 04, 2016 at 02:44 PM


avatar image

I am using two Bex queries in a WebI report.

Query 1 has: Employee, Customer, Picking Hours, and Cases Picked

Query 2 has: Customer, Total Picking Hours, and Total Cases Picked

The purpose of the report is to compare each employee's cases picked per hour with the cases picked per hour for all the customers they work with (for all employees for the customers). The queries are merged by Customer.

Here is a screenshot of the data displaying in WebI:

The "Cases per Hour" is just the employee's "Cases Picked" divided by "Picking Hours". The values for customer are for the total of all employees that work with that customer.

Each detail row displays correctly. However, the customer aggregated values are not correct; it is just giving the overall total, and repeating that for each employee. For example, Employee 1 should show 2697.92 for the total customer picking hours, 844044 for the total customer cases, and 312.84 for the total customer cases per hour. The end goal is to just show one row for each employee, without the customer details; but I'm assuming that part will be easy once I can get the total rows to work.

I have tried various combinations of In, ForAll, and ForEach; but have had no luck. I've also come across some posts about using the ForceMerge function; but when I try that, the values just show "#TOREFRESH" even after refreshing several times.

10 |10000 characters needed characters left characters exceeded

For some reason, "SAP Knowledge Acceleration, version for SAP BusinessObjects Web Intelligence " was the only option that came up when I typed "Web Intelligence" for the primary tag. If there is a way to change that, please let me know. Thanks.

* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Nick VanLent Nov 15, 2016 at 04:01 PM

I ended up doing a break on the employee, with a running sum on the hours, and a max of the running sum in the employee break footer. Then used the outline feature to collapse the detail rows, so that only one row shows for each employee.

10 |10000 characters needed characters left characters exceeded
Nov 04, 2016 at 07:56 PM

try like this to display hours for each employee.

=[your forcemerge hour variable] in ([Customer])

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thanks; that is getting me closer. When using that, the ForceMerge Hours variable is filled in for every row. However, when I use that in the original screenshots, it gives me a #MULTIVALUE message when displaying at the Employee level; and if I put a SUM around that, then it just shows the same grand total for every employee. I'll keep playing with it though.

Nick VanLent Nov 04, 2016 at 05:53 PM

It turns out that the ForceMerge function is the key. The Bex query has a field that already does the cases divided by hours; and I was using ForceMerge on that, which was producing the "#TOREFRESH". If, instead, I do ForceMerge on the Cases field, and ForceMerge on the Hours field, I can then do a formula to divide those two, and get the 312.84 that I was looking for.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

This is getting even more complex. When I put in my previous "answer", I was filtering the first query to just 3 employees. When I run it without the employee filter, the formulas no longer work correctly. For some reason, the ForceMerge function is causing the values to only show for one of the employees for each customer.