Dear Concern,

We want report in vertical format. Herewith attaching the document for your reference.


Regards,
Arya

Hi,

Haven't you got training to create reports?

There are two ways in which you can create this report. 1st way is to use Classic Baan Report's which I think will not work in your case as it has a limit of 255 Characters only. The second way is to use seq.* functions to put your data in a file either excel or csv.

There's one more way to do it i.e. SSRS.

Now, going back to the logic, you can get all this data in table cisli245.

I have done this both ways that Bhushan has mentioned. My report was based off work center information.

The easiest way was to use variables for the headers and arrays for the details records. So basically my program determined something like 8 month-year ranges. So I had something like mon.year(8) for the headers - the header layout had mon.year(1) thru (8) in it.

Then I took my details layouts and had arrays for it also. I would take each work center get standards actuals for the month in question. So I had an array like act(8) - where (1) thru (8) corresponded to the header records for month and year. So the flow of the program at a high level:
(1) Get the months in question
(2) For each work center:
(3) for each of the 8 months requested
(4) determine the month start and end from calendar
(5) get actuals each month
(6) get standard each month
(7) get next month
(8) send to report
(9) get next work center requested

Now the program was a lot more complicated than that - since it actually let them pick the reporting type (weeks or months) and how many (weeks or months) to generate for the report. I think the max was 10 - but can't remember.

So the best way to approach this from your side is to generate a report that first works for just one month using arrays. Then add on however many months you need or fit on the report.

Yes,

As Mark said, the program can get a lot more complicated.
But, if you know the use of seq.* functions, you can do it in a simpler way as well. Just create a header with the 4 quarter's i.e 12 months which will be hardcoded with 1 column as Item code.

Take 12 variables for 12 months (in your case 12*4 = 48 :D). Now, based on the users input of date range, for every item, calculate the values for the variables. If the date range is for a few months out of 12, just keep the other variables as 0. When you collect the values for a single item, put it into the details lines. In this way build your report.

One more way is to take the input as year and quarter. So, you will need to have lesser variables & columns. Now, design the header with 3 months (in your case 3*4 = 12 variables). Now, add the months name in the header based on the users input.
This can be a stable way of doing it.

The only thing you will need to worry is how you can convince your users for the stable header.;) Later when you start going into advance programming, start developing the one as Mark mentioned, using arrays.

Dear Sir,

I know that we've limited space in report layout i.e 255. I've used tmp.date variable in report.
And in Script i'm using this query
tmp.date = str$(monthno)&"-"&str$(yearno)


Regards,
Arya

Dear Concern,

Waiting for your reply

Regards,
Arya

I am not sure who or what the question is? Yes - you are limited to the number of input fields and number of characters per report line. So you just figure out how many months you can report.

Dear Sir,

My problem is that I want report (month Sale report)in vertical format. I'm attached document for your reference and also I want this report for 12 month basis. For eg.if I gave range from June to October it should print vertically. Herewith I attached existing report which is coming in horizontal format.Both Report I'll sent the existing one and the required report format.


Regards,
Arya

As Bhusan and I have pointed out - you have 2 options. I would first limit the options to 3 or 4 months then you can determine how many months your report can support.

Option 1 is to redesign the initial program to use arrays. You could have 4 arrays by bp code - invoice qty, weight, assess value, and gross amount. Write one or multiple subroutines to update the data for the BP code. Send one record to the report. The layout would have all 4 arrays(assuming enough space on the report). I don't know you data, field limits, etc. So I do not know if there is enough space. On my reports I usually had less than a year - maybe 6 to 10 months.

Option 2 is to take the original report and write it to disk. Then ready it back in printing each month across the sheet. Again it may not have enough space. But you can read the data back into a record buffer.

Good Luck!

Hi,

tmp.date = str$(monthno)&"-"&str$(yearno)

This piece of code will do no change to your report. This is just concatinating two variables which is of no use for your requirement. We have no idea what you are trying to do with this line. Anyway, you can't expect people to prepare reports for you, rather you can start working on what you want first and later when you face any problems you can come back and post your queries.

