Microsoft Business Solutions online community
It is currently Fri Jul 31, 2015 7:03 am

All times are UTC + 1 hour [ DST ]

Post new topic Reply to topic  [ 10 posts ] 
Author Message
 Post subject: How To create a report which is based on a temporary table?
PostPosted: Tue Jul 05, 2005 2:40 pm 
Site Admin

Joined: Sun Nov 07, 1999 8:01 am
Posts: 2171
Location: Wilrijk, Belgium
Country: Belgium (be)
How To create a report which is based on a temporary table?


Discuss this How To here.

 Post subject: Thanks
PostPosted: Tue Jul 05, 2005 2:54 pm 
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.

 Post subject: TmpItem Record
PostPosted: Tue Jul 05, 2005 2:59 pm 
Don't forget to set the Property for you Variable TmpItem to Temporary = Yes.

 Post subject: How to create a report based on a temporary table
PostPosted: Tue Jul 05, 2005 3:00 pm 
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.

 Post subject: A better way
PostPosted: Tue Jul 05, 2005 3:01 pm 
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..))"

 Post subject: how to sort\group the data in temporary table
PostPosted: Fri Jul 29, 2005 4:10 am 

Joined: Tue Mar 15, 2005 11:37 am
Posts: 28
Location: Sydney
Country: Australia (au)
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.


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."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";


 Post subject:
PostPosted: Wed Nov 30, 2005 2:53 am 

Joined: Wed Jan 19, 2005 11:55 am
Posts: 84
Location: West Port, Malaysia
Country: Malaysia (my)
I'm using temporary table in my reports, you can get them here, or here, or here.

Hope it helps.

Peter Ng

 Post subject:
PostPosted: Sun Dec 04, 2005 10:00 pm 

Joined: Tue Mar 15, 2005 11:37 am
Posts: 28
Location: Sydney
Country: Australia (au)

Thanks Peter for the post.



 Post subject:
PostPosted: Fri Jan 04, 2008 10:10 am 

Joined: Fri Jan 04, 2008 8:09 am
Posts: 2
Location: united states
Thanks peter so much,It's my first time to use temporary table. I think I come the right place.

 Post subject:
PostPosted: Wed Aug 06, 2008 6:51 pm 

Joined: Thu Jul 03, 2008 4:00 pm
Posts: 77
Location: Haiti
Country: Haiti (ht)

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

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

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.

Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 10 posts ] 

All times are UTC + 1 hour [ DST ]

Who is online

Users browsing this forum: No registered users and 1 guest

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to: