Parameterized query in report builder 9

rbuser

?Is it possible to write Parameterized query in report builder 9 .
Currently i m using version 7 and cannot pass parameters from delphi form
to report builder CALC. Is it possible to do so?
Thanks



--- posted by geoForum on http://delphi.newswhat.com

rbuser

Lucy, I am able to pass parameters in RB7 using pass through functions.

For example, I created two pass through functions called OSFirstTransNo and
OSLastTransNo which basically just pass integers from my App in Delphi.
Then I set the query parameter to autosearch for the fields I want to modify
at run time. In the BeforeAutoSearchDialogCreate event I put
Report.ShowAutoSearchDialog:=False; so that the auto search dialogue never
appears. In the OnGetAutoSearchValues event, I put the following code:

if (Report.AutoSearchFields[0].FieldName = 'TransNo') then
Report.AutoSearchFields[0].SearchExpression := IntToStr(OSFirstTransNo);
if (Report.AutoSearchFields[1].FieldName = 'TransNo') then
Report.AutoSearchFields[1].SearchExpression := IntToStr(OSLastTransNo);

The latter part of the SQL code being modifed looks like the following:
----------------------------
FROM Trans Trans
LEFT OUTER JOIN Customer Customer ON
(Customer.StoreNo = Trans.cStNo)
AND (Customer.CustNo = Trans.CustNo)
WHERE ( Trans.TransNo >= 1 )
AND ( Trans.TransNo <= 99 )
ORDER BY Trans.TransNo
----------------------------

Basically, the 1 and the 99 are being modified to the variables of my
choosing from Delphi, in this case, to use values from fields in another
table. It isn't really a parameterized query, but the result is the same.

One caveat, this does not work in the preview tab when designing the report.
The OnGetAutoSearchValues event does not fire then, but if I actually print
the report to the screen from outside the designer, it works. Hope this
helps.

David Miller.

p.s. Following is the unit I wrote for the pass through functions. The
unit VARS is a personal unit containing some global variables and functions.
LastTransNo and FirstTransNo are global variables set in another unit and
passed through to RB through this unit called RAPFunc. I will add other
pass through functions to this unit when needed. These two functions show
up under category "Custom" in the Calc Tab of ReportBuilder.

---------------------------------------
unit RAPFunc;

interface

uses
Forms, raFunc, ppRTTI, vars;

type
TCustomFunctions = class(TraSystemFunction)
public
class function category: string; override;
end;

TOSFirstTransNo = class(TCustomFunctions)
public
procedure ExecuteFunction(aParams: TraParamList); override;
class function GetSignature: string; override;
class function HasParams: boolean; override;
end;

TOSLastTransNo = class(TCustomFunctions)
public
procedure ExecuteFunction(aParams: TraParamList); override;
class function GetSignature: string; override;
class function HasParams: boolean; override;
end;

implementation

class function TCustomFunctions.Category: string;
begin
result:='Custom';
end;

{ OSFirstTransNo --- START ---}
procedure TOSFirstTransNo.ExecuteFunction(aParams: TraParamList);
begin
{FirstTransNo is Global Integer In Vars.Pas That Is Set To DB Field's
Value}
SetParamValue(0,FirstTransNo);
end;

class function TOSFirstTransNo.GetSignature: string;
begin
result:='function OSFirstTransNo: integer;';
end;

class function TOSFirstTransNo.HasParams: boolean;
begin
result:=false;
end;
{ OSFirstTransNo --- END ---}

{ OSLastTransNo --- START ---}
procedure TOSLastTransNo.ExecuteFunction(aParams: TraParamList);
begin
{LastTransNo is Global Integer In Vars.Pas That Is Set To DB Field's
Value}
SetParamValue(0,LastTransNo);
end;

class function TOSLastTransNo.GetSignature: string;
begin
result:='function OSLastTransNo: integer;';
end;

class function TOSLastTransNo.HasParams: boolean;
begin
result:=false;
end;
{ OSLastTransNo --- END ---}

initialization
raRegisterFunction('OSFirstTransNo',TOSFirstTransNo);
raRegisterFunction('OSLastTransNo',TOSLastTransNo);
end.

--------------------------------------------

rbuser

Thanks a lot for your help David. I tried this solution but i m not able
to use the functions OSFirstTransNo and OSLastTransNo. Can you please
make it more clear how to use parameterized query in RB 7 through the eg.
you have given.
Thanks for your help.



--- posted by geoForum on http://delphi.newswhat.com

nardmoseley

Can you explain further what you are trying to accomplish? That will help me
to suggest a solution.

RB 9 adds two new Report level events that facilitate the report
initialization process. The events are available to Delphi and to RAP (the
Calc workspace).

1. Report.OnIntializeParameters event.

This event fires whenever a report is generated via a call to the Print
method. This event occurs prior to the AutoSearch events. Use this event to

- Define Report.Parameters[] and Report.AutoSearchFields[]
- Set Parameter and AutoSearchField values.
- Display a custom dialog that enables a user to enter parameter values.
This is useful when not using the built-in AutoSearch feature.
- Generate custom SQL that adds autosearch criteria.

Set the Cancel parameter to True to cancel the report generation process.

2. Report.BeforeOpenDataPipelines event

This event fires prior to the report engine opening the datapipelines
associated with the report, child reports, and data-aware controls. Use this
event to

- apply custom parameter values to SQL
- generate custom SQL



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

rbuser

Hi Lucy. The OSFirstTransNo and OSLastTransNo functions are simply my own
custom functions. You would do your own functions with different names.
Look in the Report Builder Developer's Guide under the section "Extending
RAP" such as "RAP Pass-Through Functions" and "Adding Functions to the Code
Toolbox" (page 209 in my manual). Without you telling me what kind of error
you are getting, I have no idea what you are doing wrong.

David Miller

rbuser

hello,
I have created my own custom functions 'funcLocation' and 'funcParno'.
But when i m assigning them to autosearch search expression it is giving
error
'Incompatible types :string and Class reference'in frmLocationwisereport.
Please help me to find the problem in my code and how to use it.
Thanks

My code for RAP unit is

unit RAPunit;
interface
uses
Forms,raFunc,ppRTTI;
type
TfrmRAPLocationFunctions = class(TraSystemFunction)
public
class function category: string; override;
end;

TfuncLocation = class(TfrmRAPLocationFunctions)
public
procedure ExecuteFunction(aParams: TraParamList); override;
class function GetSignature: string; override;
class function HasParams: boolean; override;
end;

TfuncParno = class(TfrmRAPLocationFunctions)
public
procedure ExecuteFunction(aParams: TraParamList); override;
class function GetSignature: string; override;
class function HasParams: boolean; override;
end;

var
frmRAPLocationFunctions:TfrmRAPLocationFunctions;

implementation

uses
vars;

class function TfrmRAPLocationFunctions.Category: string;
begin
result:='Location';
end;

{ funcLocation --- START ---}
procedure TfuncLocation.ExecuteFunction(aParams: TraParamList);
var
lsResult:string;
begin
{locname is Global string In Vars.Pas That Is Set To DB Field's
Value}
lsResult:=Application.ExeName;
SetParamValue(0,locname);
end;

class function TfuncLocation.GetSignature: string;
begin
result:='function funcLocation: string;';
end;

class function TfuncLocation.HasParams: boolean;
begin
result:=false;
end;
{ funclocation --- END ---}

{ funcParno --- START ---}
procedure TfuncParno.ExecuteFunction(aParams: TraParamList);
begin
{parano is Global Integer In Vars.Pas That Is Set To DB Field's
Value}
SetParamValue(0,parano);
end;

class function TfuncParno.GetSignature: string;
begin
result:='function funcParno: integer;';
end;

class function TfuncParno.HasParams: boolean;
begin
result:=false;
end;
{ funcParno --- END ---}

initialization
raRegisterFunction('funcLocation',TfuncLocation);
raRegisterFunction('funcParno',TfuncParno);
end.



Code of VARS.PAS is
unit Vars;

interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls,
Forms,
Dialogs, ppCtrls, ppPrnabl, ppClass, ppDB, ppBands, ppCache, DB,
DBTables, StdCtrls, ppEndUsr, ppProd, ppReport, ppComm, ppRelatv,
ppDBPipe, ppDBBDE;

type
TfrmVars = class(TForm)
Button1: TButton;
private
{ Private declarations }
public
{ Public declarations }

end;

var
locname:string;
parano:string;
frmVars: TfrmVars;

implementation

{$R *.dfm}


end.


Code for frmLocationwisereport where i am using report component and need
to pass the parameters from query .

procedure TfrmLocation.FormCreate(Sender: TObject);
begin
locname:=ppdbtxtLname.Text;
parano:=ppDBText1.Text;
qurLocation.Open;
while not qurLocation.Eof do
begin
cmbxLocaName.Items.Add(qurLocation.Fields[1].AsString);
qurLocation.Next;
end;
cmbxLocaName.Text := cmbxLocaName.Items[0];
end;

