mibuso.com

Microsoft Business Solutions online community
It is currently Mon Oct 20, 2014 3:04 pm

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 
Offline
Site Admin

Joined: Sun Nov 07, 1999 8:01 am
Posts: 2070
Location: Wilrijk, Belgium
Country: Belgium (be)
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.


Top
 Profile  
 
 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.


Top
  
 
 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.


Top
  
 
 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.


Top
  
 
 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..))"


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

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.

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;


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

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.

_________________
Regards,
Peter Ng


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

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

Thanks Peter for the post.


Regards,

Rodel


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jan 04, 2008 10:10 am 
Offline

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.


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

Joined: Thu Jul 03, 2008 4:00 pm
Posts: 77
Location: Haiti
Country: Haiti (ht)
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

Code: Select all
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.


Top
 Profile  
 
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: