Hello friends,

I need some advice URGENTLY:

For a certain (fully-customized) report I have to add a list with data for records not mentioned on the report detail lines. These lines are variable - depending on the data printed - and thus, the amount of lines is variable too.

The required list has to be printed at the bottom of the report, so I have to add it as an after.report layout. In the report script I will make the query to determine which records "are missing", i.e. which records I have to add there. How do I handle this? How do I keep the amount of lines for this layout variable? Do I do this best by using the need() fundtion? If yes, please give an example of how to use it (I am not so familiar with those functions). Any other suggestions are most welcome!!

Many thanks in advance!!

En.

Hello Andreas,

Thanks very much for your response! Let me try to explain a bit better what my situation is:

At our company, for most 'areas' there is a warehouse with the same name. I.e. if you find in tcmcs045 an area "abc" you will find in tcmcs003 a warehouse "abc".

The report has to print warehouse data based on a selection of areas. So if the user selects areas "AAA" to "ABC" the report will print data (in teh detail lines) of warehouses "AAA" to "ABC". Now at the bottom of the report I want to print a list of the data of all warehouses that are not yet mentioned on the report, i.e. those who do not have an area with the same name.

I retrieve them very simply, with the following code:
select tcmcs003._index1
from tcmcs003
order by tcmcs003._index1
selectdo
creg.cwar = shiftl$(strip$(tcmcs003.cwar))
select tcmcs045._index1
from tcmcs045
where tcmcs045._index1 = {:creg.cwar}
order by tcmcs045._index1
selectdo
selectempty
|// Selecting my data, and sending to report
endselect
endselect


I hope my intention is clear. So now, if I adopt your solution, how will it work? My code to select the required records goes through the whole tcmcs003 (warehouses) table and then checks for each warehouse if a tcmcs045 (area) exist; if not, a record has to be printed in the after.report. Can this be achieved also with layout.again?? If not, would you, or anyone else have another suggestion??

Many thanks in advance!!

Kind regards,

En

Andreas,

Thanks very much for your response, and sorry for my late reply; I just now found time to continue working on this...

Your solution still has one big problem:
I only want a record printed in case of 'selectempty' on the tcmcs045-query, i.e. if for that warehouse there is no area linked with same name; but what for all those who land in the 'selectdo' section, i.e. there is an area found with same name as warehouse; those will all be printed as well now...

I cannot use lattr.print = false, because then it will stop the cycle, and not check the remaining warehouses anymore; i.e. it will never hit the layout.again() anymore....

As you were so nicely on your way to helping me, I'm sure you will find a nice solution to this as well.... ;)

Thanks again!!

En

I would use an array to track the information (but the code is just hacked i.e. untested):


declaration:
long cnt
domain tccreg arr.creg(1000) | or make it dynamically
domain tcbool arr.used(1000)

long final.i

table ttcmcs045

before.program:
cnt = 0
select tcmcs045.*
from tcmcs045
order by tcmcs045._index1
selectdo
cnt = cnt + 1
arr.creg(1,cnt) = tcmcs045.creg
arr.used(cnt) = false
endselect

final.i = 0

detail.i:
after.layout:
long i
for i = 1 to cnt
if arr.creg(1,i) = creg.just.printed then
arr.used(i) = true
break
endif
endfor

| scheme from andreas
after.report.5:
before.layout:
if final.i < cnt then
repeat
final.i = final.i + 1
if final.i > cnt then
lattr.print = false
break
endif
if arr.used(i) = false then
warehouse.to.print = arr.creg(1,final.i)
break
endif
until false
else
lattr.print = false
endif
after.layout:
layout.again()

Andreas, thanks for this idea; sounds good. I have a problem while compiling the code with the not exist statement: I get compiler warnings for the after.report.5: after.layout: and for the after.report.10 layouts saying "label 'after.layout' declared but never used", "label 'after.report.10' declared but never used" and "label 'before.layout' declared buit not used". If I put the not exist lines in comment, I do not get the warnings. Af I run the report in debug mode, the report will hit the after.layout section where it says layout.again(); however the layout will not be printed again....

Does anyone know what this means?

Andreas,

What did you revise? I do not see many changes between your latest code, and the code from before. I don't know why this is, but I keep getting those warnings.

If I delete the 'not exist (select ...)' part, I don't have the warnings.

Anyone can help me out here...?

Yes, I created them via the Baan report editor (is there any other way of creating layouts??????? - well, except for via de session generator then...). I am on BaaN 5.0B. The funny thing is that it seems like somehow it has to do with this piece of nested subquery... but I don't understand how and what...

P.s. the after.layout.2 complies with the after.report.5 from your example, and after.report.3 is an additional after.report layout, for the final totals.

Sure, here it goes:


after.report.1:
after.layout:
creg.cwar = ""


