Solved

Operational Report development question

  • 20 May 2020
  • 5 replies
  • 397 views
K
Rank
Userlevel 4
Badge +9

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?

 

 

icon

Best answer by Tomas Ruderfelt 21 May 2020, 10:10

View original

5 replies

Tharindu
Rank

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
Rank
Userlevel 4
Badge +7

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
Rank
Userlevel 7
Badge +19

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.

pwlm
Rank
Userlevel 4
Badge +11

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
Rank
Userlevel 4
Badge +9

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

Reply


Terms & ConditionsCookie settings