Also, make sure you are well prepared with Baan Reporting & Programming. If you are, you should first create sample reports using a single table, then go for joins etc. For your requirement, you will need a basic understanding of how to use select loops, how to print records, how to convert dates.

As, you want month wise report, you will need to use special functions to convert your dates and much more. So, start with your self study. You can get Prog. Guide from Xtreme and study it. Also, there are many threads which will give you basic understanding of report printing. Also, when you are prepared, you can refer this thread too, Mark and I have already proposed few solutions for you.

I am sure you will be get this report done soon. All the best :)

Hi Aryaraj

As everyone is saying there are two way

1) where you create baan report ( this is generally for printing on paper, so there is a limitation on paper width , similarly you have limitation on baan report i.e 255 chars)

2) you can create a file at server ( just like notepad file, and you can write as much data as you want with out limitation) and transfer it local machine and open it as csv file


i have used spool functions which is similar to creation of file , please go through the script and try to get the flow. ( check this function write.data(long rpt.no) )

code not compiled ( as i simplified it)

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

table twhinh501 | Cycle Counting Order Lines
table twhinh500
table twhinh521
table twhinh520
table ttcibd001
table ttcmcs023



extern domain tcorno orno.f fixed
extern domain tcorno orno.t fixed
extern domain tcpono pono.f, pono.t
extern domain tccwar cwar.f, cwar.t
extern domain whloca loca.f, loca.t
extern domain tcmcs.str40 rpts fixed
extern domain tcmcs.str30 type fixed
extern domain tcmcs.str30 dtsl fixed
extern domain tcitem item.f,item.t
extern domain tccitg citg.f,citg.t
extern domain whinh.vara appr.f,appr.t
extern domain whinh.csst ccst.f,ccst.t
extern domain tcncmp comp.f,comp.t
extern domain tfgld.prod prno
extern domain tfgld.year year
extern domain tcdate date.f,date.t
extern domain tcmcs.str40 list(3) | report selection
extern domain tcmcs.str30 list1(3) | for type of report
extern domain tcmcs.str30 list2(3) | for date selection
extern domain tcdsca cdis.dsca

domain tccopr ecpr
string bucket(700),mon_year(10)
long rpts.option , dummy ,comp , type.option ,s.spool.id , dtsl.option,yy,mm
boolean rpt1.header,rpt2.header,split.year
double qvrc , amnt
domain tcsern cntn.f , cntn.t

#define delimiter "|"
|chr$(9)

|****************************** program section ********************************


|****************************** group section **********************************

group.1:
init.group:
get.screen.defaults()
disable.group(3)
disable.fields("date.f","date.t","prno")


|****************************** choice section ********************************

choice.cont.process:
before.choice:
check.all.input()
on.choice:
execute(print.data)

choice.print.data:
before.choice:


after.choice:
read.main.table()
if s.spool.id > 0 then
spool.close()
else
Message("No data with in selection")
endif



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


field.comp.f:
init.field:
comp.f = get.compnr()
comp.t = comp.f

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

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

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

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

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

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

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

field.date.f:
when.field.changes:
date.t = date.f
field.ccst.f:
when.field.changes:
ccst.t = ccst.f

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

field.rpts:
init.field:

check.input:

when.field.changes:





field.dtsl:
init.field:

check.input:

if isspace(dtsl) then
set.input.error("tcgenstring","Please fill the Date Selection")
endif
when.field.changes:

field.type:
init.field:
list1(1,1) = "Detail Report"
list1(1,2) = "Report By Item / Month"
list1(1,3) = "Report By Item Group / Month"
set.list.values.for.field ("type",3,list1)
type = "Detail Report"
type.option = 1
check.input:

if isspace(type) then
set.input.error("tcgenstring","Please fill the Report Type Selection")
endif

field.year:
init.field:
num.to.date(date.num(),year,dummy,dummy)
date.f = date.to.utc (year,1,1,0,0,0)
if utc.add( date.f, 1,0 , 0, 0, 0, -1, date.t ) <> 0 then
message("Error While Calculating date")
endif

