End user report app using Stored Proc

rbuser

Apologies, if this has come up before but I couldn't find anything related
in the newsgroups.
I need to create an end user reporting application in which the resultsets
are returned (mostly) by stored procedures which take a bunch of parameters
(complex reports involving lot of calculations etc). Can someone let me know
what is the easiest way to execute the stored procs and pass parameters to
them in an end user reporting application? I am using SQL Server 2000,
Delphi 5 EE (fully patched), and RB 6 Enterprise edtion. Upgrading the
version of Delphi right now is not an option but I can upgrade RB. I am
connecting using ADO.
Bascially what i want to do is just before the reports are generated show a
form which allows the user to input the values for the various parameter in
the stored proc. I have been able to display a form which collects the
values for the user (using RAP passthru function ) during GlobalOnCreate
event but do not know what to do after this. Any help gratefully
acknowledged.
I am planing to build with runtime packages.
Thanks in advance
Paras

digitalmetaphors

Hi Paras,

First, when you want to parameterize a query using ReportBuilder, you
usually create TppAutosearchFields on the report. Then you can show a dialog
that is created automatically based on those autosearch fields. You can
customize the autosearch dialog as shown in the autosearch demos located in
your RBuilder\Demo installation directory.

In order to use stored procs in an end user environment, you have to create
custom dataview templates that run these on the database. That way you can
surface a datapipeline, created in your custom datatview template object, to
the end user which has this dataset. The advantage is that the dataview
template is stored inside the report template so you don't have to manage
pipeline components on a form or datamodule in the end user environment.

To merge these together, you'll have to create autosearch fields in code and
manually update the dataview templates in code for the search parameters in
the Report.OnGetAutosearchValues event. You may be aware that you can
define autosearch criteria on regular dataviews, however, you won't be able
to do that since you will be creating a custom dataview template and
managing the parameters going into the stored proc. It will be easier to use
autosearch fields that you create on the report. One example of using
autosearch fields in an end user solution is when you manually edit the SQL
for a dataview for example:

http://www.digital-metaphors.com/tips/EditSQLAndSearch.zip

Cheers,

Jim Bennett
Digital Metaphors

rbuser