make selection of your query result

i will try to explain what i want with an example

if have a query that returns

user code amount

user1 A 10

user1 B 100

user1 C 10

user2 B 50

user2 D 10

user3 A 10

user3 C 20

what i want in my report is the following as result

Sum of code A and C Sum of code B and D

user1 20 100

user2 60

user3 30

so can you make variables on the scope user that do a selection on the 'code'

in words like a variable that give me the sum of the amount where code in A and C in the scope user

Maybe this is not the best choice but it works.

We have:

Usuario Code Amount
--

Alejandro C 200
Erika A 50
Erika B 50
Karla D 100
Marcos A 100
Marcos B 50
Marcos C 25
Marcos D 70
Pedro A 100
Pedro C 70

and the code Returns

usuario F_Group S_Group
- - -
Alejandro 200 0
Erika 50 50
Karla 0 100
Marcos 125 120
Pedro 170 0

Code Snippet

--declare the parameters
declare @.F_group_1 varchar(2)
declare @.F_group_2 varchar(2)
declare @.S_group_1 varchar(2)
declare @.S_group_2 varchar(2)
--set value to parameters or define scoupe
select @.F_group_1='A',
@.F_group_2='C',
@.S_group_1='B',
@.S_group_2='D'
--sum both result
select qry.usuario, sum(qry.F_group) F_Group, sum(qry.S_group) S_Group
from (
-- sum the first group and in the 2nd we put 0
select usuario, sum(amount) F_group , 0 as S_group
from tabla
where code in (@.F_group_1, @.F_group_2)
group by usuario
union
-- sum the 2nd group and in the 1st we put 0
select usuario, 0 as F_group, sum(amount) S_group
from tabla
where code in (@.S_group_1, @.S_group_2)
group by usuario
) as qry
group by qry.usuario

Regards,

Marcos

|||

your solution is done at/in the query / datasource and i thought about that too

but is there a solution possible in report itself ?

like in businesobject you can make variables who are sum of an amount in a scope and with a where condition

on the report

|||

Krisje,

If I understand you correctly the following should work.

1. Use a table with 3 columns

2. Insert a group and set it to group on user

3. On the group row set column 1 expesression to fields!user

4. On the group row set column 2 to

=sum(iif(Fields!code.Value="A" or Fields!code.Value="C",Fields!amount.Value,0))

5. On the group row set column 2 to

=sum(iif(Fields!code.Value="A" or Fields!code.Value="C",0,Fields!amount.Value))