procedure TfrmLocation.cmbxLocaNameChange(Sender: TObject);
begin
qurLocation.SQL.Clear;
qurLocation.SQL.Add('select location_initial from metering_location
where location_name = ' + '''' +trim(cmbxLocaName.text) + '''');
qurLocation.Open;
edtInitial.Text :=qurLocation.fields[0].AsString;
qurLocation.Close;
end;

procedure TfrmLocation.ppReport1BeforeAutoSearchDialogCreate(
Sender: TObject);
begin
ppReport1.ShowAutoSearchDialog:=True;
end;

procedure TfrmLocation.ppReport1GetAutoSearchValues(Sender: TObject);
begin
if(ppReport1.AutoSearchFields[0].FieldName='location_name') then
ppReport1.AutoSearchFields[0].SearchExpression:=TfuncLocation;
if(ppReport1.AutoSearchFields[1].FieldName='parno') then
ppReport1.AutoSearchFields[1].SearchExpression:=IntToStr(tfuncParno);
end;

end.



--- posted by geoForum on http://delphi.newswhat.com

nardmoseley

The AutoSearchField.SearchExpression property is a string that represents
the value to search upon. You can only assign a string value to this
property.

TfuncLocation refers to a class that you have defined.


I think your intent is to call a RAP function that returns a string value.

SearchExpression := funcLocation.




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

rbuser

I'm not taking time to look through all your code, but the error indicates
that you are referencing the class rather than the function.

Instead of:
------------------------------
procedure TfrmLocation.ppReport1GetAutoSearchValues(Sender: TObject);
begin
if(ppReport1.AutoSearchFields[0].FieldName='location_name') then
ppReport1.AutoSearchFields[0].SearchExpression:=TfuncLocation;
if(ppReport1.AutoSearchFields[1].FieldName='parno') then
ppReport1.AutoSearchFields[1].SearchExpression:=IntToStr(tfuncParno);
end;
------------------------------

Change to the following:

------------------------------
procedure TfrmLocation.ppReport1GetAutoSearchValues(Sender: TObject);
begin
if(ppReport1.AutoSearchFields[0].FieldName='location_name') then
ppReport1.AutoSearchFields[0].SearchExpression:=funcLocation;
if(ppReport1.AutoSearchFields[1].FieldName='parno') then
ppReport1.AutoSearchFields[1].SearchExpression:=IntToStr(funcParno);
end;
------------------------------

In other words, assign funcLocation and funcParno to SearchExpression
instead of TfuncLocation and TfuncParno.

David Miller.

rbuser

hello
I am really thankful for your help but i am still not able to use the
pass through functions correctly. I tried with
searchexpression:=funclocation
but it is not working.Also the new functions are not appearing in the
calc tab language.If it is possible can anyone provide me complete
example as how to implement pass through functions.
thanks



--- posted by geoForum on http://delphi.newswhat.com

nardmoseley

--------------------------------------------------
Article: Extending RAP
---------------------------------------------------

There are two very simple and powerful techniques to extend the capabilities
of RAP infinitely. These are summarized below and covered in more detail in
the RAP.hlp online help. Demos and tutorials are installed to
RBuilder\Demos\RAP. The tutorial text is located in RAP.hlp.


1. RAP Pass-Through Functions

These are functions that appear in the Language tab of RAP's Code Toolbox.
These functions are written in Delphi and can be called from RAP. RAP's
pass-through function architecture enable's developers to add new built-in
functions to RAP's code toolbox.

2. Extend RAP's RTTI

RAP's Run-time Type information defines what classes and properties can be
accessed via RAP. By default the published properties of any class that is
registered with Delphi's RegisterClass procedure is recognized by RAP. In
addition many of the public properties and methods of ReportBuilder classes
are exposed.







--
Tech Support mailto:support@digital-metaphors.com
Digital Metaphors http://www.digital-metaphors.com

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

rbuser

I already gave you a complete example of implementing a pass through
function. What I posted to you is the complete unit that works in my
application. To create it, I simply followed the Tutorial that I referenced
for you before in the RB Developer's Guide.

Without telling us the error message you are seeing, it is difficult to know
what the problem is. I really don't have time to scrutinize over each line
of your code when an error message would tell us exactly what the problem
is. It looks like you should have a new category in your language tab
called "Location" with your functions located there. However, there are
some strange aspects of your codes, such as the lsResult variable in
TfuncLocation.ExecuteFunction which doesn't seem to do anything.

David Miller