Save Report

[Imaac]

Hi

I have been trying to get this to work all day now and have now gave up and am turning to the trusted message board for assistance. :)

I am trying to save a report to a local drive on my machine, the report runs fine if I use "open a report" action javascript but when trying to save a report using the same report with the following code nothing happens.



Dim filename as c = "Additionals_"+session.report_no+".pdf"
Dim filepath as c = "G:\Additionals"+chr(92)+filename

dim args as sql::Arguments
dim Options as p
dim printOptions as p
args.set("blank", "")
options.propogateErrors = .t.
printOptions.blank = ""
'Dim reportname as c = a5_removetrailingbackslash(request.applicationRoot) +"\additionals_auth.a5rpt"
Dim reportname as c = "[email protected]"
Dim saveAs as c = "PDF"
Dim filter as c = "REPORT_NO = "+val(session.report_no)
Dim order as c = ""

fn = a5w_report_saveAs(reportName,saveAs,filter,order,filepath,global_variables(),printOptions,args,options)


Could anyone perhaps advise me where I am going wrong

Your help would be appreciated.

Thank you

Peter

[Pat Bremkamp]

Re: Save Report

Couple ideas. Try
Dim filename as c = "Additionals_"+session.report_no
that is, without the .pdf.

Also, after you print it, you can copy it anywhere you want
sleep(3) 'give it time to print
new_path_and_name = "g:\Additionals"+chr(92)+filename+".pdf"
file.copy(fn,new_path_and_name)

[Imaac]

Re: Save Report

Hi Pat

Thanks for that.

At least I am now getting somewhere.

I am now getting the error:

Invalid report filter expression: 'additionals_header.report_no = adir-49268'.

With this code:

Dim filename as c = "Additionals_"+session.report_no
Dim filepath as c = "G:\Additionals"+chr(92)

dim args as sql::Arguments
dim Options as p
dim printOptions as p
args.set("Blank", "")
options.propogateErrors = .t.
printOptions.blank = ""
Dim reportname as c = "additionals_auth@[PathAlias.ADB_Path]\pcss.alb"
Dim saveAs as c = "PDF"
Dim filter as c = "additionals_header.report_no = "+session.report_no
Dim order as c = ""

fn = a5w_report_saveAs(reportName,saveAs,filter,order,filepath,global_variables(),printOptions,args,options)

I am not sure where I am going wrong with the filter.

[Pat Bremkamp]

Re: Save Report

common problem...try
Dim filter as c = "additionals_header.report_no = \""+session.report_no+"\""

Without the quotes, Alpha sees adir-49268 as a variable, not a value. For sql backend you may need ' instead of "

[Imaac]

Re: Save Report

Hi

Sorry for my delay in replying but have been busy with other urgent things and never got round to fixing this problem.

I have at the moment managed to get my report to save to the harddrive but all my reports are showing 'Report does not contain any records' I suspect it is my filter.

My filter is currently:

Dim report_number as c = session.report_no
Dim filter as c = "report_no = \""+report_number+"\""

I am unsure where I am going wrong now.

Any help would be greatly appreciated.

[[email protected]]

Re: Save Report

Hi,

Suppose your sql definition is domething like: select * from additionals_header

What does
select * from additionals_header
where additionals_header.report_no = session.report_no

directly submitted in an sql query tool give as result?

session.report_no is that numerical or not?

You better use an argument
either
set.args("whatreport_no",session.report_no) or set.args("whatreport_no",convert_type(session.report_no,"N"))

filter = "additionals_header.report_no = :whatsession.report_no "


compare to my code you have no
options.filter = Filter



Creating an report an directly downloading I have done some cutting and pasting in my code but maybe you can see where yours goes wrong:
This works for me:
function Create_xls as c (e as p)
'create a sql arguments object
dim args as sql::arguments
args.set("whatcentra_kode",e.datasubmitted.veldcentra_kode)
args.set("whatpeildatum",convert_type(e.datasubmitted.veld_peildatum,"D"))
args.set("whatuser",a5ws_getcurrentuser())

