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

AdministratorAdministrator Member, Moderator, Administrator Posts: 2,495
edited 2008-08-06 in How Tos section
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.

Comments

  • AnonymousAnonymous Guest Posts: 137
    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.
  • AnonymousAnonymous Guest Posts: 137
    Don't forget to set the Property for you Variable TmpItem to Temporary = Yes.
  • AnonymousAnonymous Guest Posts: 137
    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.
  • AnonymousAnonymous Guest Posts: 137
    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..))"
  • rodbernardorodbernardo Member Posts: 34
    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;
    Rodel Bernardo
    Pressure is an opportunity.
  • itspeteritspeter Member Posts: 105
    I'm using temporary table in my reports, you can get them here, or here, or here.

    Hope it helps.
    Regards,
    Peter Ng
  • rodbernardorodbernardo Member Posts: 34
    8)

    Thanks Peter for the post.


    Regards,

    Rodel
    Rodel Bernardo
    Pressure is an opportunity.
  • rolaeerolaee Member Posts: 2
    Thanks peter so much,It's my first time to use temporary table. I think I come the right place.
  • redhotmustangredhotmustang Member Posts: 91
    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.
    Redcodestudio: Web Development, FLASH & Webdesign (and a little NAV, in the future)
Sign In or Register to comment.