Hi All

I want to create a Print Session as in below format:

-----------------------------------------------------------------------
Item Code Desc Apr May Jun .................. Jan Feb Mar Total
-----------------------------------------------------------------------

TCPFGPCB001 ABCD 100 50 120 12 282
Here month column contains production qty. I want to apply logic as first create temp. table as per above structure, extract data from live tables and replace it in to temp table and at last print temp table and then delete temp table.

I dont have experience to develop such logics, tell me how to write code to get it done. If someone is having code please post on the thread.

Thanx & Regards,

Ganesh

Hi,

Why are u insisting on a temporary table??? are u looking for some other functionality to be achieved by using temp table
You can achieve functionality in a straightforward way.
You can total the qtys in either scripts.

Please elaborate on your requirement.

Bye

Hi bigjack

I need to apply the same reqd. logic in many other sessions and also want to know how it works in BaaN. Regarding your suggestions of total the qty in script I dont know how to do sum (monthwise depends on date field) in SQL. In some other application there is provision in SQL like sum(cmonth(tisfc001.date)), it will give total qty monthwise.

Please tell me how to apply logic in BaaN.

Thanx


Ganesh

Hello, as long as you are talking about REPORTING rather than DISPLAYING in display sessions, you indeed should not have any need for temp tables.

If you need more detailed help, you should post us the exact requirements with all details.

As far as how to accumulate the records: if you will always report for one whole year, i.e. months 1 -> 12, you just declare an array field of 12, select all required, and then based on the date (i.e. month), you accumulate in the relevant array element, using the date conversion functions. For example in case of utc date format:

extern domain tcamnt totals(12)
domain tcmcs.long ret, month, dummy

select .......
selectdo
ret = utc.to.date(<date_field>, dummy, month, dummy, dummy, dummy, dummy)
totals(month) = totals(month) + <amount_field>

In the report you just make 12 columns, totals(1), totals(2) etc...

This is just a the concept, but hope it helps you..

Good luck!!
En

Hi Ganesh,

I back En's logic. Only one suggestion if you anly need this to be printed on the report then use the array only in report script.

Rita, I don't understand a) how you get to responding to this old thread now, and b) why you don't like the idea of populating the array fields in the program script.

Anyway, I will have to disagree with you; it should be done in program/script, rather than in report script. Why? the requirement was to have one total line per item, with 13 columns: 12 months and a total. This means that the data should be sent to the spooler once per item. In your suggestion of writing my code in the report-script, this will not be correct, since you will only get the total amount of all months (for that item)..!

What I suggested is, to calculate the values for the different months (= array fields) till the last record of the corresponding item is calculated, and then send the data to spooler, so like this you get one report line, with all the fields filled correctly.

NOTE: Of course technically it IS possible to write the logic in report script, by manipulating the input (for instance: send data to report for each line, and in report first calculating the corresponding month and updating array, and then setting lattr.print to false, until item changes, then lattr.print will be true for once etc.), but why would you...???

Regards,
En

Hi En

I am not aware that how to use arrays in prog.script. I will be thankful to you if you can paste a demo script if you have.

What I suggested is, to calculate the values for the different months (= array fields) till the last record of the corresponding item is calculated, and then send the data to spooler, so like this you get one report line, with all the fields filled correctly.

Thanks

Hi


declarations:
extern long i
extern long no(10)
extern string cc(5,10)

call the function ....

