Report Based On Temp Table Not Working

samantha73samantha73 Member Posts: 96
Hi All
I have a report based on a temporary table (TableType = Temporary) in BC SaaS v19. If I comment out the table type = temporary then all works, however temporary table does not provide nay results (empty dataset)
table 60030 SalesInvoiceCreditTemp
{
    Caption = 'Sales Invoice & credit Temp';

   [b] TableType = Temporary[/b];//Not working


    fields
    {
        field(60010; Key1; Integer)
        {
            Caption = 'Key';
            AutoIncrement = true;

        }
        field(60020; DocumentType; Code[20])
        {
            Caption = 'Document Type';
        }
        field(60030; DocumentNo; code[50])
        {
            Caption = 'Doc No.';
        }
        field(60040; PostingDate; Date)
        {
            Caption = 'Posting Date';
        }
        field(60050; ExternalNo; Text[200])
        {
            Caption = 'Extenral No';
        }
        field(60060; CustomerCode; code[50])
        {
            Caption = 'Customer Code';
            TableRelation = Customer;
        }
        field(60061; CustomerName; Text[200])
        {
            Caption = 'Customer Name';
            TableRelation = Customer.Name;
        }


    }
    keys
    {
        key(PK; DocumentType, DocumentNo)
        {
            Clustered = true;
        }

        key(Key2; CustomerName, PostingDate)
        {

        }
    }

}

Report
report 60030 CustomerItemSales 
{
    DefaultLayout = RDLC;
    RDLCLayout = 'CustomerItemSales.rdlc';
    ApplicationArea = Basic, Suite;
    Caption = 'Customer Item Sales';
    PreviewMode = PrintLayout;
    UsageCategory = ReportsAndAnalysis;
    DataAccessIntent = ReadOnly;
    Permissions = TableData SalesInvoiceCreditTemp = rimd;

    dataset
    {

        dataitem(SalesTemp; SalesInvoiceCreditTemp)//*Temp*
        {
            DataItemTableView = sorting(CustomerName, PostingDate);


            RequestFilterHeading = 'Sales';
            column(ReportName; 'Customer Sales by Item') { }
            column(SalesBufferFIlter; SalesBufferFIlter) { }
            column(StartingDate; FromDate) { }
            column(EndingDate; ToDate) { }
            column(DocumentType; DocumentType) { }
            column(CustomerCode; CustomerCode) { }
            column(CustomerName; CustomerName) { }



            trigger OnPreDataItem()
            var
                SalesTempTbl: Record SalesInvoiceCreditTemp;
                SalesInvHdr: Record "Sales Invoice Header";

            begin
                SalesTempTbl.DeleteAll();
                SalesTempTbl.Init();
                begin//*INVOICE
                    SalesInvHdr.Reset();
                    SalesInvHdr.SetRange("Posting Date", FromDate, ToDate);
                    if SalesInvHdr.FindSet() then
                        repeat

                            SalesTempTbl.DocumentType := '1INV';
                            SalesTempTbl.DocumentNo := SalesInvHdr."No.";
                            SalesTempTbl.PostingDate := SalesInvHdr."Posting Date";
                            SalesTempTbl.CustomerCode := SalesInvHdr."Sell-to Customer No.";
                            SalesTempTbl.CustomerName := SalesInvHdr."Sell-to Customer Name";

                            SalesTempTbl.Insert();

                        until SalesInvHdr.Next() = 0;
                end;

            end;

        }
    }
    requestpage
    {
        SaveValues = true;

        layout
        {
            area(content)
            {
                group(Options)
                {
                    Caption = 'Date Range';
                    field(StartingDate; StartingDate)
                    {
                        ApplicationArea = All;
                        Caption = 'Starting Date';
                        ToolTip = 'Specifies the date when you want the report to start.';
                    }
                    field(EndingDate; EndingDate)
                    {
                        ApplicationArea = All;
                        Caption = 'Ending Date';
                        ToolTip = 'Specifies the date when you want the report to end.';
                    }
                }
            }
        }
    }

    trigger OnPreReport()
    var
        FormatDocument: Codeunit "Format Document";
    begin
        CompanyInformation.Get;

        SalesBufferFIlter := SalesTemp.GetFilters;
        FromDate := StartingDate;
        ToDate := EndingDate;

        IF (StartingDate = 0D) OR (EndingDate = 0D) THEN
            ERROR('Start Date and End Date are Mandatory');

    end;

    var
        StartingDate: Date;//check
        FromDate: date;
        EndingDate: date;
        ToDate: date;
        CompanyInformation: Record "Company Information";
        Text000: Label 'Period: %1';
        Item: Record Item;

        SalesBufferFIlter: TExt;



}

Best Answer

  • ShaiHuludShaiHulud Member Posts: 228
    Answer ✓
    Your problem has nothing to do with UseTemporary - it's got to do with the fact that your Dataset is based on the instance of your table called "SalesTemp", but in the OnPreDataItem trigger you're filling instance called "SalesTempTbl".

    Solution is to remove "SalesTempTbl" as a variable and replace it in code with "SalesTemp". Then you will be inserting records into the correct instance of your table

Answers

  • bbrownbbrown Member Posts: 3,268
    Set temporary as a property of the report's dataitem. Not the table. The table needs to be a real database table. The report just uses a temporary version of it.
    There are no bugs - only undocumented features.
  • bbrownbbrown Member Posts: 3,268
    Ignore my prior posting. Leave the table as-is and set the "IsTemporary" property on the report. You have to do this for pages, I'm thinking reports would work the same way.
    There are no bugs - only undocumented features.
  • samantha73samantha73 Member Posts: 96
    Thanks. Couldn't find where to insert IsTemporary in the report as there is no setting before dataitems. However tried the data item UseTemporary as below which didn't work
            dataitem(SalesTemp; SalesInvoiceCreditTemp)//*Temp*
            {
                DataItemTableView = sorting(CustomerName, PostingDate);
                UseTemporary = true;//added
    
  • ShaiHuludShaiHulud Member Posts: 228
    Answer ✓
    Your problem has nothing to do with UseTemporary - it's got to do with the fact that your Dataset is based on the instance of your table called "SalesTemp", but in the OnPreDataItem trigger you're filling instance called "SalesTempTbl".

    Solution is to remove "SalesTempTbl" as a variable and replace it in code with "SalesTemp". Then you will be inserting records into the correct instance of your table
  • bbrownbbrown Member Posts: 3,268
    I completely missed the different variable names.
    There are no bugs - only undocumented features.
  • samantha73samantha73 Member Posts: 96
    awesome ..works now..silly mistake
Sign In or Register to comment.