I have a request from a user to modify a current Baan report. We do not have source code.

I can dump the data that the Baan session script gets into an interim table and then read from that table to re-sort and do the 'extras' requested then spit the new report out.

Is there a way to do this without creating an entire new table. Can I have the script stream data into a file and then read that file to then be processed into a new report? If so, how and what commands/functions do I need?

Thanks for any help!

We don't have the source code either, but we have modified a number of Baan reports. If you don't have to deal with data from tables other than those used, and then you can re-work the report. Baan SQL statements almost always include a SELECT * or get all columns. That means that it is possible to add other columns for the report. To modify the report, copy it up to your own VRC. You can then modify the layout and add the other fields to the Input Fields section of the report. Re-compile it and see what happens. You also have the option of adding or modifying the report script as well, but it may cause performance to degrade on the report depending on how you change it.

If they just want its appearance modified, then you have an easier task.

Gilbert Guymer
Database Administrator
Lufkin Industries, Inc.

The problem is that too much data is coming into the report. For example the report prints information based on purchase orders, but they want to be able to segregate by buyer. I can't modify session script, so I have a before.program section of the report script prompt another session where the user can enter the buyer number. That is imported into the report script. I was then trying to set lattr.print to false if the imported buyer number didn't match the PO's buyer number. There are about 10 different layouts in the vanilla Baan report and there are flags all over the place to print different things (mostly different texts). I can't see what some of the flags are for since they are assigned in the session script. I was hoping to just dump it all into a table, and then rprt_send() only records where the buyer matched.

You can actually read the input file in the report and delete the records you do not want to print. I will post an example of how something like this could be done. In the case below I am actually adding a sort field to the report. Beware that the character in the sort and concat is the "STX" character.

In your case you could just read in the records and delete the ones you do not need. Then write the temp file.

Mark


declaration:
table ttipgc001
long l,r.o,first

|detail.1:
|before.layout:
after.receive.data:
if(first=0) then
r.o = seq.open (r.datafile$ & ".tmp","w")
r.reccount = 0
while e = 0
select tipgc001.cplb
from tipgc001
where tipgc001._index1 = {:tisfc001.ccot.a,
:tisfc001.mitm}
selectdo
endselect
l = seq.puts(concat$("",
tipgc001.cplb,
tisfc001.pdno,
tisfc001.ccot.a,
tisfc001.mitm,
error.message,r.reccount),r.o)
r.reccount = r.reccount + 1
r.read.seq.file()
endwhile
l = seq.flush (r.o)
l = seq.close (r.lfn)
l = seq.close (r.o)
l = run.baan.prog( "sort","-t +0n
-1 +1n -2 " & r.datafile$ & ".tmp -o " &
r.datafile$ & ".tmp", RP_WAIT)
r.lfn = seq.open (r.datafile$ & ".tmp" ,"a+")
r.read.seq.file()
first = 1
endif

after.program:
l = seq.unlink(r.datafile$ & ".tmp")

Mark,

This is one of the lines from the above code:

l = run.baan.prog( "sort","-t +0n -1 +1n -2 " & r.datafile$ & ".tmp -o " & r.datafile$ & ".tmp", RP_WAIT)

I believe that this is the actual line that re-sorts your original data. Can you define some of the variables in this line? If I had three items in a record, say First Name, Last Name and favorite website:

EddieMonsterwww.baanboard.com
JohnSmithwww.yahoo.com
BobJoneswww.msn.com

How are you defining which field you are sorting on?

Are there any other types of 'executions' that you can use with the run.baan.prog() function?

One more question...What is the error.message variable. When I compile my program it says that it isn't declared. If I declare it as a string, does the function return a value for that variable?

I guess I should have mentioned that this is running on Sun solaris box. The sort is the Unix sort command. The -t is the field separator for the records - in this case the "STX" character. The sort fields are defined with +n -m fields. So +0n is skip 0 fields, -1 sort by first field and +1n is skip one field, -2 is sort by second field. In our case we were adding a primary sort field.

