report design question

donit

Hello,

I need to create a report with several groupings in a Classic Client (NAV 2009).

The structure could is as follows:

‘Sales Invoice Header’

‘Table with Receipts’ (Link via [Invoice No_]; The [Receipt No_] is not unique and can be repeated several times in the table so I’d need to group it)

‘Transaction Header’ (Linked via [Receipt No_])

‘Trans. Sales Entry’ (Linked via [Transaction No_])

‘Trans. Payment’ (Linked via [Transaction No_])

‘Trans. Income/Expense’ (Linked via [Transaction No_])

It’s also possible to skip ‘Transaction Header’ and link the three with the same indention tables via [Receipt No_].

I tried to create the report with a Report Designer only using the Data Items with properties DataItemLink, TotalFields, GroupTotalFields, and adding a Key into a table. I could handle with a first indentation but further indented tables are giving me duplication. So, I’d probably need to use a temporary table variable ‘I’ to group firstly then to link further indented table with details. Please, advise how could I resolve it or from where I could start.

======================
I believe if I could get a temp table from the ‘Table with Receipts’ table with a distinct or grouped InvoiceNo and ReceiptNo fields I would resolve the problem. I would link 'Sales Invoice Header' table to the temp table and then to all further tables and there wouldn't be any duplication based on ReceiptNo.
If I did it in SQL I'd probably create a temp table @tblTempReceipt and did SELECT DISTINCT InvoiceNo, ReceiptNo into that temp table then I would do SELECT ... FROM 'Sales Invoice Header' SIH INNER JOIN @tblTempReceipts R ON SH.ReceiptNo=R.ReceiptNo INNER JOIN .... that wouldn't give me a needed result. So, I need to do something similar in NAV. Please, advise if anybody possibly know how I could do it.
==========================
Thanks

donit

It's solved. As it's only a few receipts for an invoice i did create a loop to check for a previouse value. Then I used SETRANGE to go from a one temp table to another nested one.