How to pass Sql Query in RAP-7

rbuser

Dear Nard Moseley,

I am using RB-7. I want to pass a sql query using RAP.How it is
possible.Give me an Example?

One another question is that actually i want to calculate opening
balances of a particular table
(Fields are: Group_code,Account_no,Trans_type,Amount,from_date,to_date).
I had created a auto search dialog for this and passing (from_date) and
(to_date).I want to calculate total amount as an opening balance of a
particular account_no of a particular group_code before from_date. For
this i had created two datapipelines. One for applying search condition
and another for without search condition. But still am unable to
calculate opening balance(total_amount) before from_date.What i will do?
How it is possible in RB-7 ? What i will do code in RAP?

Regads
Kuku



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

nicocizik

Hi Kuku,

What are you trying to ultimately accomplish? Using RAP with RB 7, there is
not an available event to successfully change the SQL before the report
generates. In RB 9.0x, there are a couple new events added just for this
purpose. We also added a new SQLBuilder object that allows you to access
and alter the DataView SQL code very easily without having to understand and
use the SQL object in ReportBuilder.

If you need to add a search conditon as well as calculate a total on all
your data, creating two datasets is probably your best option. I would
recommend creating a simple dataset that contains a single calculated field
containing the total opening balance, then adding that single value to your
report where you need it. Since you do not need to traverse this data, you
should not need to use a subreport. Then use the second dataset to search
on a certain condition and display the rest of the report. You should not
need any RAP code to accomplish this task.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hi Nico

I think what i waanted to say was not clear

This is what i want to achieve
Opening Balance Periodic Banlnce
Cr Dr Cr Dr
Expense [This is the Group Code]
Vechile Expense 0 100 0 50
Petrol Expense 100 0 150 0

and so on Petrol Expense is a Account Code which falls under the Group
Code.

Now for the Period 01/09/05 to 07/09/05 i want this to come.

My opening balance is calculated as sum of all Accountcode before
01/09/2005 under its respective groupcode

I have the format and the data for the Period coming but the opening
balance is not coming.

I have taken Two data pipeline(fadopn and fad, data is on same table
called account) as suggested by you.

Got the autosearch value dates but i am doing something worng which i do
not know.

Plz suggest what i have to do.

TIA
KUKU

===================================

This is the Rap code what i have written:

{Global Declaration}
Var
frdate: datetime;
todate: datetime;
vfrdate: string;
vtodate: string;

{assinging the value of variabl is zero at group break}
Procedure Group2AfterGroupBreak;
begin
variable1.value :=0; {Periodic debit}
variable2.value :=0; {Periodic credit}
variable3.value :=0; {Opening credit}
variable4.value :=0; {opening debit}

end;

{Retriving the parameter through Auto seach dialog}
procedur ReportOnGetAutoSearchValue;
begin

Vfrdate :=Report.AutosearchFields[0].SearchExpression;
ShowMessage(Vfrdate);


end;
{For calculating Periodic Balance for Credit}
Procedure Variable1OnCalc(var Value: Variant);
begin
if( Fad['Fad Faddrcrflg']='C') then
Value := value + Fad['Fad Fadamount'];
end;


{For calculating Periodic Balance for Debit}
Procedure Variable2OnCalc(var Value: Variant);
begin
if( Fad['Fad Faddrcrflg']='C') then
Value := value + Fad['Fad Fadamount'];
end;

{For calculating Opening Balance for Crdit}

Procedure Variable3OnCalc(var Value: Variant);
begin
frdate := StrToDate(vfrdate);
if(fadopn['Fah Fahtrandate'] < vfrdate)then
begin
{ShowMessage(fadopn['Fah Fahtrandate']);
}
Value := value + fadopn['Fad Fadamount'];
end;
end;


there is
this
access
and
field
your
you
search
not
Group_code,Account_no,Trans_type,Amount,from_date,to_date).
do?



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

nicocizik

>Got the autosearch value dates but i am doing something worng which i do

What is going wrong? My first suggestion would be to get this to work
strictly using Delphi, then try to move it to RAP. This way you will see if
the appropriate events and code are available to accomplish this in RAP
without pass-thru function.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com