I am not sure what you mean by other "executions" of the run.baan.prog(). I believe I have used it with script files and with awk/sed scripts.

Hope this helps.

Mark

As far as 'executions', I didn't realize that 'sort' was a unix command (I don't use unix very much as all), I thought it was a Baan function and there may have been other functionality or commands to use with it.

Thanks.

I also found baan executeable in

$BSE/bin/sort6.2

I think it works the same as the unix sort, I never used it yet.

Mark,

From your original script I made the following modifications:

Imported a variable ccon.f which represents a user defined buyer for PO's.

modified your select statement and l=seq.puts(...) line to the following:

select tdpur040.ccon
from tdpur040
where tdpur040.orno = :tdpur041.orno
selectdo
endselect
if tdpur040.ccon = ccon.f then
l = seq.puts(concat$("~",
tdpur040.ccon,
suco,
ipur.ccor.dsca,
tdpur000.erdc.4,
tdpur041.cntr,
tdpur041.revi,
tiitm012.aitc,
itemline,
itm.dsca,
itm.kitm,
itm.oltm,
note.date,
nr.copy,
stand.desc,
tccom000.city,
tccom000.namc,
tccom020.ctit,
tccom020.nama,
tccom020.namb,
tccom020.namc,
tccom020.namd,
tccom020.name,
tccom020.namf,
tcmcs007.dsun,
tcmcs019.dsca,
tdpur021.itsu,
tdpur041.cprj,
tdpur041.ddta,
tdpur041.ddtc,
tdpur041.item,
tdpur041.oqua,
tdpur041.orno,
tdpur041.suno,
tdpur041.txta,
tipcs020.dsca,
txtn.ptr,
error.message,r.reccount),r.o)
else
endif

All of the fields are the I got from debugging the report except for tdpur040.ccon which I added.

I'm getting an error when I run the script:

Error in reading sort file record: 1 fields scanned, 36 expected. What does that tell me?

It means that when it is doing the string scan it is looking for 36 fields and not 1. I notice that you have a "~" character in the concat command. My change makes use of the Baan generated r.seq.read.file() routine and this expects the STX character or a ctrl-b. So if you debug the report and check the above routine you should see the error from a string.scan command.

Basically you are switching the temp files on the r.seq.read.file routine. You should be able to see the field separator in debug mode.

I did not know there is a baan sort program. When I was developing the script I was working with a consultant who said that it was running the Unix sort. I guess I never really paid attention to the actual sort command. So dis-regard what I said before - I use the run.prog for the unix stuff and run.baan.prog to run Baan's sort routine. My sincere apologies for the mistake.

And NPRao is correct the baan sort program is equivalent to the unix sort command. At least that is what the documentation says.
Mark

Thanks for the help Mark, I had to tweak it a little, but finally got everything I needed. Thanks again!

Glad to see it is working for you eric. My appologies again about the confusion on the run.baan.prog command.


Mark

Mark,

You mentioned in your first entry to this post that you would be able to provide a sample of how to delete the records that you wouldn't want to print. Would you still post that?

Thanks.

The code Mark prestened here is sure one of the greatest Baan hacks I've ever seen. So it get's my special wandering price for Baan wizadry. ~Vamsi, please hand it over to him! That's really cool!

BUT:

Don't do this at home. Let's go back to the original problem: A user want's to filter the records in a report by his/her buyer id. The source code is not available.

Well guys, why don't we just get the source code then????

Having the sourcecode we have to put four(!) additional lines in the source, recompile it, put two additional fields on the form and we are done.


And what are we doing here? We need a new session for entering our values. We hack the report script with more than fifty lines of code. (If another user requires a new report with ohter fields, but filtered equally, we do the same in this other report again.) We use a Baan-internal executable to resort the report. Any new porting set could make this hacked report not working any longer.

Say, which solution will be easier (and that is: cheaper) to maintain? This (really genious) hack, or a clean and small modification in the program script?

May the source be with you!

Uli

This "hack" was not completely my own - I had an excellent D&T consultant who helped me work this out.

