Creating SQL on the fly in RAP

rbuser

Greetings All -

Once again, looking for some RAP help...

I'm creating a query (and right now it works fine so there is no query
issue)...

Here is a Query:

select substring([account number],5,3),
sum(CASE WHEN [account number] like '4%' THEN ifnull([credit amount],0)
- ifnull([debit amount],0) ELSE 0 END) as Sales,
sum(CASE WHEN [account number] like '5%' THEN ifnull([debit amount],0) -
ifnull([credit amount],0) ELSE 0 END) as Cost,
sum(CASE WHEN [account number] like '4%' and [date] between '4/1/2007'
and '4/30/2007' THEN ifnull([credit amount],0) - ifnull([debit
amount],0) ELSE 0 END) as LastSales,
sum(CASE WHEN [account number] like '5%' and [date] between '4/1/2007'
and '4/30/2007' THEN ifnull([debit amount],0) - ifnull([credit
amount],0) ELSE 0 END) as LastCost
from genledg
where ([account number] like '423%' or [account number] like '523%')
and [date] between '4/1/2008' and '4/30/2007'
group by substring([account number],5,3)

This query works fine...

But here is what I want to do...

The date range is an autosearch, and I want to be able to take that,
take a year off of it, and put that as the date range in the 2 CASE
portions of the select statement...

Is that possible?

I'm assumming that at some point the sql code has to be recreated
manually in code, editing the 2nd set of case statements to change the
dates manually...

Any help/advice would be appreciated!

Thanks in advance...

- Chris Kiraly

nardmoseley

Here is an example that shows how to manually edit the sql text and
implement autosearch.

www.digital-metaphors.com/tips/EditSQLAndAutoSearch.zip

--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com

rbuser

Nard Moseley (Digital Metaphors) wrote:
Well, I tried that and nothing seemed to happen...

Here is what I have:
The report itself is just 5 fields linked to GENLEDG, which is the
single query piepline. Fields are Acct, Sales, Cost, LastSales, LastCost.

The Query, created with the Query Designer, is as follows (didn't use
the SQL field, but created it with the fields and calcs in the designer.
However, the query itself looks like this:

SELECT
GENLEDG."Account Number" AS Account_Number,
sum(CASE WHEN [account number] like '4%' THEN ifnull([credit
amount],0) - ifnull([debit amount],0) ELSE 0 END) AS
sum_CASE_WHEN_account_num,
sum(CASE WHEN [account number] like '5%' THEN ifnull([debit
amount],0) - ifnull([credit amount],0) ELSE 0 END) AS
sum_CASE_WHEN_account_n_2,
sum(CASE WHEN [account number] like '4%' and [date] between
'4/1/2007' and '4/30/2007' THEN ifnull([credit amount],0) -
ifnull([debit amount],0) ELSE 0 END) AS sum_CASE_WHEN_account_n_3,
sum(CASE WHEN [account number] like '5%' and [date] between
'4/1/2007' and '4/30/2007' THEN ifnull([debit amount],0) -
ifnull([credit amount],0) ELSE 0 END) AS sum_CASE_WHEN_account_n_4
FROM GENLEDG GENLEDG
WHERE (( GENLEDG."Account Number" LIKE '423%' )
OR ( GENLEDG."Account Number" LIKE '523%' )
)
GROUP BY GENLEDG."Account Number"

There is 1 autosearch field on the date, asking for a between range.

I made the following changes in the CALC section of RAP:
[code start]
Global Variables:
var
sdt1,
edt1,
sdt2,
edt2: string;

procedure ReportBeforeOpenDataPipelines;
begin
Report.ShowAutoSearchDialog := True;
end;

procedure ReportOnGetAutoSearchValues;
var
lSQLBuilder: TdaSQLBuilder;
s: string;
dategrab: TppAutoSearchField;
begin
lSQLBuilder := TdaSQLBuilder.Create(Report.DataPipeline);

s := 'select substring([Account Number],5,3) as "Acct" from genledg';

if not Report.AutoSearchFields[0].ShowAllValues then
begin
dategrab := Report.AutoSearchCriteriaByName('GENLEDG', 'Date');
sdt1 := datetostr(dategrab.values[0]);
edt1 := datetostr(dategrab.values[1]);
sdt2 := copy(sdt1,1,6) + inttostr(strtoint(copy(sdt1,7,4))-1);
edt2 := copy(edt1,1,6) + inttostr(strtoint(copy(sdt1,7,4))-1);
s := 'select substring([Account Number],5,3) as "Acct",';
s := s + 'sum(CASE WHEN [account number] like ''4%'' and [date]
between '+ chr(39) + sdt1 + chr(39) + ' and '+ chr(39) + edt1 + chr(39)
+ ' THEN ifnull([credit amount],0) - ifnull([debit amount],0) ELSE 0
END) as Sales,';
s := s + 'sum(CASE WHEN [account number] like ''5%'' and [date]
between '+ chr(39) + sdt1 + chr(39) + ' and '+ chr(39) + edt1 + chr(39)
+ ' THEN ifnull([debit amount],0) - ifnull([credit amount],0) ELSE 0
END) as Cost,';
s := s + 'sum(CASE WHEN [account number] like ''4%'' and [date]
between '+ chr(39) + sdt2 + chr(39) + ' and '+ chr(39) + edt2 + chr(39)
+ ' THEN ifnull([credit amount],0) - ifnull([debit amount],0) ELSE 0
END) as LastSales,';
s := s + 'sum(CASE WHEN [account number] like ''5%'' and [date]
between '+ chr(39) + sdt2 + chr(39) + ' and '+ chr(39) + edt2 + chr(39)
+ ' THEN ifnull([debit amount],0) - ifnull([credit amount],0) ELSE 0
END) as LastCost,';
s := s + 'from genledg';
s := s + 'where ([account number] like ''423%'' or [account
number] like ''523%'')';
s := s + 'group by substring([Account Number],5,3)';
end;

lSQLBuilder.SQL.SQLText.Text := s;

lSQLBuilder.Free;
end;

[code end]

Now, I couldn't make the change in the OnInitializeParameters section,
because when I tried to enter what was in the sample I was given, RAP
kept giving me "( or [ expected". What I had in there was:

procedure ReportOnInitializeParameters(var aCancel: Boolean);
begin
if (Report.AutoSearchFieldCount = 0) then
Report.CreateAutoSearchField('GENLEDG', 'Date', 'Between',
sdt2+','edt1, True);

end;

but since it kept erroring, I simply deleted it.

When I ran the report - it only gave me figures from the auto criteria
range, and since the sql manually created in the OnGetAutoSearchValues
section changes the look of account number, I can tell that when I run
the report that its not doing anything in there, and its running the
original query.

How do I fix this?

Again, TIA...

Chris

nardmoseley

If you are using the Query Designer to define Calc fields, then you can use
TdaSQLBuilder.CalcFields[ ] to acess the calc fields items at run-time. The
items are of type TdaCalculation and have an Expression property.

TdaSQLBuilder can be used from RAP or Delphi code.

See the TdaSQLBuilder topic in the Reportbuilder help file. And from there
traverse to the subtopics.



--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com