Parameter fields and web reports.

[Ruairi]

I have some fairly complex reports that i am running from a VB 6 application (reporting from a SQL 2000 DB). The client wants to add the ability to view these reports from their web server.
The problem is that in my vb app i am generating a sql statement based on the parameters chosen on the vb forms and setting the .SQLQuereyString property of the report to that sql statement. With the web reports you don't have that kind of control over the report objects. You can feed it paramaters for a report like this:
-------------------------------------------------------------------------
<form method=&quot;POST&quot; action=&quot;

http://intranet.acompany.com/testreport.rpt&quot;>

<p>From: <input type=&quot;text&quot; name=&quot;prompt0&quot;
<p> To: <input type=&quot;text&quot; name=&quot;prompt1&quot;</p>
<p> Exclude Product Group: <input type=&quot;text&quot; name=&quot;prompt2&quot;</p>
<p> Production Center: <input type=&quot;text&quot; name=&quot;prompt3&quot;</p>
<p> Include C1: <input type=&quot;text&quot; name=&quot;prompt4&quot;</p>
<p> Include C2: <input type=&quot;text&quot; name=&quot;prompt5&quot;</p>
<p> Include C3: <input type=&quot;text&quot; name=&quot;prompt6&quot;</p>
<p> Include C4: <input type=&quot;text&quot; name=&quot;prompt7&quot;</p>
<p> Summary Only: <input type=&quot;text&quot; name=&quot;prompt8&quot;</p>
<p> Include Misc. Invoice: <input type=&quot;text&quot; name=&quot;prompt9&quot;</p>
<p><input type=&quot;submit&quot; value=&quot;View Report&quot;></p>

which places some values from text boxes on the webpage into parameter fields in the report (i wasn't using parameters at all in VB but took the sql statement being generated by VB, removed the where clause, put it into the report, and added a record selection formula using new paramater fields). The report printed correctly after 15 minutes. Obviously that is not an acceptable amount of time to wait. I can't schedule the reports with preset parameters. My next thought was to get rid of the record selection formula and use the parameter fields directly in the sql statement. I changed the date parameters to strings and assigned them these values (including the single quotes):
{?SDate} = '06/12/2001'
{?EDate} = '06/12/2001'

And inserted them into the report's SQL Querey like this
WHERE
Production.&quot;date&quot; >= {?SDate} And Production.&quot;date&quot; <= {?EDate}

This gives me the following errors:
&quot;ODBC Error:[Microsoft][ODBC SQL Server Driver]syntax error or access violation&quot;
and the standard:
&quot;Error Reported by database DLL&quot;

has anyone used parameter fields in the sql querey?
Any Ideas?
TIA
Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com

 

[Glouby]

I use stored procedures for everything.

Create a stored procedure with parameters, and use that as your data source (when you're connecting to the data source choose OPTIONS and make sure that the &quot;stored procedures&quot; box is checked or you won't be able to see them).

The stored procedure parameters bubble up through the report and become parameters of the report. Maybe this will give you all of the flexibility of SQL, and a data source that can be used for everything.