I have written a program for monthly sales report for a year 2015.
When this program is defined as report layout it is fine. But, the same script if I want the output to excel there are some modifications which I have perfromed in the script. But the output is not as desired. I have attached my script as well as my Excel file for your reference.

Check the Excel Sheet1 and Excel Sheet 2 . I want Excel Sheet1 layout to be same as Excel Sheet 2 layout.
Excel file attached

In the first sheet I have written a script as follows.

#pragma used dll ottdllbw
#pragma used dll ottstpapihand
#include <bic_tt>



|****************************** program section ********************************
before.program:

determine.number.of.periods.by.year()
determine.current.year.and.period()


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

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

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

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


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



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



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

functions:

function read.main.table()
{
error.log = bse.tmp.dir$()
error.log = strip$(shiftl$(error.log)) &"\Error_Log_"&dte$()&".txt"
error.log.ptr = seq.open(error.log, "w+")


save.ctyp = ""
save.item = ""
qty(12) = 0.0
|save.item = ""
whse.count = 0
first.pass = tcyesno.yes

temp.line = concat$("|" ,"Employee"
,"Employee Description"
,"January"
,"February"
,"March"
,"April"
,"May"
,"June"
,"July"
,"August"
,"September"
,"October"
,"November"
,"December")
seq.puts(temp.line,error.log.ptr)
seq.puts("",error.log.ptr)
read.qty()
}

function read.qty()
{
cisli240.crep = ""
save.dsca = ""
qty(12) = 0.0
whse.count = 0
| month.in.year = 0
curr.comp = get.compnr()

select cisli240.*,cisli245.*
from cisli240,cisli245
where cisli240.crep <> ""
and cisli240._index1 = {:curr.comp,1,3,cisli245.slso,cisli245.oset}
and cisli240.crep inrange (:crep.f) and (:crep.t)
order by cisli240.crep,cisli245.apdt
selectdo
reset.vars()
ret = utc.to.date(cisli245.apdt,period.year,month.in.year,day.in.month,hrs2,mins2,sec2)
if cur.year = period.year then
qty(month.in.year) = qty(month.in.year) + (cisli245.amth(1)-cisli245.ldai)
tot.qty = tot.qty + (cisli245.amth(1)-cisli245.ldai)
read.tccom001()
rprt_send()
write.line()
else
endif
selectempty
endselect

close.and.transfer.file()
}

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

}

function determine.number.of.periods.by.year()
{
year.peri = 12
}

function determine.current.year.and.period()
{
long year.no,mnth.no,mnth.day


utc.to.date(utc.num(),year.no,mnth.no,mnth.day,hrs3,mins3,sec3)
cur.year = year.no
cur.peri = mnth.no
cur.day = mnth.day
|message ("year.no % d,mnth.no %d,mnth.day %d",year.no,mnth.no,mnth.day)
}
function read.tccom001()
{

select tccom001.nama:save.dsca
from tccom001
where tccom001._index1 = {:cisli240.crep}
selectdo
selectempty
cisli240.crep= "XXXXX"
save.dsca= "Other Sales"
endselect
}





function close.and.transfer.file()
{
seq.close(error.log.ptr)
create.local.directory ("C:\tmp")
|
client.file = "C:\tmp\Error_Log_"&dte$()&".xls"
|
|
server2client( error.log,client.file ,false)
app_start ( client.file, "C:\tmp","","","")
| message("File is created at "&client.file)
}
function write.line()
{
item.no = item.no + 1
if item.no >0 then

temp.line = concat$("|" ,cisli240.crep
,save.dsca
,qty(1)
,qty(2)
,qty(3)
,qty(4)
,qty(5)
,qty(6)
,qty(7)
,qty(8)
,qty(9)
,qty(10)
,qty(11)
,qty(12))
seq.puts(temp.line,error.log.ptr)
endif
|seq.puts("",error.log.ptr)
}

It looks like spreadsheet 1 is just all of the details that go into a summary - which is what spreadsheet 2 looks like. If that is the case then instead of printing all of the details lines in the script - sum them up then only write the totals. So when the employee number changes write the record and reset the totals to 0.

What I typically do is just clone the report to another report on the same session. Then strip out all of the stuff not needed in excel(like page titles, etc.). Then the user can just pick that report and run it straight to an excel device we have. Saves a lot of coding.

