All Sales Order report

amphysvena

Hi,
My Client needs a report that list all the Sales Order information(invoiced or not invoiced yet).

I got the information for this report from 2 tables, Sales Header (table 36) & Sales Invoice Header (table 112), cos Sales Order will be deleted whenever it invoiced.

The problem is, i need to union this 2 tables then sorted it by Sales Order number. How can I solve this?
Thx. Any help would be appreciated

kriki

You need a temporary table to store all information in it. So you can search on Sales Header and then on Sales Invoice Header. For printing the results, you can loop on the temptable.

amphysvena

Hi,
Thx for the reply. Sory, I'm still quite new in NAV.
A temporary table is variable with record type and set the property temporary:yes. Right?
Or a temporary table is a table (create new table), that is used only for this report?
Thx. Sory if my question kinda stupid

Waldo

The first:
A temporary table is variable with record type and set the property temporary:yes.

Remember ... there are no stupid questions !

kriki

Waldo wrote:

Remember ... there are no stupid questions !

I can confirm that!

amphysvena

Ok, I've just read Luck Van Dyck, How To create a report which is based on a temporary table.

Base on the replies from this post & the tutorials, I've created a new table, named TempHeader (No., SO number, Inv number, posting date, Cust No.).

In the report, I created a temporary table from TempHeader. Fill the temporary table from Sales Header & Sales Invoice header, then showed it using data item integer in the report. It worked.
But I need to group the TempHeader by Country, then by Customer. Also i need to show the detail of each tempheader (item, qty, amount, etc).

How to groupped temporary table in report?Thx a lot

Remember ... there are no stupid questions

Thanks

kriki

-Create your temptable with all fields you need in it:
Country,Customer,order no,Invoice no,item no,line no,quantity,.....
//"Line No." is to make it unique in case the same item no is more than once in the same document.

-create an index on the fields on which you need to sort, so e.g.:
"Country,Customer,order no,Invoice no,item no"

-in your report, define multiple temptables based on the same table in which you fill only the fields you need for the grouping:

CLEAR(tmpGroup1);
tmpGroup1.Country := recSalesHeader."Country Code";
IF tmpGroup1.INSERT THEN ;

CLEAR(tmpGroup2);
tmpGroup2.Country := recSalesHeader."Country Code";
tmpGroup2."Customer No." := recSalesHeader."Sell-to Customer No.";
IF tmpGroup2.INSERT THEN ;

CLEAR(tmpDetails);
// fill in this all fields from the header + line you currently have

Now you define 3 dataitems in your report:
first : tmpGroup1
second : indented : tmpGroup2 with "Country" = tmpGroup1.Country.
third : indented twice : tmpDetails with Country and customer = the ones from tmpGroup2.

BTW : real tables you ONLY use as temptable (so you NEVER write them to the DB) you don't even need to put them in the license of the customer!

Waldo

kriki wrote:

BTW : real tables you ONLY use as temptable (so you NEVER write them to the DB) you don't even need to put them in the license of the customer!

IMO, that's one for Tips&Tricks =D>

kriki

Waldo wrote:

kriki wrote:

BTW : real tables you ONLY use as temptable (so you NEVER write them to the DB) you don't even need to put them in the license of the customer!

IMO, that's one for Tips&Tricks =D>

http://www.mibuso.com/forum/viewtopic.php?t=5458
Use the Search,Waldo

Waldo

OK, already there :oops:

amphysvena

I'm sorry Kriki. But I'm still don't understand.

kriki wrote:

CLEAR(tmpGroup1);
tmpGroup1.Country := recSalesHeader."Country Code";
IF tmpGroup1.INSERT THEN ;

CLEAR(tmpGroup2);
tmpGroup2.Country := recSalesHeader."Country Code";
tmpGroup2."Customer No." := recSalesHeader."Sell-to Customer No.";
IF tmpGroup2.INSERT THEN ;

CLEAR(tmpDetails);
// fill in this all fields from the header + line you currently have

So tmpGroup1, tmpGroup2, tmpDetails are record variables which are temporary:yes. Right?

kriki wrote:

Now you define 3 dataitems in your report:
first : tmpGroup1
second : indented : tmpGroup2 with "Country" = tmpGroup1.Country.
third : indented twice : tmpDetails with Country and customer = the ones from tmpGroup2.

How to define dataitem from those variables(tmpGroup1, tmpGroup2, tmpDetails)? Cos, as far as i know, i can't make dataitem from temporary records.

Thx

kriki

amphysvena wrote:

So tmpGroup1, tmpGroup2, tmpDetails are record variables which are temporary:yes. Right?

Right.

amphysvena wrote:

How to define dataitem from those variables(tmpGroup1, tmpGroup2, tmpDetails)? Cos, as far as i know, i can't make dataitem from temporary records.

Check this for a report on temptables: http://www.mibuso.com/howtoinfo.asp?FileID=6.
And extra :
-the first dataitem works on tmpGroup1.
-the second dataitem works on tmpgroup2 and is indented AND shows only records in which tmpGroup2."Group Level 1" = tmpGroup1."Group Level 1".
So in the OnPreDataItem of group2, you put:

tmpGroup1.RESET;
tmpGroup1.SETRANGE("Group Level 1",tmpGroup1."Group Level 1");
SETRANGE(Number,1,tmpGroup1.COUNT);

-third dataitem on the details filtered by group level 1 and group level 2.

amphysvena

Thx for the replies. I think I've succeeded making the report using the temporary table (using integer data item in the report). But the report is quite slow. I've used setcurrentkey in the code, but still get slow report.
Anyway, I'll try to convice my client to get the data from sales shipment header instead of sales header & sales invoice header. So we don't need to make any temporary table to do the sorting.
Thx for helps

kriki

amphysvena wrote:

Thx for the replies. I think I've succeeded making the report using the temporary table (using integer data item in the report). But the report is quite slow. I've used setcurrentkey in the code, but still get slow report.

It also depends on how many records you read.
How many records are there between the 2 tables and between header and details?

amphysvena

I've made some changes, & its performance quite affordable I think.
These are the records, my report read:
- Sales Header : 251 records
- Inv Header : 8089 records
- Sales Line : 305 records
- Inv Line : 11557 records

It generate 929 pages, in 2 minutes 55 sec.
I used 2 temporary tables before, for the header & the detail. But now, i used 1 temporary table to store both sales header & inv header.

Thx a lot for the helps