Next ulrich.fuchs said:
Well guys, why don't we just get the source code then????

Because my company refuses to buy the source. Things would be so much easier if we bought the source. So yes I do agree that if you owned the source it would be easier to make the changes, but not everybody can get the source.

And I believe both are just about as hard to maintain. Our consultant left some Baan code with us and with each new release we have to port these changes into the new release.

So it is a toss up to me.

Mark

Thanks to mark (and of course to the unknown consultant) for that great pice of code how to manipulate the report input file.

With help of this example we solved a difficult user request.

Regards,
Juergen

Good stuff, guys!

I also had done some *special* customizations on reports and tried to discuss it, but the response was poor:

http://www.baanboard.com/baanboard/showthread.php?threadid=9090

Günther

I do remember your other thread. I just have never had the opportunity or the need to try it. I thought it was a good solution. I actually have some bookmarks to certains threads - this is one.


Mark

Hello,

Trying to use this technique to sort a BOM report by planner code and it appears to be working reasonably well. However

The sort is occuring but I have a problem in that the first record being printed is not being sorted correctly. The report then works perfectly and even reprints the first record in the correct position in the sort.

I suspect I may have my receive.data section in the wrong position in the script. Where should the receive.data section be put to ensure that the first record is sorted correctly.

Regards
Neal Matthews
Intier Automotive - IT Support Analyst

Not sure there is a wrong spot for this. Can you post your after.receive.data code? I would certainly put it in debug mode and check the temp files - before and after the sort. Need to make sure those are working correctly and that first record is not in their twice.

Mark

Thanks Mark,

I'll have a careful look at the tmp file and what's going on in the debugger.

Nothing too different about the code just several other fields in the seq.puts statement. See attached text file.

Cheers
Neal

after.receive.data:
if(first=0) then
r.o = seq.open (r.datafile$ & ".tmp","w")
r.reccount = 0
while e = 0
select tdpsc001.item, tdpsc001.cpln
from tdpsc001
where tdpsc001._index3 = {:sitm.topl}
selectdo
select tccom001.emno, tccom001.nama
from tccom001
where tccom001.emno =:tdpsc001.cpln
selectdo
endselect
endselect

l = seq.puts(concat$("",
tccom001.emno,
sitm.topl,
tibom010.mitm,
widt.ext,
cuni.mitm,
cuni.sitm,
cycle.detected,
datearr(1),
datearr(2),
datearr(3),
datearr(4),
datearr(5),
dayarr(1,1),
dayarr(1,2),
dayarr(1,3),
dayarr(1,4),
dayarr(1,5),
dsca.mitm,
dsca.sitm,
dum.chain,
gqan.ext,
leng.ext,
m_arrears,
m_arrears1,
m_arrears2,
m_date,
m_date1,
m_date2,
m_date3,
m_invhand,
m_invhand1,
m_invhand2,
m_invtopl,
m_monday,
m_monday2,
m_pursched,
m_stocdays,
message.to.brp,
nqan.ext,
sort.dim,
tccom001.nama,
tccom010.nama,
tccom020.nama,
tdinv001.stoc,
tdpsc003.dtwk,
tdpsc003.endt,
tdpsc003.item,
tdpsc003.qtos,
tdssc003.cdel,
tdssc003.cuno,
tdssc003.dqty(1),
tdssc003.dqty(2),
tdssc003.dqty(3),
tdssc003.dqty(4),
tdssc003.dqty(5),
tdssc003.dqty(6),
tdssc003.dqty(7),
tdssc003.dtwk,
tdssc003.item,
tdssc003.reqf,
tdssc003.reqt,
tibom000.buom,
tiitm001.cuni,
tiitm001.dsca,
tiitm001.suno,
level.overflow,
message.counter,
noun.ext,
r.reccount),r.o)
r.reccount = r.reccount + 1
r.read.seq.file()
endwhile
l = seq.flush (r.o)
l = seq.close (r.lfn)
l = seq.close (r.o)
l = run.baan.prog( "sort","-t +0n -1 +1n -2 " & r.datafile$ & ".tmp -o " & r.datafile$ & ".tmp", RP_WAIT)
r.lfn = seq.open (r.datafile$ & ".tmp" ,"a+")
r.read.seq.file()
first = 1
endif

