I hv already written the report and its successfully coming to excel but while m groping a field its not coming group wise in excel
and how to make total of the any grouping field ....before.field,after.field are not working here .


hope u can understand my prob,,

Moved to correct forum.

Can you post a snapshot of your layouts?

hello,
this is my layout
while m displaying only, it coming exactly but in excels the grouping field and the grouping total calculation r not coming ...

when v using excel to print our report Is there something else to make group of any field and the total calculation
help me out boss,,

Hi,

The layout seems ok to me. I am guessing, you are having a problem with the format you see in excel. For that, you will need to add pipe seperators under all columns in your after.field section of layout and the totals will be appearing at the right place.

hello,
M giving u both report output sheet,
when I do general display its showing what ever I want but,,
when it coming to excels it looks totally different
hope after watching both result u can understand

Hi,

They are totally different. Check you script. If the device is excel then either they are calling some other report or using file functions to create the excel file.

Hi bhushan,,
This is my script check it out ..
And it is exactly coming when printing the report in display mode.

I think there is some different method to print tht group and subtotal those I was printing in display mode, can u share ur knowledge abt it

functions:
function generate.file()
{
domain tcloco v.user
domain tcbool bool.flag
long exitCode,ret
v.user = logname$
err.file = bse.tmp.dir$() &"\" & v.user & ".csv"
file.name = bse.tmp.dir$() &"/" & v.user & ".csv"

stat.fp = seq.open(err.file, "w+")
process.file()
seq.close(stat.fp)


if ( api.mode = 1 or job.process ) then
import("export.file.name",import.file.name)
ret = file.mv(file.name, import.file.name)
if ret = 0 then
message("Report Copied to path -"&import.file.name)
else
message("Error while copying file to path -"&import.file.name)
endif
else
local.file.name = "C:\temp\tfgldf480m00c."&str$(utc.num())
ret2 = server2client(file.name,local.file.name,false)
if ret2 <> 0 then
ret2 = create.local.directory("C:\temp")
if ret2 <> 0 then
message("Error while transfering report file to Client C:\temp Directory")
else
ret2 = server2client(file.name,local.file.name,false)
endif
endif
seq.unlink(file.name)
bool.flag = start.application.local("Excel "&local.file.name, FALSE, exitCode )
if bool.flag = false then
message("Error while transfering report file to Client C:\temp Directory")
endif
endif
}


function process.file()
{
generate.header()
read.main.table()
}


function read.main.table()
{
date.f = date.to.num (year.f,perd.f,1)
if perd.t = 12 then
date.t = date.to.num (year.t,perd.t,31)
else
date.t = date.to.num (year.t,perd.t+1,1)
date.t = date.t - 1
endif


select tdsls041.*,tdsls040.*
from tdsls041,tdsls040
where tdsls041._index1 inrange {:orno.f,:pono.f} and {:orno.t,:pono.t}
and tdsls041.cuno between :cuno.f and :cuno.t
and tdsls041.item between :item.f and :item.t
and tdsls041.ddta between :date.f and :date.t
and tdsls040.orno = tdsls041.orno

selectdo
select tiitm001.dsca,tiitm001.stoc,tiitm001.ordr,tiitm001.blck
from tiitm001
where tiitm001._index1 = {:tdsls041.item}
selectdo
selectempty
tiitm001.dsca = ""
endselect
select tccom010.name
from tccom010
where tccom010._index1 = {:tdsls041.cuno}
selectdo
selectempty
tccom010.name = ""
endselect

write.data()

rprt_send()
selectempty
endselect
}

function generate.header()
{
if stat.fp >0 then
line =concat$("|",
sprintf$("ITEM"),
sprintf$("ITEM DESCRIPTION"),
sprintf$("ORDER"),
sprintf$("POSITION"),
sprintf$("ORDER DATE"),
sprintf$("PLANT DELIVERY DATE"),
sprintf$("CUSTOMER"),
sprintf$("NAME"),
sprintf$("QUANTITY"),
sprintf$("STOCK AVAILABLE"),
sprintf$("FINAL STOCK"))
line = strip$(shiftl$(line))
seq.puts(line, stat.fp)
endif
}

function write.data()
{
line = concat$("|",
sprintf$("%s",tdsls041.item),
sprintf$("%s",tiitm001.dsca),
sprintf$("%s",tdsls041.orno),
sprintf$("%s",tdsls041.pono),
sprintf$("%s",tdsls041.odat),
sprintf$("%s",tdsls041.ddta),
sprintf$("%s",tdsls041.cuno),
sprintf$("%s",tccom010.name),
sprintf$("%s",tdsls041.oqua),
sprintf$("%s",stock.avail),
sprintf$("%s",final.stock))
line = strip$(shiftl$(line))
seq.puts(line, stat.fp)
}