check.input:
if dtsl.option <> 3 and year = 0 then
set.input.error("tcgenstring","Invalid Year")
endif
when.field.changes:



field.prno:
check.input:
if dtsl.option = 2 and prno = 0 then
set.input.error("tcgenstring","Invalid Year")
endif
when.field.changes:



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

functions:

function read.main.table()
{
long curr.comp
curr.comp = get.compnr()

select tcemm170.comp:comp
from tcemm170
where tcemm170._index1 inrange {:comp.f} and {:comp.t}
and tcemm170.ctyp <> tcemm.ctyp.financial
selectdo
rpts.option = 1
type.option = 3
if switch.to.company(comp) = 1 then
on case type.option

case 3:
on case rpts.option
case 1:
get.cycle.counting.by.itemgroup()
break
break
default:
break
endcase
endif
selecteos
switch.to.company(curr.comp)
endselect
}



|********* Functions related to report by item group


function get.cycle.counting.by.itemgroup()
{
long i
domain tcdate month.first.day , month.last.day
select
tcmcs023.citg, | item group
tcmcs023.dsca, | Description
sum(whinh501.qvrc):qvrc, | Variance Qty
sum(whinh501.amnt):amnt | Valuation Amount

from
whinh500, | Cycle Counting Orders
whinh501, | Cycle Counting Order Lines
tcibd001, | Items - General
tcmcs023
where whinh501._index1 inrange {:orno.f, :cntn.f, :pono.f}
and {:orno.t, :cntn.t, :pono.t} and
whinh500._index1 refers to whinh501 and
whinh501.item refers to tcibd001 and
tcibd001.citg refers to tcmcs023 and
whinh501.item inrange :item.f and :item.t and
whinh501.cdat inrange :date.f and :date.t and
whinh501.cwar inrange :cwar.f and :cwar.t and
whinh501.loca inrange :loca.f and :loca.t and
tcibd001.citg inrange :citg.f and :citg.t and
whinh500.ccst inrange :ccst.f and :ccst.t and
((:rpts.option <> 2 and whinh501.appr inrange :appr.f and :appr.t )
or (:rpts.option = 2))
group by tcmcs023.citg,tcmcs023.dsca
selectdo
bucket = ""
if not split.year then
bucket = str$(qvrc)& delimiter & str$(amnt)& delimiter
else

for i = 1 to 12 step 1
month.first.day = date.to.utc (year,i,1,0,0,0)
if utc.add( month.first.day, 0, 1, 0, 0, 0, -1, month.last.day ) <> 0 then
message("Error While Calculating date")
endif

get.cycle.counting.by.itemgroup.and.month( month.first.day,
month.last.day,
tcmcs023.citg )


endfor
endif
write.data(1)
endselect

}


function get.cycle.counting.by.itemgroup.and.month(domain tcdate i.date.f ,
domain tcdate i.date.t ,
domain tccitg i.citg)
{

| double qvrc , amnt
select tcibd001_1.citg,sum(whinh501_1.qvrc):qvrc,sum(whinh501_1.amnt):amnt | Valuation Amount

from whinh500 whinh500_1,whinh501 whinh501_1,tcibd001 tcibd001_1
where whinh501_1._index1 inrange {:orno.f, :cntn.f, :pono.f}
and {:orno.t, :cntn.t, :pono.t} and
whinh500_1._index1 refers to whinh501_1 and
whinh501_1.item refers to tcibd001_1 and
whinh501_1.item inrange :item.f and :item.t and
whinh501_1.cdat inrange :i.date.f and :i.date.t and
whinh501_1.cwar inrange :cwar.f and :cwar.t and
whinh501_1.loca inrange :loca.f and :loca.t and
tcibd001_1.citg = :i.citg and
whinh500_1.ccst inrange :ccst.f and :ccst.t and
((:rpts.option <> 2 and whinh501_1.appr inrange :appr.f and :appr.t )
or (:rpts.option = 2))
group by tcibd001_1.citg
selectdo
bucket = bucket & str$(qvrc)& delimiter & str$(amnt)& delimiter
selectempty
bucket = bucket & " " & delimiter & " " & delimiter

endselect


}




