Report grouping question

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.

Thanks

Try to create report with repprt wizard and then do the . changes . accordingly

Thank you for your response, Amol. The wizard wouldn’t help here as I did try to do it already. The problem for me in this design is the table with Receipts has non-unique receipts numbers so when I link Sales Invoice Header table to the Receipts it gives several the same receipts per invoice so when I link it further to other tables it gives duplication. So, I’d need to get distinct receipts from the Receipt table. If I did it in SQL I’d probably create a temp table @tblTempReceipt and did SELECT DISTINCT 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 duplication. So, I need to do something similar in NAV. Please, advise if you possibly know how I could do it.

thanks

Ok, I created a temp table TempReceipts (Subtype: ‘Receipts Table’, Temporary: Yes) and Integer in Gloabal Variables.

I also setup keys on the ‘Receipts Table’ as ‘Posted Invoice No.’ (the same as No. in ‘Sales Invoice Header’, ‘Document No.’ (represent ‘Receipt No.’ in other tables), and Date. I entered these keys on separate lines and also as three together. The combination of the keys however is not giving unique combination in the ‘Receipts Table’ table.

I’m trying to implement a code as below but the Temp table seems as empty anyway as if I put TempReceipts.”Document No.” on an integer body section in the Report layout there is nothing in that section.

Please, help.

I do have DataItems now as below:

‘Sales Invoice Header’

Integer

‘Transaction Header’

‘Transaction Sales Entry’

//=====================================

Integer - OnPreDataItem():

//=====================================

TempReceipts.INIT

TempReceipts.DELETEALL

TempReceipts:=”Receipts Table”;

TempReceipts.SETCURRENTKEY(‘Posted Invoice No.’, ‘Document No.’, Date);

IF TempReceipts.FINDFIRST THEN
REPEAT
TempReceipts.SETRANGE(“Posted Invoice No.”, TempReceipts.“Posted Invoice No.”);
TempReceipts.SETRANGE(“Document No.”, TempReceipts.“Document No.”);

TempReceipts.SETRANGE(Date, TempReceipts.Date);

TempReceipts.FINDLAST;

TempReceipts.SETRANGE(“Posted Invoice No.”);
TempReceipts.SETRANGE(“Document No.”);

TempReceipts.SETRANGE(Date);
UNTIL TempReceipts.NEXT = 0;

TempReceipts.RESET;

SETRANGE(Number,1, TempReceipts.COUNT);

//============================================

Integer – OnAfterGetRecord:

//============================================

IF Number = 1 THEN

TempReceipts.FIND(’-’)

ELSE

TempReceipts.NEXT;

//============================================

Transaction Header – OnPreDataItem()::

SETRANGE(“Receipt No.”, TempReceipts.”Document No.”);

I don’t also have any idea how I could link the Integer table to the Up table (‘Sales Invoice Header’) and to the Down tables that are in the DataItems (‘Transaction Header’ and ‘Transaction Sales Entry’).

The Integer temp table should be somehow linked to “Transaction Header” table via has “Receipt No.” and “Document No.”. Then the “Transaction No.” or “Receipt No.” fields could be used to link further down to the ‘Transaction Sales Entry’ table.

Thanks

Ok, I created a temp table TempReceipts (Subtype: ‘Receipts Table’, Temporary: Yes) and Integer in Gloabal Variables.

I also setup keys on the ‘Receipts Table’ as ‘Posted Invoice No.’ (the same as No. in ‘Sales Invoice Header’, ‘Document No.’ (represent ‘Receipt No.’ in other tables), and Date. I entered these keys on separate lines and also as three together. The combination of the keys however is not giving unique combination in the ‘Receipts Table’ table.

I’m trying to implement a code as below but the Temp table seems as empty anyway as if I put TempReceipts.”Document No.” on an integer body section in the Report layout there is nothing in that section.

Please, help.

I do have DataItems now as below:

‘Sales Invoice Header’

Integer

‘Transaction Header’

‘Transaction Sales Entry’

//=====================================

Integer - OnPreDataItem():

//=====================================

TempReceipts.INIT

TempReceipts.DELETEALL

TempReceipts:=”Receipts Table”;

TempReceipts.SETCURRENTKEY(‘Posted Invoice No.’, ‘Document No.’, Date);

IF TempReceipts.FINDFIRST THEN
REPEAT
TempReceipts.SETRANGE(“Posted Invoice No.”, TempReceipts.“Posted Invoice No.”);
TempReceipts.SETRANGE(“Document No.”, TempReceipts.“Document No.”);

TempReceipts.SETRANGE(Date, TempReceipts.Date);

TempReceipts.FINDLAST;

TempReceipts.SETRANGE(“Posted Invoice No.”);
TempReceipts.SETRANGE(“Document No.”);

TempReceipts.SETRANGE(Date);
UNTIL TempReceipts.NEXT = 0;

TempReceipts.RESET;

SETRANGE(Number,1, TempReceipts.COUNT);

//============================================

Integer – OnAfterGetRecord:

//============================================

IF Number = 1 THEN

TempReceipts.FIND(’-’)

ELSE

TempReceipts.NEXT;

//============================================

Transaction Header – OnPreDataItem()::

SETRANGE(“Receipt No.”, TempReceipts.”Document No.”);

I don’t also have any idea how I could link the Integer table to the Up table (‘Sales Invoice Header’) and to the Down tables that are in the DataItems (‘Transaction Header’ and ‘Transaction Sales Entry’).

The Integer temp table should be somehow linked to “Transaction Header” table via has “Receipt No.” and “Document No.”. Then the “Transaction No.” or “Receipt No.” fields could be used to link further down to the ‘Transaction Sales Entry’ table.

Thanks

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.

hi Alexander,

i am also facing this problem ,but i am using only one dataitem, so how can i group several fields

Hi Avinash,

You could use a GroupTotal field please see below or indentation. If it’s indentation then you’d need to bring another data item e.g. you could have a Customer and a Customer Ledger Entry. The indentation is a easer way. But GroupTotal must be used if the field that is to be grouped on is not related to another table.

The use of the GroupTotalFields:

The GroupTotalFields property is the key for grouping. It is used to specify fields to be grouped on by the report. It enables printing special sections which usually are used to show subtotals in the report. Although, this property has nothing to do with totals or indentation, the name of the property is somewhat misleading.

To correctly use this field, developers must be aware of the following:

  • The fields specified in this property must also be in the key that is specified in the DataItemTableView property.

  • If the DataItemTableView does not have the correct key, the fields specified in the GroupTotalFields property are ignored by the report.

  • Special sections must be created in the Section Designer. Grouping causes these sections to be printed at the correct time.

    I hope this could help.

Thanks Alaxander,

But after that i have to write some code also?,and one more thing can i use more than 1 key in data item table view property?and which field i have to grouping that have to mention in the group total fields property?

Ex-

Data item table view - Sorting(Item no,Entry Type, Posting Date)

Group total - Item no,Entry Type, Posting Date

Is it write?