Oracle Analytics Cloud and Server

Products Banner

Regroup Data in RTF Template

Received Response
347
Views
25
Comments

Summary

Regroup Data in RTF Template

Content

Hello,

I'm working on a template with data that I want to regroup when it is similar.

Example of what I currently have :

I would like to regroup the quantites for SLI---2112 and the quantities for SLI---0770 in order to have only two lines in the synthesis.

Fyi, I currently in my RTF have a For-end loop :

Thanks!

Antoine

Answers

  • Carlos Carvalho
    Carlos Carvalho ✭✭✭✭✭

    Hello,

    Not sure of what you want exactly.

    The desired final output would be a line with:

    SLI---2112                         11

    and other line with:

    SLI---0770                         46

    ?

    Can you clarify, pls (maybe provide an accurate example).

    Regards

    Carlos

  • timdexter
    timdexter ✭✭✭✭✭

    Hi Antoine

    You need to use a group left layout in word and a for-each-group command to achieve this.

    Word is not great at nested tables which is what you need. Your best approach will be to use the report wizard in the Word BIP plugin. It will get you the layout and the fields you need. The plugin does not use nested tables but a series of variables and xpath commands to get the data to summarize correctly by row.

    I would create it outside of the main template first to get it working as needed. You will then need to embed it inside your main template and modify the fields to make it work correctly.

    Its a case of playing with it a little to get it working. If I get time I will try and create a sample for you

    Tim

  • Carlos Carvalho
    Carlos Carvalho ✭✭✭✭✭

    Hello ​,

    Thanks for clarify.

    So if that is the case, you need a sum, not a group(or perhaps the group is only to 'aggregate' the sum of results).

    I do not know if ​ agrees with me, but you would do a query like:

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

    Although it can also be done in msword.

    If i am seeing the whole thing right...

    Regards,

    Carlos

  • Antoine KAIBER
    Antoine KAIBER ✭✭✭✭✭

    Ok thanks, that would be really helpful, I will try to understand what you wrote on my part to start.

    managed BEAN  yes that's exactly what I mean :

    SLI---2112                         11

    and other line with:

    SLI---0770                         46

  • Antoine KAIBER
    Antoine KAIBER ✭✭✭✭✭

    I've tried doing this but it doesn't seem to work...

    ​, why can't I use a nested group on word ? I'm currently trying to work on that but can't seem to merge the data...

  • Carlos Carvalho
    Carlos Carvalho ✭✭✭✭✭

    Hello,

    BTW, have a look at: ,it might be helpful.

    Regards,

    Carlos

  • Carlos Carvalho
    Carlos Carvalho ✭✭✭✭✭

    Hello ,

    Can you please provide the wordLayout.rtf and sampleData.xml files, pls?

    Regards,

    Carlos

  • Antoine KAIBER
    Antoine KAIBER ✭✭✭✭✭

    Hello Managed Bean,

    How can I do that on this forum ? I don't see this option :

    But i've seen people do it so this option must exist.

  • Antoine KAIBER
    Antoine KAIBER ✭✭✭✭✭

    Hello Tim,

    Thanks for you advice on the report wizard, I actually tried using the pivot table as it seems to be more suited.

    I managed to have this result :

    by making this pivot table in my rtf :

    I still have a lot of issues but at least it merges data.

    Some of the issues that this pivot table is causing are that :

    • It merges THU's of the same stop number in case of multi shipment (for example on the screen it's written SLI 0770 = 3  because in stop 1 of shipment A we have 2 SLI 0770 and in shipment B we have 1 SLI 0770.
    • displays all the THU's that are in the shipments, even if there is 0 quantity for the concerned stop (example of the screen where MAN 9136 = 0 because MAN 9136 exists for another stop number)

    • Doesn't calculate small THU's, it currently calculates the number of THU's

    PS: I could send those attachments by mail as I do not have the @attach function in the advanced editor.

  • Carlos Carvalho
    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
    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
    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

  • Antoine KAIBER
    Antoine KAIBER ✭✭✭✭✭

    Because I've tried but I can't manage to make a group by in the SQL, I don't think it's this simple

  • Antoine KAIBER
    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 ?

  • Antoine KAIBER
    Antoine KAIBER ✭✭✭✭✭

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

  • Antoine KAIBER
    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.

  • Carlos Carvalho
    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
    Carlos Carvalho ✭✭✭✭✭

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

  • Carlos Carvalho
    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
    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

  • Antoine KAIBER
    Antoine KAIBER ✭✭✭✭✭

    What do you call an IDE ?

    I have an SQL Execution Interface in Oracle where I can try this query but it says :

    java.rmi.AccessException: [EJB:010160]Security violation: User DBA.CONFIGDBA has insufficient permission to access EJB type=<ejb>, application=GC3App, module=SqlSession.jar, ejb=SqlSessionHome, method=execute, methodInterface=Remote, signature={java.lang.String,java.lang.Object[]=null}.

    when I try to test it.

    I could send you the XML, what is your email ? Since I can't attach any file even in advanced mode

  • Antoine KAIBER
    Antoine KAIBER ✭✭✭✭✭

    My internet is very slow atm so if i can avoid downloading SQL Dev it's better. I tried to run SQL plus with a tuto but I get "'sqlplus' is not recognized as an internal or external command," in cmd, do you have an idea why ?

    Edit: I created a link and I can now launch the report but it doesn't change anything, there is no grouping by.

    I know this should be easier doing it directly in the RTF so if anyone has an idea about nested groups I'm all ears, thank you.

  • Antoine KAIBER
    Antoine KAIBER ✭✭✭✭✭

    Well, appart from the ORA number, that was the only error message I got:

    image

    With your new SQL the Data set can be created (G_1) :

    image

    But when I run the report it says "report not valid"  and I also get this message :

    ORA-01722: invalid number --

    I don't know if it's not coming from the fact that I didnt use links like for other data sets (Data sets are at leats linked by Shipment_GID I think, I will try)

  • Antoine KAIBER
    Antoine KAIBER ✭✭✭✭✭

    Weirdly I get "TRANSPORT_HANDLING_UNIT_XID": invalid identifier

  • Antoine KAIBER
    Antoine KAIBER ✭✭✭✭✭

    Well I posted this :

    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

    but it says "missing right parenthesis".  I already asked my DBA who said it should be difficult and he was more ok with the idea of doing the group by in the RTF I think. I will see again with him when I can.