|************* Common Functions

function write.data(long rpt.no)
{
| Has 6 cases depending on type.option and rpt.no

string bucket.header(600)
if s.spool.id = 0 then
s.spool.id = spool.open("","EA",0)
if s.spool.id > 0 then
if split.year then | dtsl.option = 1 and group by reports
bucket.header = concat$(delimiter, "Jan-"&str$(year)&" Var. Qty", "Jan-"&str$(year)&" Var. Amt",
"Feb-"&str$(year)&" Var. Qty", "Feb-"&str$(year)&" Var. Amt",
"Mar-"&str$(year)&" Var. Qty", "Mar-"&str$(year)&" Var. Amt",
"Apr-"&str$(year)&" Var. Qty", "Apr-"&str$(year)&" Var. Amt",
"May-"&str$(year)&" Var. Qty", "May-"&str$(year)&" Var. Amt",
"Jun-"&str$(year)&" Var. Qty", "Jun-"&str$(year)&" Var. Amt",
"Jul-"&str$(year)&" Var. Qty", "Jul-"&str$(year)&" Var. Amt",
"Aug-"&str$(year)&" Var. Qty", "Aug-"&str$(year)&" Var. Amt",
"Sep-"&str$(year)&" Var. Qty", "Sep-"&str$(year)&" Var. Amt",
"Oct-"&str$(year)&" Var. Qty", "Oct-"&str$(year)&" Var. Amt",
"Nov-"&str$(year)&" Var. Qty", "Nov-"&str$(year)&" Var. Amt",
"Dec-"&str$(year)&" Var. Qty", "Dec-"&str$(year)&" Var. Amt" )
endif

else
Mess("gbinh5401.1",1)
|Error while opening the Spool
end()
endif
endif

if not rpt2.header and not rpt1.header then |#627478-002.sn

spool.pr.line = concat$( delimiter , "Company",
"Order Type",
"Item Group",
"Description",
trim$(bucket.header)
)
spool.line()

rpt2.header = true
endif |#627478-002.en



|cycle counting by item group
|#627478-002.eo

spool.pr.line = concat$( delimiter , comp,
"Cycle Counting Order",
tcmcs023.citg, | item group
trim$(tcmcs023.dsca), | Description
trim$(bucket)
)
spool.line()


endif
break

endcase



}





function get.standard.cost.price( domain tcitem i.item)
{
select ticpr007.ecpr:ecpr
from ticpr007
where ticpr007._index1 = {:i.item}
as set with 1 rows
selectdo
selectempty
ecpr = 0
endselect
}


function get.reason.code.desc(domain tccdis i.cdis)
{
select tcmcs005.dsca:cdis.dsca
from tcmcs005
where tcmcs005._index1 = {:i.cdis}
as set with 1 rows
selectdo
selectempty
cdis.dsca = ""
endselect
}

Dear Sir,

Waiting for your reply.

Regards,
Arya

Dear Sir,

For month in horizontal format we have tried the below coding, and used totals(1),totals(2),.........,totals(12) this field in report layout. But still not getting. Please help us in this matter.

reset.vars()
utc.to.date(r.ddat,yearno,monthno,month_dayno,hours,minutes,seconds)
tmp.date = str$(monthno) & "-" & str$(yearno)

ret = utc.to.date(r.ddat, dummy, month, dummy, dummy, dummy, dummy)
totals(month) = totals(month) + cisli245.amth(1)
tot.amnt = tot.amnt + cisli245.amth(1)

function reset.vars()
{
tot.amnt = 0

for i = 1 to 12
totals(i) = 0
endfor
}

What exactly is the problem? This code looks fine. It will reset the total amount to 0 then add 1 amount to 1 month. Now I would expect something like where you reset totals to 0 then do a select for maybe those cisli records and add each one to the appropriate month. Then after all that totaling you do a report send for the record. Then on the report you add 12 fields in the detail layout - totals(1) thru totals(12). That would work.