function try()
{
for i = 1 to 10
no(i) = i
cc(1,i) = chr$(64+i)&chr$(64+i)&chr$(64+i)
endfor
for i = 1 to 10
message("%d - %s",no(i), cc(1,i)
endfor

}

Hello,

This sample script takes planned inventory movements, and sums them up into time buckets, the time buckets are on the form as input fields.

This report is a shortage report, that shows when in time the shortage will be. I think it is sort of interesting.

This will give you some ideas of what is possible with arrays and program scripts and time. I send 1 record to the report for each item.



|******************************************************************************
|* tdinv9006 0 VRC B40C c4 live
|* Inventory Transactions by Multiple Items
|* bkl
|* 02-06-16 [15:45]
|******************************************************************************
|* Script Type: 4
|******************************************************************************

| SCR 441-BPM-62

| JRoberts, Sept 30, 2005
| Added Ability to run report for all parts that flow through a specific machine.

|****************************** DECLARATION SECTION ***************************

declaration:
table ttiitm001
table ttdinv150
table ttdinv001
table ttirou102


| ****** Form Variables **********************
extern domain tcitem item.f, item.t
extern domain tccitg citg.f, citg.t
extern domain tcsuno suno.f, suno.t
extern domain tcemno cplb.f, cplb.t
extern domain tccwar cwar.f, cwar.t
extern domain tcdate date.range(6)

extern domain tirou.mcno selected.machine

| ****** Program Variables
extern domain tcdate inv.to.date, next.req.date
domain tcqsl1 total.req
extern domain tcmcs.long days.supply, days.p.supply
extern domain tcnama suno.nama
extern domain tcqsl1 inv.on.hand

extern domain tcqsl1 short.array(8)
|****************************** PROGRAM SECTION ***************************
|****************************** ZOOM FROM SECTION ***************************
|****************************** FORM SECTION ***************************

form.1:
init.form:
get.screen.defaults()

field.item.f:
when.field.changes:
item.t = item.f

field.suno.f:
when.field.changes:
suno.t = suno.f

field.citg.f:
when.field.changes:
citg.t = citg.f

field.cplb.f:
when.field.changes:
cplb.t = cplb.f

field.cwar.f:
when.field.changes:
cwar.t = cwar.f

field.selected.machine:
after.input:
| if strip$(selected.machine) <> "" then
| to.field("cwar.f")
| endif

|****************************** CHOICE SECTION ***************************

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

choice.print.data:
before.choice:
db.retry.point()
tccom.dll0840.user.activity.trigger(logname$, prog.name$, 3)
commit.transaction()
on.choice:
if rprt_open() then
if strip$(selected.machine) = "" then
read.main.table()
else
find.items.per.machine()
endif
rprt_close()
else
choice.again()
endif

|****************************** FIELD SECTION ***************************
|****************************** MAIN TABLE SECTION ***************************
|****************************** FUNCTION SECTION ***************************

functions:
function find.items.per.machine()
{
domain tcdate today.date
today.date = date.num()
select tirou102.mitm
from tirou102
where tirou102._index2 = {:selected.machine}
and (tirou102.indt = 0 or tirou102.indt <= :today.date)
and (tirou102.exdt = 0 or tirou102.exdt >= :today.date)
selectdo
item.f = tirou102.mitm
item.t = tirou102.mitm
| cwar.f = " "
| cwar.t = "ZZZ"
read.main.table()
endselect

}

function read.main.table()
{
| short.array.1 = period 1 shortage
| short.array.2 = period 2 shortage
| short.array.3 = period 3 shortage
| short.array.4 = period 4 shortage
| short.array.5 = period 5 shortage
| short.array.6 = period 6 shortage
| short.array.7 = current on hand
| short.array.8 = total of periond 1 to 6.

long i

select tdinv001.*,
tiitm001.*
from tiitm001,
tdinv001
where tiitm001._index1 inrange {:item.f} and {:item.t}
and tiitm001.citg between :citg.f and :citg.t
and tiitm001.cplb between :cplb.f and :cplb.t
and tiitm001.suno between :suno.f and :suno.t
and tdinv001.item refers to tiitm001
and tdinv001.cwar between :cwar.f and :cwar.t
and tdinv001.stoc <= tdinv001.allo
selectdo

for i = 1 to 8
short.array(i) = 0

endfor
short.array(7) = tdinv001.stoc
get.planned.move()
endselect
}

function get.planned.move() |**********************************************
| This function reads the planned inventory movements from tdinv150.
{

long q
select *
from tdinv150
where tdinv150._index2 = {:tiitm001.item}
and tdinv150.cwar between :cwar.f and :cwar.t
| and tdinv150.kotr = tckotr.requirement
order by tdinv150.date
selectdo

if tdinv150.date <= date.range(1) then
if tdinv150.kotr = tckotr.requirement then
|requirements add, receipts subtract
short.array(1) = short.array(1) + tdinv150.qana
else
short.array(1) = short.array(1) - tdinv150.qana
endif
else

for q = 2 to 6
if tdinv150.date <= date.range(q) and tdinv150.date > date.range(q-1) then
if tdinv150.kotr = tckotr.requirement then
|requirements add, receipts subtract
short.array(q) = short.array(q) + tdinv150.qana
else
short.array(q) = short.array(q) - tdinv150.qana
endif
endif
endfor
endif

endselect

short.array(1) = short.array(1) - tdinv001.stoc

for q = 1 to 5
if short.array(q) < 0 then
short.array(q+1) = short.array(q+1) + short.array(q)
short.array(q) = 0
endif
endfor

short.array(8) = short.array(1) + short.array(2) + short.array(3) + short.array(4) + short.array(5) + short.array(6)
if short.array(1) > 0 or short.array(2) > 0 or short.array(3) > 0 or short.array(4) > 0 or short.array(5) > 0 or short.array(6) > 0 then
rprt_send()
else
|do nothing
endif
}

With all due respect to Rita and Jroberts, I don't think this all is necessary. The script I suggested will do the trick, and is extremely simple.

Ganesh, I already gave you the concept of the script, hereby again in a bit more detail, based on tisfc001:

extern domain tiqep1 qrdr(12)
extern domain tiqep1 tot.qrdr
domain tcmcs.long month
domain tcmcs.long ret

select tiitm001.item |// main selection on item master table, in order to avoid having to use hold-variables on report..
from tiitm001
where tiitm001._index1 inrange {:mitm.f} and {:mitm.t}
order by tiitm001._index1
selectdo
reset.vars()

select tisfc001.*
from tisfc001
where tisfc001._index1 inrange {:tiitm001.item, :dldt.f, :pdno.f}
and {:tiitm001.item, :dldt.t, :pdno.t}
order by tisfc001._index2
selectdo
ret = utc.to.date(tisfc001.dldt, dummy, month, dummy, dummy, dummy, dummy)
qrdr(month) = qrdr(month) + tisfc001.qrdr
tot.qrdr = tot.qrdr + tisfc001.qrdr
selectempty
endselect

rprt_send()
selectempty
endselect

function reset.vars()
{
tot.qrdr = 0
for i = 1 to 12
qrdr(i) = 0
endfor
}


All you still need to do, is in the report layout put the fields:
tisfc001.mitm
qrdr(1)
qrdr(2)
....
qrdr(11)
qrdr(12)
and for the total: tot.qrdr

Hope this helps you through now.. If not, don't hesitate...

Good luck!!
En

Hi Madam,

Thanks for the guidance. I did the same at my side.I given qrdr in report input filed and then put qrdr(1),qrdr(2)....qrdr(11),qrdr(12) in report layout then system gives error message as

mp235888707( 435): Error: Illegal number of subscripts for 'qrdr'.

Pls help me what to do?

Hi,

Go to report input fields - double click on the field - Set the depth to 5 or 6 depending on the size of the array.

Check attached.

Dear Bhushan sir,
Thanks for your lasr reply. I want I item wise , monthwise sale. And i written below script. But when I run this report reset of array is giving problem. It is showing wrong qty.Can you pls check my script?
|*******************************************************************************
|* cirtf5404m000 0 VRC B61O a live
|* Print Daily Sales Report
|* Installation user
|* 2011-09-20
|*******************************************************************************
|* Main table cisli245 Sales and Warehouse Order Invoice Lines, Form Type 4
|*******************************************************************************

|****************************** declaration section ****************************
declaration:

table tcisli245
table ttcibd001
table ttcmcs023
table ttdsls400
table tcisli205

long yearno, monthno, dayno, hrs, mins, secs
long month_dayno,hours,minutes,seconds
double tmp.var

domain tccom.bpid hold.ofbp.t

|* Report Field Variables
extern domain tcdate r.ddat
extern domain tcmcs.str15 r.invoice
extern domain tcwght r.net.wght,r.tot.ntwt
extern domain tcqsl1 r.dqua, tot.qrdr,r.tot.dqua,totamt1
extern domain tcamnt r.amti
extern domain tcamnt r.amth,newdt
extern domain tcccur r.ccur
extern domain tcaitm r.aitc
extern domain tcnama r.cust.nama
extern domain tccitg r.citg
extern domain tcmcs.st30m cisli245.corn
extern domain tcmcs.str60 r.convar |gk-n
extern domain tccom.bpid cisli245.stbp
extern domain tcnama tccom100.nama
extern domain tcmcs.str10 tmp.date
extern domain tcitem item
extern domain tcgld.docn docn
extern domain tcmcs.str60 r.convar



|------- Variables to hold total values

extern domain tcamnt r.tot.amti,totalamnt1,totalamnt2,totalamnt3,totalamnt4,totalamnt5,totalamnt6,
totalamnt7,totalamnt8,totalamnt9,totalamnt10,totalamnt11,totalamnt12


|* Form Field Variables
extern domain tcdate frm.inv.dt.f |* From Date
extern domain tcdate frm.inv.dt.t |* To Date
extern domain tfgld.ttyp frm.ityp.f |* From Trasaction Type
extern domain tfgld.ttyp frm.ityp.t |* To Trasaction Type
extern domain tfgld.docn frm.idoc.f |* From Invoice Number
extern domain tfgld.docn frm.idoc.t |* To Invoice Number
extern domain tccom.bpid frm.ofbp.f |* From Business Partner
extern domain tccom.bpid frm.ofbp.t |* To Business Partner
extern domain tcitem frm.item.f |* From Item
extern domain tcitem frm.item.t |* To Item
extern domain tccom.bpid bp
extern domain tcaitm cpart



extern domain tiqep1 qrdr(12)
extern domain tiqep1 total.qrdr,total.qrdr1,total.qrdr2,total.qrdr3,total.qrdr4,
total.qrdr5,total.qrdr6,total.qrdr7,total.qrdr8,total.qrdr9,
total.qrdr10,total.qrdr11,total.qrdr12
domain tcmcs.long month,i
domain tcmcs.long ret




|****************************** program section ********************************
|****************************** group section **********************************
group.1:
init.group:
get.screen.defaults()
hold.ofbp.t = frm.ofbp.t


|****************************** choice section ********************************
choice.cont.process:
on.choice:
execute(print.data)

choice.print.data:
on.choice:
|check.all.input()
if rprt_open() then
read.main.table()
rprt_close()
else
choice.again()
endif

|****************************** field section *********************************


field.frm.ityp.f:
when.field.changes:
frm.ityp.t = frm.ityp.f

field.frm.idoc.f:
when.field.changes:
frm.idoc.t = frm.idoc.f

field.frm.item.f:
when.field.changes:
frm.item.t = frm.item.f

|****************************** function section ******************************

functions:

function read.main.table()
{


domain tcbool l.need.cont
domain tcdate l.inv.dt.f, l.inv.dt.t
domain tcncmp l.curr.comp
domain whinh.shpm l.shpm
domain tccom.bpid l.hold.ofbp

yearno = 0
monthno = 0
dayno = 0

select cisli205.idoc:docn,cisli205.itbp:bp
from cisli205
where cisli205._index5 inrange{:frm.inv.dt.f,:frm.idoc.f} and {:frm.inv.dt.t,:frm.idoc.t}
and cisli205.itbp inrange {:frm.ofbp.f} and {:frm.ofbp.t}
|and cisli205.itbp = {:bp}
selectdo

select tcibd004.aitc:cpart,tcibd004.bpid:bp
from tcibd004
where tcibd004.bpid = {:bp}
selectdo


select cisli245.ityp,
cisli245.idoc,
cisli245.item,
cisli245.pric,
cisli245.corn,
cisli245.stbp,
cisli245.ddat,
cisli245.ddat:r.ddat,
|cisli245.stbp:l.hold.ofbp,
|cisli245.ctem:r.aitc,
|cisli245.ccur:r.ccur,
cisli245.dqua
from cisli245
where cisli245.idoc = :docn
|and cisli245.ddat inrange {:frm.inv.dt.f} and {:frm.inv.dt.f}
and cisli245.item inrange {:frm.item.f} and {:frm.item.t}
and cisli245.ctem = {:cpart}

selectdo

ret = utc.to.date(r.ddat,yearno,monthno,month_dayno,hours,minutes,seconds) |arya
newdt = monthno
qrdr(newdt) = qrdr(newdt) + cisli245.dqua
getitemdesc()



endselect

rprt_send()

endselect

reset.vars()
endselect

}

function getitemdesc()
{
select tcibd001.dsca
from tcibd001
where tcibd001._index1={:cisli245.item}
selectdo
endselect
}

function reset.vars()
{
tot.qrdr = 0
for i = 1 to 12
qrdr(i) = 0
endfor
}

Hi,

There seem to be an incorrect logic for selecting the records. To mention some of the incorrect logic(on a generic level)

1. Comparing only the document number will give you incorrect results. You should use, Transaction Type & Document Number Both.
2. The sequence of Selection to get Item/Part wise values, should be
A. tcibd004
B. cisli205
C. cisli245
3. You should use Indexes wherever possible for better performance
4. Debugging is the first best step towards investigating the problems. Try, running for a single item and single month and verify each transactions and check. Let it take time. Spending time in debugging is worth it.

Follow the mentioned steps and I hope you will make it.

Thank you Sir.

I will try this