Report Based On Temp Table Not Working

samantha73

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;



}

ShaiHulud

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

bbrown

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.

bbrown

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.

samantha73

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

ShaiHulud

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

bbrown

I completely missed the different variable names.

samantha73

awesome ..works now..silly mistake