|*************************************************************

Well I certainly do not see anything in the code. Hopefully running in debug mode and looking at the temp file will provide some better clues.

Mark

Hello Mark,

Still struggling with this I'm afraid. I had to take some time off to get the unsorted version of the report to the user.

Anyway looking at the temp file what should excatly happen after the sort command;

l = run.baan.prog( "sort","-t +0n -1 +1n -2" & r.datafile$ & ".tmp -o " & r.datafile$ & ".tmp", RP_WAIT)

After I execute this line the tmp file is recreated as a file of zero bytes. Is this correct ?

Cheers
Neal

I believe at that point it should not be 0 bytes. With the seq.flush and seq.close I would expect the file to have some data in it. I was hoping that right after the seq.close and before the sort you could do a more on the file to see what data was in it. I have not run ours in a long time, but I will give it a try.

Mark

On my system I stopped it on the sort and went to the tmp directory. My ".tmp" file did have data in it. I stepped through the sort and it still had more than 0 bytes. Even on the next seq.open it still had data. From this I would conclude that your file should also have data.

Mark

Hi Mark

Just to finish this one off couldn't get the sort working. I think the problem may be related to the fact that I had two layouts in my report and was trying to sort across the two layouts.

The performance of the report was also a little suspect so I've now changed tack and to get a planner / supplier sort I'm using a Purchase Contract report and drilling through the bom in the script.

I'll bookmark this post and hope to use this technique in the future.

Cheers
Neal

In nutshell, the solution is excellent to bring non-input-variable information from other tables probably customized and then sort it on such information if there are no other sort layouts (before field & after fields) and no sort breaks are required on such out-of-the-report sort field. This solution may give wrong results when after field / before fields layouts exist.

Please correct me if wrong .

Correct and very well summed up.

After playing with this code so much I am detemined to find a use for it somewhere but sadly not today.

Thanks to everyone involved in this thread.

Cheers
Neal

Mark

I just copied your code in one of the report we need to customise at on the basis of item group, made some changes and got the error above mentioned error message.

after.receive.data:
if(first=0) then
r.o = seq.open (r.datafile$ & ".tmp","w")
r.reccount = 0
while e = 0
select tcibd001.citg
from tcibd001
where tcibd001._index1 = {:whina112.item}
as set with 1 rows
selectdo
endselect

l = seq.puts(concat$("",
tcibd001.citg,
error.message,r.reccount),r.o)
r.reccount = r.reccount + 1
r.read.seq.file()
endwhile
l = seq.flush (r.o)
l = seq.close (r.lfn)
l = seq.close (r.o)
l = run.baan.prog( "sort","-t +0n -1 +1n -2 " & r.datafile$ & ".tmp -o " &r.datafile$ & ".tmp", RP_WAIT)
r.lfn = seq.open (r.datafile$ & ".tmp" ,"a+")
r.read.seq.file()
first = 1
endif

after.program:
l = seq.unlink(r.datafile$ & ".tmp")



On looking at the temp files, I found out that the file with ext .tmp contained the record with fields in following sequence
(item group, STX,error.message STX, r.reccount)
that clarifies the error that its waiting for a total 55 fields but got only 3

I also found out that total report input field counted to 55. So my question are

1. Why the rest of the data not copied to our file.

2. Are there some prerequisites for applying your solution, like Do we need to add the new sort field(item group) in the report input field.

also let me know what changes I should make in the "run.baan.prog" to make it applicable in my case ("-t +0n -1 +1n -2 " part)

Thanks in Advance

You need to add all 55 fields into the concat statement to get all the input fields. I believe, but am not positive that I added the new field into the input fields. And I can not remember what all the sort parameters are - I will check at work tomorrow to see if I can find some help. I believe they are identical to the UNIX sort command, but not positive. So what are your sort fields in the order of the sort?

