HELP!! Calculation/Condition Issue

[arti_z]

Hi

I’m new to this and I have tried to find a solution on the forum but haven’t had any luck yet.

I have a result set that comes from a SQL statement that looks simplified like this:

 

cl-id |order id | grouporder id | value

================================

1 |1 |NULL |5

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

2 |2 |NULL |4

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

3 |3 |NULL |3

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

1 |4 |1 |1

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

2 |5 |1 |8

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

3 |6 |1 |2

 

That means some orders can go in as client individual orders and some can be done as group orders. In this case we have 3 individual orders and 1 group order!!

 

I want the report to look like this :

cl-id |order id | grouporder id | value

================================

1 |1 |NULL |5

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

2 |2 |NULL |4

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

3 |3 |NULL |3

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

--- |--- |1 |11

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

 

so for each individual order print a row and for the group order, adding the values for each row for that specific group and then printing it as 1 row.

 

This logic requires to be somewhere and I am not sure where to put it…I have been using iReport to create the base report and then been trying to tweak the XML stuff for conditions etc but I am lost here.

ANY help is appreciated!!

Thanks a bunch

Arti

[csbac]

Hi!

If I see it right, you can just put it into the SQL query ...

 

Code:

select "cl-id", "order id", NULL, value
from ...
where "grouporder id" is null
union
select '---', '---', "grouporder id", sum(value)
from ...
where "grouporder id" is not null
group by "grouporder id"

 

Hope this helps,

Sebastian

[arti_z]

Thanks Sebastian

 

I actually realized that too late last night...need to polish up my SQL instead of sending stupid q's to this forum.

Thanks anyway

/A