Saving a report to PDF using report.saveas

[glenschild]

Hi

I have a fairly complex report which is based upon a stored procedure. It requires two inputs to run:

1. a numeric storecode
2. a date value

This runs just fine from a dialog component which sets the two variables into session.variables.

My problem is that I am trying to save the report to a pdf on the server and it just does not run! I managed to find potential solutions on the forum but no luck.

The relevant part of my xbasic script is:

dim reportLayoutName as c
reportLayoutName = "[EMAIL="[email protected]"][email protected][/EMAIL]"
dim vStorecode as N
vStorecode = convert_type(rs.data("cs_id"),"N")
dim vSalesWeek as t
vSalesWeek = session.salesweek
dim reportFileName as c
reportFileName = remspecial(dtoc(session.salesweek,"12-"))+"_WSM_"+rs.data("cgc")+"_"+remspecial(rs.data("store"))+".pdf"
dim reportSavePath as c
reportSavePath = "D:\Reports\\"+reportFileName
DIM args as SQL::arguments 
args.Set("salesweek",vSalesWeek)
args.Set("storecode",vStorecode)
dim options as p
options.filter = "@SALESWEEK = :salesweek, @STORECODE = :storecode"
report.saveas(reportLayoutName, "PDF", "" , "" , reportSavePath,.F.,.F.,args,options)

in debug as soon as the report.saveas line is executed I receive an error message (See attached image)

I have tried a variety of combinations on variable types without success. If anyone has any pointers, gratefully received.

Kind regards

Glen

[Peter.Greulich]

Re: Saving a report to PDF using report.saveas

Not sure, Glen. But according to the help file:

.filter = Adds to the WHERE clause in the underlying SQL expression

So maybe this:

options.filter = "@SALESWEEK = :salesweek, @STORECODE = :storecode"

Can't work?

[frankbicknell]

Re: Saving a report to PDF using report.saveas

i would try

options.filter = "@SALESWEEK = :salesweek and @STORECODE = :storecode"
or
options.filter = "SALESWEEK = :salesweek and STORECODE = :storecode"

is @ part of the name of your field?

[glenschild]

Re: Saving a report to PDF using report.saveas

Not sure, Glen. But according to the help file:



So maybe this:

options.filter = "@SALESWEEK = :salesweek, @STORECODE = :storecode"

Can't work?

Hmmm this is based on a stored procedure which does not use a where clause in the sql expression, this appears ominous.

[glenschild]

Re: Saving a report to PDF using report.saveas

i would try

options.filter = "@SALESWEEK = :salesweek and @STORECODE = :storecode"
or
options.filter = "SALESWEEK = :salesweek and STORECODE = :storecode"

is @ part of the name of your field?

Have already tried those combos without success. The @ is a part of the variable declaration in the declared stored procedure.

[frankbicknell]

Re: Saving a report to PDF using report.saveas

I have many report saveas methods working. I have never tried using a procedure. I can't even think of how I could use a procedure. Would a view work? I would get the saveas working i its simplest form then try it with the procedure.

[Peter.Greulich]

Re: Saving a report to PDF using report.saveas

That's the thing, Glen, I don't believe that options.filter is set up to process or read variable declarations in S.P.s.

[glenschild]

Re: Saving a report to PDF using report.saveas

Hi Frank

Using a procedure to do "on the fly" calculations which works really well. This particular report (which creates a summary of stock movement records) takes two input variables from the dialog. The first is a date. The procedure then returns calculations of movements over the seven day period from the input date including opening and closing stocks. These are grouped by a stock category AND then the second input variable filters for the particular stock location. The recordset produced by the procedure forms the basis of the report with linked reports used to show specific line data.

A view does not provide the flexibility needed to calculate opening and closing balances. It works well but there is a need to generate saved pdf's one for each location.

Peter that is my presumption, so I am going to somehow have to figure out an alternative solution to generate multiple pdf's

Glen

[christappan]

Re: Saving a report to PDF using report.saveas

Your stored procedure--depending on the server you're using, you presumably use something like this to call it:

CALL myStoredProcedure(:salesWeek, :storeCode)

Is that the SQL definition for the report? You should be able to set the report definition as that and then just set your arguments accordingly without using options.filter at all.