Mark

I added all 55 fields into the concat statement, the input file (.tmp) now contains all data fields. but the error message(It still looks for 55 fields and is able to read only one) is still there.

I already have four sort fields on the report, I want item group (tcibd001.citg) to overide other four sort fileds in the report; the sort fields are as follows:

whina112.cwar 1 tc cwar Ascending 1
whina112.trdt 1 tc date Ascending 2
whina112.item 1 tc item Ascending 3
whina112.seqn 1 tc mcs.long Ascending 4

Regards

Amit,

I think that maintaining 55 inpuit fields is a pain and I would suggest a different approach. I have a very little report sample to give you the idea:


| simple report script
|
| assumptions:
| 1. The report MUST at least have one sorting field (so that the function
| r.write.seq.file will be generated).
| 2. Additional / missing fields should be added as INPUT fields, and given
| their value before the calling of r.write.seq.file().
declaration:
#define ORIGINAL.FUNCTION() originally.generated.r.write.seq.file()
#define r.write.seq.file() ORIGINAL.FUNCTION()

functions:
#undef r.write.seq.file()
function extern r.write.seq.file()
{
| Code her to give the addition / missing fields their value, e.g.
a = rnd.i(10)
b = rnd.i(10)

| Now continue with the regular report flow ...
ORIGINAL.FUNCTION()
}


Would you try that?

Günther

A very short, simple and effective solution indeed, It solved my problem. Gunther you really made my day and even my night(I can have a sound sleep now). Just can't stop wandering that how the solutions to the worst problems can be so simple

Thanks a lot mate.

But I will still wait for mark's solution, now not driven by urgency but by curiosity.

Regards

Interesting solution Gunther. If I ever have to do something like this again I will try it. Looks much simplier than what we did.

As to the original question - yes I added my new field as the primary sort on the input fields form. And my sort command defined two sort fields. If you wanted one then you could have subtracted the second from the posted code. If you wanted it to have 5 then you would have to define 5. All that besides defining all the fields - in my case I only have 5 fields. One of them was the error.message field passed from the script.

Dear Mark,

I am successfully sorted the field in my report. But the field is not GROUPING even I set it as before.field.

Refer to attachment for details.

Please help.



You can actually read the input file in the report and delete the records you do not want to print. I will post an example of how something like this could be done. In the case below I am actually adding a sort field to the report. Beware that the character in the sort and concat is the "STX" character.

In your case you could just read in the records and delete the ones you do not need. Then write the temp file.

Mark


declaration:
table ttipgc001
long l,r.o,first

|detail.1:
|before.layout:
after.receive.data:
if(first=0) then
r.o = seq.open (r.datafile$ & ".tmp","w")
r.reccount = 0
while e = 0
select tipgc001.cplb
from tipgc001
where tipgc001._index1 = {:tisfc001.ccot.a,
:tisfc001.mitm}
selectdo
endselect
l = seq.puts(concat$("",
tipgc001.cplb,
tisfc001.pdno,
tisfc001.ccot.a,
tisfc001.mitm,
error.message,r.reccount),r.o)
r.reccount = r.reccount + 1
r.read.seq.file()
endwhile
l = seq.flush (r.o)
l = seq.close (r.lfn)
l = seq.close (r.o)
l = run.baan.prog( "sort","-t +0n
-1 +1n -2 " & r.datafile$ & ".tmp -o " &
r.datafile$ & ".tmp", RP_WAIT)
r.lfn = seq.open (r.datafile$ & ".tmp" ,"a+")
r.read.seq.file()
first = 1
endif

after.program:
l = seq.unlink(r.datafile$ & ".tmp")

You may want to double check the sort is working. After adding the field and sorting the data - go look at the temprorary report file that is generated. So debug the report, make sure the records are sorted correctly and they are being read in correctly.

You may also want to look at the post by gunther on page two - here is thelink (http://www.baanboard.com/baanboard/showthread.php?threadid=9090).