Good afternoon BaaN gurus,

Hope everyone is doing well? I am sure most of you guys must have a sales report which would supply the sales representatives or finance department with their top customers.

I have a SQL query at present which does what is required but am trying to get it converted into a standard BaaN session and report type.

I feel like I have done everything correct, however when I try to run the session and the report, firstly the report promts me twice to select the device, then secondly it feels like it does nothing as the results never appear.

Could you possibly assist me with identifying what I have done wrong?

Thank you in advance.

Hi chris

I found two issue in your code, if you fix them hopefully your problem will get solved.

1.) comment the under-mention code in your script

choice.cont.process:
before.choice:
execute( print.data)

2.) provide some value to variable 'ckor'

Let me know if that solves your problem

I would make several recommendations. Move the execute print to on.choice. Also include a rprt_close. And to keep things simple instead rprt_send() instead of print.record(). A little sample code below. I would also drop the tdslsckor inrange statement. You already have it in the index.

choice.cont.process:
on.choice:
execute(print.data)

choice.print.data:
on.choice:
if(rprt_open())then
| Fetch records.
ckor = 3
print.turnover.lines()
rprt_close()
else
choice.again()
endif
functions:
function print.turnover.lines()
{
select tdsls051.*
from tdsls051
where tdsls051._index3 inrange {:ckor, :cuno.f, :cprj.f } and {:ckor, :cuno.t, :cprj.t }
and tdsls051.trdt inrange :date.f and :date.t
and tdsls051.orno inrange :orno.f and :orno.t
order by tdsls051._index3
selectdo
rprt_send()
endselect
}

A bit addition to Mark's code:-

choice.cont.process:
on.choice:
execute(print.data)

choice.print.data:
on.choice:
if(rprt_open())then | Fetch records.

ckor = 3
print.turnover.lines()
rprt_close()
else
choice.again()
endif
functions:
function print.turnover.lines()
{
select tdsls051.*
from tdsls051
where tdsls051._index3 inrange {:ckor, :cuno.f, :cprj.f } and {:ckor, :cuno.t, :cprj.t }
and tdsls051.trdt inrange :date.f and :date.t
and tdsls051.orno inrange :orno.f and :orno.t
order by tdsls051._index3
selectdo
get.bp.name() | To get BP Name
rprt_send()
endselect
}


function get.bp.name()
{
db.set.to.default(ttccom010)
select tccom010.*
from tccom010
where tccom010._index1 = :tdsls051.cuno
selectdo
endselect
}

I just went through your script. I guess you want the total's of each Business Partner.

So, you can do this:-

Declare a variable as:-

extern domain ttsql.doub float0

choice.cont.process:
on.choice:
execute(print.data)

choice.print.data:
on.choice:
if(rprt_open())then | Fetch records.

ckor = 3
print.turnover.lines()
rprt_close()
else
choice.again()
endif
functions:
function print.turnover.lines()
{
select tdsls051.ckor, tdsls051.cuno, tdsls051.cprj, tdsls051.trdt, tdsls051.orno, sum(tdsls051.amta):float0
from tdsls051
where tdsls051._index3 inrange {:ckor, :cuno.f, :cprj.f } and {:ckor, :cuno.t, :cprj.t }
and tdsls051.trdt inrange :date.f and :date.t
and tdsls051.orno inrange :orno.f and :orno.t
group by tdsls051.cuno
selectdo
get.bp.name() | To get BP Name
rprt_send()
endselect
}


function get.bp.name()
{
db.set.to.default(ttccom010)
select tccom010.*
from tccom010
where tccom010._index1 = :tdsls051.cuno
selectdo
endselect
}


Now, on your report layout :-

Insert float0 as Report Input Field with Print Format as %A005

Now, if you print your report, you will get the total amta of each BP.

I am not sure if this is your requirement, but you can try this as well.

Thank you everyone - you guys have been an absolute star. I will try the recommendations out and keep you posted as to the results.

Mark, thank you for pointing out that I am making reference to the ckor twice and thank you for correcting my "before.choice" to "on.choice"

Ok guys, have made the changes as requested. Attached are the errors that I receive after adding the two extra items:
Error 1 is when I add the extra function call get.bp.name() - I do require this so thank you
Error 2 is when I remove the extra function call but use the "select tdsls051.ckor, tdsls051.cuno, tdsls051.cprj, tdsls051.trdt, tdsls051.orno, sum(tdsls051.amta):float0"

both extras which have been recommended would be appreciated, I can see what is being done by the float0 variable declaration. I think that it would require more tweeking or else adding more of the options to the "group by" in order for this to work.

Hi,

Add float0 as Report Input Field with Domain ttsql.doub and Print Format as %A005 instead of tdsls051.amta and try again.

Also, I am not sure about the fields in index3 of this table as I am on LN.

So, you can try this:-

function print.turnover.lines()
{
select tdsls051.ckor, tdsls051.cuno, tdsls051.cprj, tdsls051.trdt, tdsls051.orno,
sum(tdsls051.amta):float0
from tdsls051
where tdsls051.ckor = :ckor
and tdsls051.cuno inrange {:cuno.f} and {:cuno.t}
and tdsls051.cprj inrange {:cprj.f} and {:cprj.t}
and tdsls051.trdt inrange :date.f and :date.t
and tdsls051.orno inrange :orno.f and :orno.t
group by tdsls051.cuno
selectdo
get.bp.name()
rprt_send()
endselect
}

function get.bp.name()
{
db.set.to.default(ttccom010)
select tccom010.*
from tccom010
where tccom010._index1 = :tdsls051.cuno
selectdo
endselect
}

Error 1 is easy. Add brackets to tccom010._index1 = :tdsls051.cuno - so it should be tccom010._index1 = {:tdsls051.cuno}. The variables for an index need to be surrounded by {}'s.

Second error is all those fields need to be added to the group by statement. The reason being those fields may not be the same for all cuno's and that makes a difference in what the sum might be.

Hi Mark,

Does that matter if I don't put a {} around the fields where I have used index?

After you said that, I tried it some of my scripts and it runs without any error.

I guess its only required in Baan IV. And, for second error, again I don't have problem with just one field as Group By. But again, I am on Ln, so it might be different on IV.

Never really knew that - I thought the {}'s were required for both, but I know it is for 4c4.

May be LN does the group by differently, but I would think you would need all the fields - otherwise what total would it give if you have 1 customer and 3 projects? Would it give the total by project which means it included it in the group by statement or would it give the same 3 totals for each of the projects? Again on 4c4 if makes you put each field in the group by. Again maybe it is ln versus 4c4.

Thank you - will give it a try in the next few minutes

It was definitely the brackets thank you Mark. I will try make a mental note.