after.report.2:
before.layout:
utc.date.f = date.to.utc(year, period, 01, 00, 00, 00)
if period < 12 then
num.date.t= date.to.num(year, period + 1, 1) - 1
else
num.date.t= date.to.num(year + 1, period - 11, 1) - 1
endif
dummy2 = num.to.date(num.date.t, fnc.year, fnc.period, day)
utc.date.t = date.to.utc(fnc.year, fnc.period, day, 23, 59, 59)

utc.all.f = date.to.utc(year - 1, period, 01, 00, 00, 00)
num.date.t= date.to.num(year, period, 1) - 1
dummy2 = num.to.date(num.date.t, fnc.year, fnc.period, day)
utc.all.t = date.to.utc(fnc.year, fnc.period, day, 23, 59, 59)

select tcmcs003._index1
from tcmcs003
where tcmcs003._index1 > {:creg.cwar}
and not exists
(select tcmcs045
from tcmcs045
where tcmcs045.creg = :tcmcs003.cwar)
order by tcmcs003._index1
as set with 1 rows
selectdo
creg.cwar = shiftl$(strip$(tcmcs003.cwar))
num.date.f = date.to.num(year - 1, period, 01)
num.date.t= date.to.num(year, period, 1) - 1

stock = 0
ucop = 0

select whinh961.*
from whinh961
where whinh961._index1 = {:creg.cwar, :item}
and whinh961.date inrange :num.date.f and :num.date.t
order by whinh961.date desc
selectdo
change2 = false
num.to.date(whinh961.date, fnc.year, new.perd, day)
| if hold.perd <> new.perd then
change2 = true
stock = stock + whinh961.stoc
ucop = whinh961.ucop
| endif
hold.perd = new.perd
selecteos
if not change2 then
| stock = stock + whinh961.stoc
| ucop = whinh961.ucop
endif
endselect

quan.amnt = tot.quan * ucop
ave.stock = stock / 12
stoc.amnt = stock * ucop
ave.stoc.amnt = stoc.amnt / 12


select sum(whinr110.qstk):miss.sqpe
from whinr110
where whinr110._index5 inrange {:utc.all.f, :tcmcs003.cwar, :item}
and {:utc.all.t, :tcmcs003.cwar, :item}
and (whinr110.koor = 34 or whinr110.koor = 35)
| order by whinr110._index5
selectdo
tot.quan.amnt = tot.quan.amnt + cwar.quan.amnt
selectempty
miss.sqpe = 0
endselect

selectempty
select tcmcs003._index1
from tcmcs003
where tcmcs003._index1 > {:creg.cwar}
order by tcmcs003._index1
as set with 1 rows
selectdo
selectempty
lattr.print = false
endselect
endselect



after.layout:
layout.again()



after.report.3:
before.layout:
ave.tot.stoc.amnt = tot.stoc.amnt / 12
if ave.tot.stoc.amnt <> 0 then
tot.rot.year = tot.quan.amnt / ave.tot.stoc.amnt
else
tot.rot.year = 0
endif

if tot.rot.year <> 0 then
tot.rot.day = 365 / tot.rot.year
tot.rot.week = 52 / tot.rot.year
else
tot.rot.day = 0
tot.rot.week = 0
endif

label.csig = ""
for i = 1 to 3
if not isspace(csig(1, i)) then
tmp.csig = csig(1, i)
select tcmcs018.dsca
from tcmcs018
where tcmcs018._index1 = {:tmp.csig}
as set with 1 rows
selectdo
csig.dsca(1, i) = tcmcs018.dsca
selectempty
csig.dsca(1, i) = ""
endselect
label.csig = "Omschrijving vermeldde signalerigscodes:"
endif
endfor

Andreas, no more inspiration???
Anybody else??? Need to solve this riddle....!!

En -
Just from looking, I think you need to take out the ":" in your where not exists / :tcmcs003.cwar statement:


and not exists
(select tcmcs045
from tcmcs045
| where tcmcs045.creg = :tcmcs003.cwar) |old
where tcmcs045.creg = tcmcs003.cwar) |new

In addition to extra colon as aforesaid , triming the value creg.cwar can also create problem if there are leading spaces . Just check the code after removing the code to trim the value of creg.cwar.

Andreas, thanks for your response. Looking forward to your "solving the riddle" today...
Lisa, thanks for your correct remark. This did however, unfortunately not solve my issue; still have the same warnings!
Hitesh, You might be right (maybe), but yet this again does not solve my problem...

Frustrating....

If the report hangs , may be 'lattr.print = false' does not reach in after.report.2 before.layout section.

Can u elaborate what the problem is now.

The report does not hang, the report just never reaches the r.s.after.report.2.aft.lay and r.s.after.report.3.bef.lay sections (verified through debugging). Those sections in the report script, are not recognized for some reason, since using the 'not exist' subquery. When putting the lines of the subquery in comment, there is no problem, and no warning by compiling. Now, when compiling I get errors indicating that the report script sections, are seen to the system as labels....

I have seen this in Baan IV in following situations.

