How to sort\report grouping in temporary table

rodbernardo

Hello guys,

I am new to Navision.

I have a report which required to provide report grouping of Sales by Sales Person.

I did created a temporary table, I sucessfully linked the 3 tables, the Item Ledger Entry, Value Entry and Sales Invoice Header.

With the temporary table I have the Invoice No., Customer No., Sales Person, Item, Posting date, amount and quantity.

Now, how can I sort\grouping in the report using the temporary table.

I wanna group the Sales report by Sales Person, Customer and Item.

Please help and thanks.

Rodel
Australia



The following is the code that I created for temporary table.


DataItem: Integer

Global C/AL Variables:
Item Ledger Entry - record
Temp Item Ledger Entry - record
Sales Invoice Header - record
Value Entry - record

//only sales transaction
ItemLedgerEntry.SetFilter("Entry Type",'Sale');

FOR i := 1 TO ItemledgerEntry.Count DO BEGIN
tmpItemLedgerEntry.INIT;
tmpItemLedgerEntry."Entry No." := ItemLEdgerEntry."Entry No.";
tmpItemLedgerEntry."Item No." := ItemLEdgerEntry."Item No.";
tmpItemLedgerEntry."Source No.":= ItemLedgerEntry."Source No.";

// to get the document no. or invoice no. from value entry table
ValueEntry.SetFilter(ValueEntry."Item Ledger Entry Type",'Sale');
ValueEntry.SetFilter(ValueEntry."Expected Cost",'No');
ValueEntry.SetFilter(ValueEntry."Source Type",'Customer');
ValueEntry.SetFilter(ValueEntry."Item Ledger Entry No.",ItemLedgerEntry."Entry No.");

if ValueEntry.Find('-') Then
tmpItemLedgerEntry."Document No." := ValueEntry."Document No.";

// to get the sales person from sales invoice header table
SalesInvoiceHeader.SetFilter("No.",ValueEntry."Document No.");

if SalesInvoiceHeader.Find('-') Then
tmpItemLedgerEntry.Description := SalesInvoiceHeader."Sales Person";

tmpItemLedgerEntry.INSERT;
END;

Albertvh

Hi

Create a key on your temp table with the sorting order that you require
eg Salesperson code,customer no.,item no. then on the dataitem of your temp table add these fields in the Properties GroupTotalFields. Hope this points you in the right direction.

Albert

rodbernardo

<Create a key on your temp table with the sorting order that you require
<eg Salesperson code,customer no.,item no. then on the dataitem of <your ,temp table add these fields in the Properties GroupTotalFields. <Hope this points you in the right direction.

Thanks for the reply Albert.

I can create\set the key in the temporary table.

But I am not able to add the key\fields in the Properties of GroupTotalFields, because the DataItem that I used is Integer for temporary table. And the only available field is "Number".

Is that mean I have to add the Item Ledger Entry table at the top of Integer (temporary table) in DataItem?

I really appreciate your help.

Thanks,

Rodel

Albertvh

Hi Rodel
Set your tmpledgentry table as a dataitem but not indented or you could do the insert on the prereport section of the report so you wouldn't need the integer dataitem. I take it you have added the tmpitemledger as a variable so you would have to remove that variable. Your report would look something like this

DataItem Name
TmpItemLedger <TmpItemLedger>

You can then add to the properties etc. You may have to add the Group Header and Footer sections to your report and put in the appropriate code to print these sections. eg
GroupFooter section on PreSection
CurrReport.SHOWOUTPUT := CurrReport.TOTALSCAUSEDBY = FIELDNO("Salesperson Code");

Good Luck

Albert

rodbernardo

It's work!

I added the Item Ledger Entry in the DataItem - top of temp. table.

I didn't indent, and I just add some code into the presection of header and footer, done and done!


Thanks for your help.