Hi,

What your program is doing is, with the report its also write the CSV file. But, there is no logic written for printing the totals. I have made some changes. You can try that out.

functions:
function generate.file()
{
domain tcloco v.user
domain tcbool bool.flag
long exitCode,ret
v.user = logname$
err.file = bse.tmp.dir$() &"\" & v.user & ".csv"
file.name = bse.tmp.dir$() &"/" & v.user & ".csv"

stat.fp = seq.open(err.file, "w+")
process.file()
seq.close(stat.fp)


if ( api.mode = 1 or job.process ) then
import("export.file.name",import.file.name)
ret = file.mv(file.name, import.file.name)
if ret = 0 then
message("Report Copied to path -"&import.file.name)
else
message("Error while copying file to path -"&import.file.name)
endif
else
local.file.name = "C:\temp\tfgldf480m00c."&str$(utc.num())
ret2 = server2client(file.name,local.file.name,false)
if ret2 <> 0 then
ret2 = create.local.directory("C:\temp")
if ret2 <> 0 then
message("Error while transfering report file to Client C:\temp Directory")
else
ret2 = server2client(file.name,local.file.name,false)
endif
endif
seq.unlink(file.name)
bool.flag = start.application.local("Excel "&local.file.name, FALSE, exitCode )
if bool.flag = false then
message("Error while transfering report file to Client C:\temp Directory")
endif
endif
}


function process.file()
{
generate.header()
read.main.table()
}


function read.main.table()
{
domain tcitem item.old,item.new
domain tcqiv1 tot.quan
long flag

date.f = date.to.num (year.f,perd.f,1)
if perd.t = 12 then
date.t = date.to.num (year.t,perd.t,31)
else
date.t = date.to.num (year.t,perd.t+1,1)
date.t = date.t - 1
endif

item.old = ""
item.new = ""
tot.quan = 0
flag = 0
select tdsls041.*,tdsls040.*
from tdsls041,tdsls040
where tdsls041._index1 inrange {:orno.f,:pono.f} and {:orno.t,:pono.t}
and tdsls041.cuno between :cuno.f and :cuno.t
and tdsls041.item between :item.f and :item.t
and tdsls041.ddta between :date.f and :date.t
and tdsls041._index1 refers to tdsls040
order by tdsls041.item
selectdo
if flag = 0 then
item.old = tdsls041.item
flag = 1
endif
item.new = tdsls041.item
select tiitm001.dsca,tiitm001.stoc,tiitm001.ordr,tiitm001.blck
from tiitm001
where tiitm001._index1 = {:tdsls041.item}
selectdo
selectempty
tiitm001.dsca = ""
endselect
select tccom010.name
from tccom010
where tccom010._index1 = {:tdsls041.cuno}
selectdo
selectempty
tccom010.name = ""
endselect



if item.new <> item.old then
item.old = item.new
write.totals()
endif

write.data()

tot.quan = tot.quan + tdsls041.oqua
rprt_send()
selectempty
endselect
}

function generate.header()
{
if stat.fp >0 then
line =concat$("|",
sprintf$("ITEM"),
sprintf$("ITEM DESCRIPTION"),
sprintf$("ORDER"),
sprintf$("POSITION"),
sprintf$("ORDER DATE"),
sprintf$("PLANT DELIVERY DATE"),
sprintf$("CUSTOMER"),
sprintf$("NAME"),
sprintf$("QUANTITY"),
sprintf$("STOCK AVAILABLE"),
sprintf$("FINAL STOCK"))
line = strip$(shiftl$(line))
seq.puts(line, stat.fp)
endif
}

function write.data()
{
line = concat$("|",
sprintf$("%s",tdsls041.item),
sprintf$("%s",tiitm001.dsca),
sprintf$("%s",tdsls041.orno),
sprintf$("%s",tdsls041.pono),
sprintf$("%s",tdsls041.odat),
sprintf$("%s",tdsls041.ddta),
sprintf$("%s",tdsls041.cuno),
sprintf$("%s",tccom010.name),
sprintf$("%s",tdsls041.oqua),
sprintf$("%s",stock.avail),
sprintf$("%s",final.stock))
line = strip$(shiftl$(line))
seq.puts(line, stat.fp)
}

function write.totals()
{
line = concat$("|",
"",
"",
"",
"",
"",
"",
"",
"",
str$(tot.quan),
"",
"")
line = strip$(shiftl$(line))
seq.puts(line, stat.fp)
}

Note that I haven't tested this. You can check.

thax bhushan ,,
for ur great effort .....its coming now

Glad that worked for you :)