Regroup Data in RTF Template

Antoine KAIBER

Any idea ? I still havent found out how to do it with nested groups as ​ said and I don't want to be using table wizard actually because it doesn't seem to suit my need and I want to keep my tables how they are currently displayed.

Antoine KAIBER

Sorry i'm a newbie, what does "where 1=1" imply ?

Antoine KAIBER

Well I don't know if it's possible. Here is my "ITEM" Data Set :

select

itm.SHIPMENT_GID

, itm.STOP_NUM

, itm.STOP_ACTIVITY

, itm.ORDER_RELEASE_GID

, itm.ORDER_RELEASE_XID

, itm.OR_LINE_GID

,LTRIM(itm.OR_LINE_GID,itm.DOMAIN_NAME||'.') OR_LINE_XID

, itm.S_SHIP_UNIT_XID

, itm.ITEM_GID

, itm.ITEM_XID

, itm.ITEM_PACKAGE_COUNT

, coalesce(spu.TRANSPORT_HANDLING_UNIT_GID,'-') TRANSPORT_HANDLING_UNIT_GID

,coalesce(substr(TRANSPORT_HANDLING_UNIT_GID, instr(TRANSPORT_HANDLING_UNIT_GID,'.', 1)+1, length(TRANSPORT_HANDLING_UNIT_GID)),'-') TRANSPORT_HANDLING_UNIT_XID

, coalesce(spu.SHIP_UNIT_COUNT,0) SHIP_UNIT_COUNT

, coalesce(spl.PACKAGING_UNIT_GID,'-') PACKAGING_UNIT_GID

, coalesce(LTRIM(spl.PACKAGING_UNIT_GID,spl.DOMAIN_NAME||'.'),'-') PACKAGING_UNIT_XID

, coalesce(spl.PACKAGING_UNIT_COUNT,0) PACKAGING_UNIT_COUNT

, coalesce(spu.UNIT_WEIGHT,0)   UNIT_WEIGHT

, coalesce(spu.UNIT_WEIGHT_UOM_CODE,' ')   UNIT_WEIGHT_UOM_CODE

, coalesce(spu.TOTAL_GROSS_WEIGHT,0)   TOTAL_GROSS_WEIGHT

, coalesce(spu.TOTAL_GROSS_WEIGHT_UOM_CODE,' ')   TOTAL_GROSS_WEIGHT_UOM_CODE

, coalesce(spl.WEIGHT,0) PU_WEIGHT

, coalesce(spl.WEIGHT_UOM_CODE,' ') PU_WEIGHT_UOM

from SHIP_STOP_ITEM_BOV itm

left join s_ship_unit spu

ON itm.S_SHIP_UNIT_GID=spu.S_SHIP_UNIT_GID

left join s_ship_unit_line spl

ON itm.S_SHIP_UNIT_GID=spl.S_SHIP_UNIT_GID

How could I group it by TRANSPORT_HANDLING_UNIT_XID ?

Carlos Carvalho

Hello,

I a mgoing to repli with my 2nd post to this discussion:

why can´t you do a query like:

select item, sum(quantity) from table where 1=1 group by item;

?

and display it as a table in report?

Regards,

Carlos

Carlos Carvalho

Hello,

No prob.

That does not imply anything. Think of it just as a placeholder for any filter criteria you need to add to the query itself.

Regards,

Carlos

Carlos Carvalho

If you do not know, we don´t know either

We do not have your Database schema and tables to know the types of your columns, but seems to me you will have to make another data set for that info like(not sure if it works):

select

sum( itm.ITEM_PACKAGE_COUNT)

,sum( coalesce(spu.TRANSPORT_HANDLING_UNIT_GID,'-') TRANSPORT_HANDLING_UNIT_GID)

,coalesce(substr(TRANSPORT_HANDLING_UNIT_GID, instr(TRANSPORT_HANDLING_UNIT_GID,'.', 1)+1, length(TRANSPORT_HANDLING_UNIT_GID)),'-') TRANSPORT_HANDLING_UNIT_XID

from SHIP_STOP_ITEM_BOV itm

left join s_ship_unit spu

ON itm.S_SHIP_UNIT_GID=spu.S_SHIP_UNIT_GID

left join s_ship_unit_line spl

ON itm.S_SHIP_UNIT_GID=spl.S_SHIP_UNIT_GID

group by TRANSPORT_HANDLING_UNIT_XID

;

You´de better ask your dba (which as access to your database) and ask him that question:

How could I group it by TRANSPORT_HANDLING_UNIT_XID ?

Let us know about your findings with that Data set (if it does not work try to remove the second line of select statement).

Regards,

Carlos

Carlos Carvalho

With one IDE you would easily check for errors in the query and test if it was ok.

If you can run the report please provide the sample.xml generated output.

Thanks,

Carlos

Carlos Carvalho

run it in db IDE (sql plus or developer).

Carlos Carvalho

Perhaps not, but i bet there is more in that error than you posted.

You want us to help you, but do not provide full error stack and hope we imagine the issues?

Luckily i think it might be in the order by, but next time do a favor to all of us and post all the info we need to help you out:

select

sum( itm.ITEM_PACKAGE_COUNT)

,sum( coalesce(spu.TRANSPORT_HANDLING_UNIT_GID,'-') )TRANSPORT_HANDLING_UNIT_GID

,coalesce(substr(TRANSPORT_HANDLING_UNIT_GID, instr(TRANSPORT_HANDLING_UNIT_GID,'.', 1)+1, length(TRANSPORT_HANDLING_UNIT_GID)),'-') TRANSPORT_HANDLING_UNIT_XID

from SHIP_STOP_ITEM_BOV itm

left join s_ship_unit spu

ON itm.S_SHIP_UNIT_GID=spu.S_SHIP_UNIT_GID

left join s_ship_unit_line spl

ON itm.S_SHIP_UNIT_GID=spl.S_SHIP_UNIT_GID

group by coalesce(substr(TRANSPORT_HANDLING_UNIT_GID, instr(TRANSPORT_HANDLING_UNIT_GID,'.', 1)+1, length(TRANSPORT_HANDLING_UNIT_GID)),'-')

;

How about now?

Regards

Carlos

Carlos Carvalho

Hello,

Check if the following is still missing rigth parentisis:

select

sum( itm.ITEM_PACKAGE_COUNT)

,sum( coalesce(spu.TRANSPORT_HANDLING_UNIT_GID,'-') )TRANSPORT_HANDLING_UNIT_GID

,coalesce(substr(TRANSPORT_HANDLING_UNIT_GID, instr(TRANSPORT_HANDLING_UNIT_GID,'.', 1)+1, length(TRANSPORT_HANDLING_UNIT_GID)),'-') TRANSPORT_HANDLING_UNIT_XID

from SHIP_STOP_ITEM_BOV itm

left join s_ship_unit spu

ON itm.S_SHIP_UNIT_GID=spu.S_SHIP_UNIT_GID

left join s_ship_unit_line spl

ON itm.S_SHIP_UNIT_GID=spl.S_SHIP_UNIT_GID

group by TRANSPORT_HANDLING_UNIT_XID

;

and let know.

BR

Carlos