Context in merged queries (foreach/forall) sum faillure

358 Views

Hey guys.

I encounter current problem:

I have 2 query's joined (Q1,Q2) on contract ID , item A and item B.

They contain information about buildings,contracts and revenue. A building most of the time has multiple contracts (imagine parkinspots)

What i want to achieve is a resultline per building that summarizes all revenue of the contracts per building.

It works if i put in the dimensions (property ID, contract ID, revenue) ten i get the sum of the revenue per contracts for the whole building.

property ID, contract ID, revenue

-----------------------------------------------

building A contract 1 sum1

contract 2 sum 2

contract 3 sum 3

building B

contract 1 sum1

contract 2 sum

What I want to achieve is

Building A Sum1

Building B Sum 2

So I remove contract id from the dimensions, I create a variable test=sum([revenue] foreach ([contract id]))

then the i receive following result

Building A TOTAL SUM

Building B TOTAL SUM

..

the query repeats every line the complete sum of the revenue of ALL the contract and all the buildings.

I've tried all possible combinations:

sum([revenue]) foreach ([contract id])

sum([revenue] foreach ([contract id] in ([property id])))

even forall (even if i know it's incorrect

sum([revenue]) forall ([contract id])

sum([revenue] forall ([contract id] in ([property id])))

sum([revenue]) in ([property id]; [contract id])

sum([revenue]) in ([contract id] in ( [property id]; [contract id]))

....

Can someone help me see the light?

Logically for me it doesn't make any sens why it's not working...

Thanks in advance

KR

Bram