Report Based On Temp Table Not Working

samantha73
Member Posts: 118
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)
Report
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; }
0
Best 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 table1
Answers
-
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.0 -
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.0
-
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
0 -
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 table1 -
I completely missed the different variable names.There are no bugs - only undocumented features.0
-
awesome ..works now..silly mistake0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions