Hello,

I'm busy with creating a report for our electronic customs document (NL=douanedocument). I've to create an ascii file with several fields.
The report receives the data via a Tools Query.

The header-section contains the global information, de the detail-section contains article-specific information.

BUT, It's mandatory to put the total weight and the total articles in the header.

I know how to calculate and display the total weight and total articles in a footer or after report section.

Somebody adviced my to run the query again in the before.program section of the report script, but I see that the result is coming from the "selectempty".

This is my query in Query Data:

select
tcibd001.seak, | Search Key I
whinh431.item, | Item
tcibd001.ccde, | Commodity Code
tcibd001.wght, | Weight
tcibd001.ctyo, | Country of Origin
whinh430.sdtt,
sum(whinh431.qpic):float0
from
tcibd001, | Items - General
whinh430, | Shipments
whinh431 | Shipment Lines
where
whinh430.sdtt >= whinh430.sdtt.f and
whinh430.sdtt <= whinh430.sdtt.t and
whinh431.clot >= 'FR0' and
whinh431.clot <= 'FR9' and
whinh431.item = tcibd001.item and
whinh431.shpm = whinh430.shpm and
whinh431.worg = whinh.oorg.sales and
(whinh431.worn < 'VAA000000' or
whinh431.worn > 'VZZ999999')

group by
tcibd001.seak,
whinh431.item,
tcibd001.ccde,
tcibd001.wght,
tcibd001.ctyo,
whinh430.sdtt
order by
whinh431.item

This is the before.program section:

before.program:
import("field.input.from",begindatum)
import("field.input.to",einddatum)

total_articles_header = 0
total_weight_header = 0

select
sum(tcibd001.wght):weight, | Total Weight
sum(whinh431.qpic):quantity | Total Articles
from
tcibd001, | Items - General
whinh430, | Shipments
whinh431 | Shipment Lines
where
whinh430.sdtt >= :begindatum and
whinh430.sdtt <= :einddatum and
whinh431.clot >= 'FR0' and
whinh431.clot <= 'FR9' and
whinh431.item = tcibd001.item and
whinh431.shpm = whinh430.shpm and
whinh431.worg = whinh.oorg.sales and
(whinh431.worn < 'VAA000000' or
whinh431.worn > 'VZZ999999')
selectdo
| total_weight_header = weight
| total_articles_header = quantity
total_weight_header = 99 | for test
total_articles_header = 99 | for test
selectempty

| total_weight_header = weight
| total_articles_header = quantity

total_weight_header = 88 | for test
total_articles_header = 88 | for test

endselect

The result printed in the header is 88.

I'm also thinking about a construction, to write the data of de detail section to file 1, to write the "header"-info in the footer to file2, and than merge the to files (file 2 first).

Does anybody have a (better) solution??

Regards,

Bennie Kriekaard
IT Administrator
Sunny Europe N.V.
Antwerp - Belgium

Bennie,

It seems your query is not good or your input variables are not filled correctly.
You can check the query with e.g. qptool and fill in all values by hand. Eliminate lines one by one until you get at least some output.

Hope this helps,
Dick

Looks like in before.program the variables for your sql statement are not filled.

Try before.report or

header -> before.layout section.

Regards,

Kozure

Dick,

I've run the same query in qptool and the result is ok.

Kozure,

I've printed the values of "begindatum" and "einddatum" on the report, and I see the values.


See attachement for more info.

Regards,
Bennie Kriekaard

Bennie,

It looks like that the company in the report is not the same as the company for qptool. Can you test with a s=database trace to see if the correct company has been selected?

Bets regards,
Dick

Bennie,

there are two things: I don't like reading the data twice, and it might happen that your total is different to your details just beause some data has changed.

Some time ago I had figured out another way that allows you to have a look at the data itself: http://www.baanboard.com/baanboard/showthread.php?threadid=9090

Günther

In the meantime I've found the following solution:

header.1:
before.layout:

bestandsnaam = "d:\infor\bse\plda\" & currentdate & ".txt"
fp = seq.open(bestandsnaam, "at+")

detail.1:
before.layout:
command = "L1=" & """" & tcibd001.ccde & """"
seq.puts(command, fp)

and other lines.....


after.report.1:
before.layout:
seq.rewind(fp)
command = "HEADER 001"
seq.puts(command, fp)
command = "HEADER 002"
seq.puts(command, fp)
seq.close(fp)


-----

and my result looks like:

HEADER 001
HEADER 002
L1="3303009000 "

So I will generate my "header"-info in the footer section.

Thanks a lot for your advices.

If you have sort fields in your report you can also add the weight and the quantity by yourself in the after.receive.data section