'We open a connection and get the single fields
Dim cn as sql::connection
dim flag as l
flag = a5SQLConnectionOpen(cn,"::name::dataPieterOntwikkeling", session)
if flag = .f. then
dim msg as c
msg = "Could not connect to database. Error reported was: " + cn.CallResult.text
'we need to encode this for javascript since the message will be sent back to the browser for display
'js_escape() encodes ' as \' and crlf as \n
msg = js_escape(msg)
dim jscmd as c
jscmd = "alert('" + msg + "');"
Create_xls = jscmd
exit function
end if
'The base sqlselect is based on the selected report
' ___where___ is the variabele where part
' ___group_by___ is the variable group by part
dim strrapport as c = upper(e.datasubmitted.veldrapport)
dim strgroupby as c 'replaces ___groupby___
dim strwhere as c 'replaces ___where___
dim strselect as c 'replaces ___select___
dim strselectunion as c 'replaces ___selectunion___
dim strsql as c 'sql defnitions for report
dim strFilenameuser as c
strFilenameuser = "to_be_determined.xls"
dim strReportname as c
strReportname = "to_be_determined"
dim filter as c
filter = ""
dim order as c
Order = ""
dim sqlFilter as c = default ""
dim options as p
options.filter = sqlFilter
options.order = ""
dim printOptions as p
printoptions.concatenate = .f.
PrintOptions.HasWatermark = .f.
PrintOptions.MultilingualSupport= .t.
PrintOptions.LinearizeForWeb = .t.
PrintOptions.Colors2GrayScale = .f.
PrintOptions.ConvertHyperlinks = .f.
PrintOptions.WatermarkType = "PDF"
PrintOptions.WatermarkText = "D R A F T"
PrintOptions.WatermarkFontName = "Times New Roman"
PrintOptions.WatermarkFontSize = 172
PrintOptions.WatermarkRotation = 450
PrintOptions.WatermarkColorHex = upper("e8fed2")
PrintOptions.WatermarkHorizPos = 120
PrintOptions.WatermarkVertPos = 120
PrintOptions.WatermarkOnTop = .F.
PrintOptions.WatermarkPDF = ""
PrintOptions.Encrypt = .F.
PrintOptions.OwnerPassword = ""
PrintOptions.UserPassword = ""
PrintOptions.CanPrint = .T.
PrintOptions.CanModifyDocument = .T.
PrintOptions.CanCopy = .T.
PrintOptions.CanAddNotes = .T.
PrintOptions.Use128BitKey = .T.
PrintOptions.EmbedFonts = .T.
PrintOptions.JpegQuality = "Low"


strFilenameuser = "Ruimten_leeg_"+e.datasubmitted.veld_peildatum+".pdf"
strReportname = "ruimten_leeg"

if e.datasubmitted.veldcentra_kode > "" then
sqlfilter = " ruimten.centra_kode = :whatcentra_kode "
end if

options.filter = sqlFilter

dim strFilename as c = Request.GetRequestTempFileName("pdf")

a5w_report_saveAs(strReportname+"@[PathAlias.ADB_Path]\CptPortal.alb","pdf",filter,order,strfilename,global_variables(),printOptions,args,options)

if file.exists(strfilename)
Session.SaveDataAsFile(strFilenameuser,file.to_blob(strfilename))
jscmd = "window.open('"+Session.FormatFileDataURL(strFilenameuser)+"');"
end if
'jscmd = jscmd+"alert('<a5:r>Gereed</a5:r>.');"
Create_xls= jscmd
exit function

[Imaac]

Re: Save Report

Hi All

Just an update on this.

I eventually got back to working on this and the code seemed fine.

How I got this to work was to change the join type to inner on the SQL genie builder and now the reports are building fine.

Thanks to everyone for their input.