How to sum up from this script and where should I make it to zero in the script?

I would do it in this routine:

select cisli240.*,cisli245.*
from cisli240,cisli245
where cisli240.crep <> ""
and cisli240._index1 = {:curr.comp,1,3,cisli245.slso,cisli245.oset}
and cisli240.crep inrange (:crep.f) and (:crep.t)
order by cisli240.crep,cisli245.apdt
selectdo
reset.vars()
ret = utc.to.date(cisli245.apdt,period.year,month.in.year,day.in.month,hrs2,mins2,sec2)
if cur.year = period.year then
qty(month.in.year) = qty(month.in.year) + (cisli245.amth(1)-cisli245.ldai)
tot.qty = tot.qty + (cisli245.amth(1)-cisli245.ldai)
read.tccom001()
rprt_send()
write.line()
else
endif
selectempty
endselect

close.and.transfer.file()
}


The way I figure it you are writing the report and a file so I would do something like this - now I assume you are currently using the same variables to write the file and the file. That would have to change.

select cisli240.*,cisli245.*
from cisli240,cisli245
where cisli240.crep <> ""
and cisli240._index1 = {:curr.comp,1,3,cisli245.slso,cisli245.oset}
and cisli240.crep inrange (:crep.f) and (:crep.t)
order by cisli240.crep,cisli245.apdt
selectdo
reset.vars() | Assumed this is the report variables
if changed(cisli240.crep) then
write.line()
reset.file.vars() | Reset the tot variable to empty
endif
ret = utc.to.date(cisli245.apdt,period.year,month.in.year,day.in.month,hrs2,mins2,sec2)
if cur.year = period.year then
qty(month.in.year) = qty(month.in.year) + (cisli245.amth(1)-cisli245.ldai) | Assumed this was for the report
tot(month.in.year).qty = tot.qty(month.in.year) + (cisli245.amth(1)-cisli245.ldai) | used this for the file
read.tccom001()
rprt_send()
else
endif
selectempty
endselect
write.line()
close.and.transfer.file()
}

Now when you write the file use the tot() array. This should give you an idea of what I am talking about.

What i would suggest is include a function which calculates the last record of the current cisli245.crep and cisli245.apdt combination,hope this is clear. I mean if for a given Representative there are 50 records, my Function would store the parameters of the 50th record and then i would set a comparison to compare whether each record is that 50th Record and only when the record is the last or the 50th record i would type rprt_send().



function read.qty()
{
cisli240.crep = ""
save.dsca = ""
qty(12) = 0.0
whse.count = 0
| month.in.year = 0
curr.comp = get.compnr()
boolean first.time
first.time = true

select cisli240.*,cisli245.*
from cisli240,cisli245
where cisli240.crep <> ""
and cisli240._index1 = {:curr.comp,1,3,cisli245.slso,cisli245.oset}
and cisli240.crep inrange (:crep.f) and (:crep.t)
order by cisli240.crep,cisli245.apdt
selectdo
|mycode.sn
if first.time then
calculate.last.record.of.current.comb()
end
|mycode.en
reset.vars()
ret = utc.to.date(cisli245.apdt,period.year,month.in.year,day.in.month,hrs2,mins2,sec2)
if cur.year = period.year then
qty(month.in.year) = qty(month.in.year) + (cisli245.amth(1)-cisli245.ldai)
tot.qty = tot.qty + (cisli245.amth(1)-cisli245.ldai)
read.tccom001()
if (cisli245.apdt = last.apdt and cisli240.crep = last.crep) then |mycode.n
rprt_send()
endif |mycode.n
write.line()
else
endif
selectempty
endselect

close.and.transfer.file()
}

Also the code for calculating the last record for the current crep and apdt combination, (Selecting Temporary Table)


select t240.crep:last.crep,t245.apdt:last.apdt
from cisli240 t240,cisli245 t245
where t240.crep <> ""
and t240._index1 = {:curr.comp,1,3,cisli245.slso,cisli245.oset}
and t240.crep inrange (:crep.f) and (:crep.t)
order by t240.crep,t245.apdt desc
and as set with 1 rows
selectdo
selectempty
last.crep = ""
last.apdt = 0
endselect