1. After layout is executed only when layout is printed .If layout is not printed no after layout is executed.(may be lattr.print set to false either in print condition or in report script for after.report.2).

2. If the print condition for layout evaluates to false , before layout too is not executed. Though u seem to have checked everything , just check whether there is after.report.3 layout in the report layout.

Hitesh,

Lattr.print is set to true on all those layouts.

During runtime in r.s.after.report.2.bef.lay, the report goes through all the code of section r.s.after.report.2.bef.lay, r.s.after.report.2.aft.lay, and r.s.after.report.3.bef.lay (which is basically till the end of the report script). During runtime Baan does not recognize the last 2 sections, they do not exist at runtime, for they are seen as part of after.report.2.bef.lay, as labels (as quoted on the warnings)....

Furthermore, if I take section after.report.3.bef.lay, and put it up in the report script, for instance before after.report.1, then I receive only one warning, on the after.report.2.aft.lay, but no warning regarding after.report.3.


Thus, it is very clear, that like I said, the problem is that those sections in the program script are not recognized as seperate sections, but rather seen as kind of labels (???), within the after.report.2.bef.lay section.

When removing the nested subquery 'where not exist', the problem does not occur.

I am getting more and more curious...!!

hello en@frrom

I was digging a little bit in my documentation to solve your described problem.
Acutually I would try the following way.

I have adapted parts of your query statement

May you have a try, if you haven't solved the problem yet


declaration:
long sqlid
long ret
string query(500)

before.program:
query = "select tcmcs003._index1 " &
"from tcmcs003 " &
"where tcmcs003._index1 > {:1} " &
"and not exists " &
"(select tcmcs045 " &
"from tcmcs045 " &
"where tcmcs045.creg = tcmcs003.cwar " &
"order by tcmcs003._index1)"
sqlid = sql.parse(query)

after.report.1:
after.layout
sql.where.bind(sqlid,1,creg.cwar)
sql.exec(sqlid)
after.report.2:
before.layout:
if sql.fetch(sqlid) <> 0 then
lattr.print = false
sql.break(sqlid)
else
| Insert your additional select statements to get the
| Data from the wh* Tables
endif

after.layout
layout.again()



/Lakoon

Hi En ,

So when u use 'not exist' sub query, the report does not recognize the
sections. Those are treated as part of before layout for after.report.2.

Very strange.Just try putting all those lines above the after.report.2.bef.lay
in the report script . Alternatively re-organize the logic so as not to
use the 'not exists' clause.

U may report this 'not exists' issue to support.

Good luck.

Hi Hitesh,

Putting the other layout sections above the after.report.2 section in the script itself, indeed solves the issue, yet not the whol eissue, because I need to still have the after.report.2.aft.lay section where I call layout.again(). This I cannot move up, for then I will receive an error saying after.report.2 section already declared....

To use an alternative method, I couldn't yet think of anything. I unfortunately have never yet worked with dynamic sql. As a matter of fact I always avoided it, for I do not have enough knowledge of it. Maybe now will be a good time to play around with dynamic sql a little. Thanks Lakoon to your suggestions, I will look into it.

As for reporting the issue to Baan: I have already logged a case by Baan Support a couple of days ago; still haven't received any response from their side...

Wishing all a wonderful day!!!

En.

Hitesh,

as I see there are two things to do: 1) determine the data you like to see on your report and 2) how to get them onto the report.

part 2) has been discussed by the "loop" consisting of after.report.x and layout.again() combinations.

part 1) has been tried by different approaches with more ore less tricky ssql statements -- why don't you try my quite simple approach that I posted recently?

Günther

En,

By shifting the code before after.report.2.bef.lay , I did not mean
change in logic . Just change in script arrangement like


after.report.3:
before.layout:
|actual code
after.report.2:
after.layout:
layout.again()
before.layout:
|complete code with 'not exists'


Not sure it helps or not . Suggested this because label error indicates
system does not recognize it as valid layout . This means it's either
treated as continuation of earlier code or the layout does not really exist.

Gunther,
Indeed the code suggested by u will accomplish the results. We just persisted
in the Andreas' approach becasue En had worked lot with that code and I wished
the results to come with that code itself. En should try this code too.

En,

The code suggested by Gunther & Lakoon are the alternative and
I add more to it with basic structure derived from the code pasted by u.


domain tcbool crgexst
select tcmcs003.*
from tcmcs003
where tcmcs003._index1 > {:creg.cwar}
order by tcmcs003._index1
selectdo
crgexst = false
select tcmcs045.*
from tcmcs045
where tcmcs045._index1 = {:tcmcs003.cwar}
selectdo
crgexst = true
endselect
if crgexst then
continue
endif
creg.cwar = tcmcs003.cwar
| all code to print the correct record in after.report.2.bef.lay
break
selecteos
if crgexst then
lattr.print = false
endif
selectempty
lattr.print = false
endselect


I know some SQL overhead in it but it's not high considering size of tcmcs tables.