Skip to main content
Solved

Operational Report development question

K
Hero (Customer)
Forum|alt.badge.img+10

Apps 7.5 had a cool enhancement to Operational Reporting called ‘iPrinting’ which was a UK customisation. It allowed additional data to be pulled into a report layout which could then be used by Report Designer.

This feature appeared to disappear by the time Apps 9 came along and I wondered whether anyone has come across this?

Custom Fields offer some assistance but what they do not provide is ‘sub queries’ of data.

e.g. Customer Invoices lines report the Lot/Batch Number of the invoice qty but I want to then show a break down of that quantity across the batches. This would require a sub-query of the inventory transaction history or something to show the separate lot ref and qty.

I used to be able to do this using iPrinting but I can’t see way to do it with custom fields.

Anyone got any ideas?

 

 

Best answer by Tomas Ruderfelt

It you want to create a custom field to show aggregated data you can use the Oracle SQL function LISTAGG.

https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030

 

A small example which shows all lines aggregated for a customer order:

SELECT listagg(order_no ||' '|| line_no ||' '|| rel_no ||' '|| catalog_no ||' '|| buy_qty_due, CHR(13)||CHR(10)) WITHIN GROUP (ORDER BY line_no,rel_no)
FROM customer_order_line
WHERE order_no = '*3031'

That will result in one line with one result column which has the following value for a CO with 3 lines:

*3031 1 1 1262753 12
*3031 2 1 1253908 10
*3031 3 1 1249877 5

In a report it might be better to create one CF for each column in the aggregated data since you have limited formatting options in SQL and characters have different width when using fonts in report designer.

View original
    Did this topic help you find an answer to your question?

    5 replies

    Tharindu
    Hero (Employee)
    • Tharindu
    • Hero (Employee)
    • 66 replies
    • May 20, 2020

    As far as I remember, The Report Rule Engine got the influence from the iPrinting and evolved but of course not everything was included.

    A
    Hero
    Forum|alt.badge.img+7
    • asanka
    • Hero
    • 69 replies
    • May 20, 2020
    KIMKIMANDREW wrote:

    Apps 7.5 had a cool enhancement to Operational Reporting called ‘iPrinting’ which was a UK customisation. It allowed additional data to be pulled into a report layout which could then be used by Report Designer.

    This feature appeared to disappear by the time Apps 9 came along and I wondered whether anyone has come across this?

    Custom Fields offer some assistance but what they do not provide is ‘sub queries’ of data.

    e.g. Customer Invoices lines report the Lot/Batch Number of the invoice qty but I want to then show a break down of that quantity across the batches. This would require a sub-query of the inventory transaction history or something to show the separate lot ref and qty.

    I used to be able to do this using iPrinting but I can’t see way to do it with custom fields.

    Anyone got any ideas?

     

     

    I have used iPrinting in 7.5 and have pretty much converted all and then some to app9 mostly using custom fields. Not sure what the sub query option did and how it presented on the report layout. Did it come across as a table? You can still create a table-like structure using either multiple custom fields, or a single custom field having multiple lines (e.g. tab delimited data).

    The following is an example of creating something using 3 custom fields. 

     

    The below using a single cf having multiple rows.

     

    ~hope is a good thing, maybe the best of things, and no good thing ever dies...
    T
    Superhero (Employee)
    Forum|alt.badge.img+19
    • Tomas Ruderfelt
    • Superhero (Employee)
    • 488 replies
    • Answer
    • May 21, 2020

    It you want to create a custom field to show aggregated data you can use the Oracle SQL function LISTAGG.

    https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030

     

    A small example which shows all lines aggregated for a customer order:

    SELECT listagg(order_no ||' '|| line_no ||' '|| rel_no ||' '|| catalog_no ||' '|| buy_qty_due, CHR(13)||CHR(10)) WITHIN GROUP (ORDER BY line_no,rel_no)
FROM customer_order_line
WHERE order_no = '*3031'

    That will result in one line with one result column which has the following value for a CO with 3 lines:

    *3031 1 1 1262753 12
*3031 2 1 1253908 10
*3031 3 1 1249877 5

    In a report it might be better to create one CF for each column in the aggregated data since you have limited formatting options in SQL and characters have different width when using fonts in report designer.

    K
    1 person likes this
    • K
      KIMKIMANDREW
    pwlm
    Hero (Partner)
    Forum|alt.badge.img+11
    • pwlm
    • Hero (Partner)
    • 70 replies
    • May 21, 2020
    Tharindu wrote:

    As far as I remember, The Report Rule Engine got the influence from the iPrinting and evolved but of course not everything was included.

    @KIMKIMANDREW As Tharindu has pointed out - IFS cherry-picked the features from the iPrinting VAP to carry into later versions. The capability that you have described was dropped sadly.

    My suggestion would be to look at replacing the Operational Report, Report Designer Layouts with Crystal Report Layouts - might not be attractive if you’ve already started to uplift your Apps 7.5 layouts but Crystal is so much more flexible than Report Designer.  Just food for thought...

    Pete McDermott - https://linkedin.com/in/pwlmcdermott
    K
    Hero (Customer)
    Forum|alt.badge.img+10
    • KIMKIMANDREW
    • Author
    • Hero (Customer)
    • 82 replies
    • May 21, 2020

    Thanks to all who contributed  - I didn’t think to use LISTAGGR in a customer field :relaxed:

    Reply


    Terms & ConditionsCookie settings

    Cookie policy

    We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

     
    Cookie settings