How To create a report which is based on a temporary table?

Administrator

How To create a report which is based on a temporary table?

http://www.mibuso.com/howtoinfo.asp?FileID=6

Discuss this How To here.

Anonymous

I need use a temp table in report, and this will help me out. Its rather stupid method (like other things in Navision), but functional. I was trying RecordRef on temp table and ist method SETTABLE (recordref.SETTABLE(record of dataitem)) but its not functioonal, iteresting is, GETTABLE work normally.

Anonymous

Don't forget to set the Property for you Variable TmpItem to Temporary = Yes.

Anonymous

I found this very useful though I must admit that I have not yet fully mastered how reports that make use of the integer table are designed. I can modify the purchase invoice to be something else but I cannot design my own 'integer based report' without getting into infinite loops! So thanks very much for this useful technique.

Anonymous

This works, but uses the COUNT function which is not recommended (it may slow down the process.) You should better take a look of report 111 ("Customer - Top 10 List") in the standard application. Pay attention that the property "DataItemTableView" has value "SORTING(Number) WHERE(Number=FILTER(1..))"

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;

itspeter

I'm using temporary table in my reports, you can get them here, or here, or here.

Hope it helps.

rodbernardo

8)

Thanks Peter for the post.


Regards,

Rodel

rolaee

Thanks peter so much,It's my first time to use temporary table. I think I come the right place.

redhotmustang

Hi,

Maybe I did not understand this "How To" exactly, but instead of using a FOR cycle, I found it more usable (and only works like this) to do it like this

recStudentsRegistrations.SETRANGE("Class Code",varClass);
if recStudentsRegistrations.find('-') then begin
   repeat
   tmpStudents.Init;
   tmpStudents.No:= recStudentsRegistrations.No;
   tmpStudents.Name := recStudentsRegistrations.Name;
   tmpStudents.INSERT;
   until recStudentsRegistrations.next = 0;
end;

I needed to create a report with all the students in one class listed.
The report was based on the Class table, which did not had the students registered there. The students were listed on the StudentsRegistrations table, which had a "Class Code" field, StudentNo and Student Name field.

The report's required field was the class code only of the Class table.

It works.