Passing Parameters to view/stored procs

rbuser

Is it possible to pass parameters to views/stored procs somehow? I can't do
what I want with autosearch fields... which may seem strange, but here's the
situation:

I have a query that selects some data, including a field called
"ActivityDate". This activity date is stored in System Time (i.e. the time
on the server). However, reports can be generated by multiple users in
multiple timezones. The client (that runs the reports) detects the timezone
settings of the user and passes this to the server, which generates the
reports and makes sure to adjust the "ActivityDate" so that it displays in
user time... For example, if the server is in GMT + 1:00 and a user
(running the client in GMT + 3:00) runs a report that includes a record for
an event that occured at 4pm (system time), the data in the user's report
should show 6pm.

Additionally, the user should be able to autosearch by this "ActivityDate",
so I can't do any fancy stuff with variables in the report design, it has to
be handled in the SQL itself.

In any case, so I have a View on my SQL Server which returns the data how I
want it... all it needs is a parameter (the user's timezoneoffset). How can
I pass this parameter?

Best Regards,

James Crosswell
Software Engineer
Microforge.net Limited
http://www.microforge.net

nardmoseley

The RB Query tools do not support parameterized views or stored procs.

Two possible options:

1. Create a custom dataview template. See RBuilder\Demos\EndUser\Custom
DataViews

Create a custom dataview class that manages the stored procedure calls.

2. Use the existing dataview class and implement some event-handler code
that manually edits the SQL for the DataView. First work on implementing a
solution using Delphi event-handlers and then re-implement it in RAP code
using pass-through functions.

Use the RB Query Tools to define a query that contains the AutoSearch
parameter to be entered by the user. Use the
Report.BeforeAutoSearchDialogCreate event to free the existing autosearch
fields and add a new one:

ppReport1.AutoSearchFields[0].Free;
ppReport1.CreateAutoSearchField('Orders', 'CustNo', 'CustNo', dtString,
soEqual, '1221', True);

Then use the Report.OnGetAutoSearchFieldValues event to manually edit the
SQL. To do this you need to extract the TdaSQL object from the dataview.

Example:

procedure TForm1.ppReport1GetAutoSearchValues(Sender: TObject);
var
liLine: Integer;
liIndex: Integer;
lAutoSearchField: TppAutoSearchField;
lsLine: String;
lbWhereInserted: Boolean;
liCurrentLine: Integer;
lSQL: TdaSQL;
begin

if GetSQLObject(ppReport1, lSQL) then
begin
lSQL.EditSQLAsText := True;

liLine := 0;

{delete any previous WHERE and ORDER BY clauses in the generated SQL}
for liIndex := 0 to lSQL.SQLText.Count - 1 do
begin

if (Pos('WHERE', lSQL.SQLText[liIndex]) <> 0) then
liLine := liIndex;

end;

if (Pos('WHERE', lSQL.SQLText[liLine]) <> 0) then
for liIndex := liLine to lSQL.SQLText.Count - 1 do
begin
lSQL.SQLText.Delete(liLine);
end;


{add new WHERE clause based on autosearch field values}
lbWhereInserted := False;
liCurrentLine := 12;

for liIndex := 0 to ppReport1.AutoSearchFieldCount - 1 do
begin
{get autosearch field}
lAutoSearchField := ppReport1.AutoSearchFields[liIndex];

{if valid entry, get search expression in SQL format}
if lAutoSearchField.Valid then
begin
lsLine := lAutoSearchField.SQLString;

{if auto search field already added, use AND operator}
if lbWhereInserted then
lsLine := 'AND ' + lsLine;

if not(lbWhereInserted) then
begin
lsLine := 'WHERE ' + lsLine;

lbWhereInserted := True;
end;

lSQL.SQLText.Insert(liCurrentLine, lsLine);

Inc(liCurrentLine);
end;

end;

{force the dataview to referesh the data}
TdaDataview(ppReport1.DataPipeline.DataView).OutOfSync;

end;

end;

function TForm1.GetSQLObject(aReport: TppReport; var aSQL: TdaSQL): Boolean;
var
liIndex: Integer;
lDataModule: TdaDataModule;
lDataView: TdaDataView;
begin

aSQL := nil;

{get the datamodule}
lDataModule := daGetDataModule(aReport);

if (lDataModule <> nil) then
begin
liIndex := 0;

while (liIndex < lDatamodule.DataViewCount) and (aSQL = nil) do
begin

lDataView := lDataModule.DataViews[liIndex];

if (lDataView <> nil) and (lDataView is TdaQueryDataView) and
(lDataview = aReport.DataPipeline.DataView) then
aSQL := TdaQueryDataView(lDataView).SQL;

Inc(liIndex);

end;

end;

Result := (aSQL <> nil);

end;





--
Nard Moseley
Digital Metaphors Corporation
http://www.digital-metaphors.com

rbuser

"Nard Moseley (Digital Metaphors)" wrote in

nardmoseley

Currently ReportBuilder supports the following types of parameters:

1. Report.Parameters[].

The Report.Parameters[] feature was added to RB 7.02. ReportParameters are
totally generic. There is no ask at runtime dialog displayed for them, nor
are they bound to any data. Report.Parameters can optionally be defined at
Delphi design-time and saved as part of the report definition.

2. Report.AutoSearchFields[]

The Report.AutoSearchFields[] are parameters that result in an ask at
runtime dialog being generated. By default the parameter values entered by
the user are not used by ReportBuilder. However, they can optionally be
associated with a DADE SQL DataView .

3. DADE AutoSearch criteria. These are SQL search criteria that are bound to
Report.AutoSearchFields.


Perhaps what you are asking for is that DataView parameters be allowed for
other purposes than search criteria. This brings the question of how will
they be used? Currently the TdaQueryDataView class applies the AutoSearch
values to the autosearch criteria. Are you looking for something similar to
the ':ParameterName' replacement that Delphi's TQuery object offers? This is
somthing that would be a cool feature for a future release.




--
Nard Moseley
Digital Metaphors Corporation
http://www.digital-metaphors.com

rbuser

rbuser

I did the following thing to run Stored Procs and pass parameters to it in a
end user Report Builder report:

1) I created a RAP function called ExecStoredProc(Param1, Param2, Param3);
// RAP doesn't have open parameters' facilities

2) I created a result table that contains the fields I need in the report
and columns corresponding the parameters I like to ask for.

3) In the AfterSearchDialog event, I call the ExecStoredProc funtions
passing the parameters, if necessary. It creates the result table I need to
show in my report.

I don't know if it's exctly what you want, so...

[],

Mauro Assis
Athena