Accessing SQL Modification

rbuser

Greetings to all:

I have a query that I'm modifying... adding a field (after a date
modification) this works fine...

My question is how to access this new field on the report???

Here is my "before pipeline" info:


procedure ReportBeforeOpenDataPipelines;
var
lSQLBuilder: TdaSQLBuilder;
vDate: TppAutoSearchField;
begin
vDate := Report.AutoSearchCriteriaByName('genledg','date');

lSQLBuilder := TdaSQLBuilder.Create(Acct210);
lSQLBuilder.CalcFields.AddExpression('round( sum( CASE WHEN
genledg.[Date] <= ''' + DateToStr(vDate.value - 30) + ''' THEN ifnull(
[debit amount], 0 ) - ifnull( [credit amount], 0 ) ELSE 0 END ), 0) as
30Bal');
lSQLBuilder.SearchCriteria.Add('Acct210', 'date', '<=',
datetostr(vDate.value));

lSQLBuilder.Free;


end;

When I say it works fine, I don't get any errors when i go to preview
mode... but I don't know how to access the new Expression in design mode?

Any help?

Thanks -

Chris

nardmoseley

I don't understand the question. What are you trying to accomplish?

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

rbuser

Here is what I'm trying to do...

I'm trying to get 2 totals... one for the the amount of activity up
through the date supplied through the autosearch, and one for 30 days
prior to that (so I can give a balance, and a balance 30 days past due).

The report has 2 queries - on is already grabbing some information from
different accounts, and has an autosearch on it for the date. (Genledg).

I am then copying that date into the 2nd query (Acct210). The result of
this query should be 1 row with 2 columns, one for the sum of the listed
account (in search criteria) up through the date added (from genledg) and
one for the sum of the same account, only 30 days prior.

The code, in and of itself, seems to work fine.

the issue I have is how do I access the value from that new calc field
being added at runtime on the design section so that it shows up on the
report? Because its not in the design phase, its not in the list of
available fields from the query.

Thanks again!




On Tue, 02 Mar 2010 12:31:04 -0500, Nard Moseley (Digital Metaphors)

nardmoseley

Thanks for providing more details.

Try something like this

var
lCalcField: TdaCalculation;

begin

lCalcField := SQBuilder.AddExpression('some expression');

// assign the Alias property
lCalcField.Alias := 'myCalcFieldName';

// use the SQLFieldName property to bind to your DBText.DataField
// (the SQLFieldName will be used as the FieldName for the DataPipeline.

myDBText.DataField := lCalcField.SQLFieldName;

end;




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

rbuser

Nard -

I tried that (in some way, probably messed up), but you didn't provide
where to put this at...

Here is what I ended up with:

procedure ReportBeforeOpenDataPipelines;
var
lSQLBuilder: TdaSQLBuilder;
lCalcField: TdaCalculation;
vDate: TppAutoSearchField;
begin
vDate := Report.AutoSearchCriteriaByName('genledg','date');

lSQLBuilder := TdaSQLBuilder.Create(Acct210);
lCalcField := lSQLBuilder.CalcFields.AddExpression('round( sum( CASE
WHEN genledg.[Date] <= ''' + DateToStr(vDate.value - 30) + ''' THEN
ifnull( [debit amount], 0 ) - ifnull( [credit amount], 0 ) ELSE 0 END ),
0) as 30Bal');
lCalcField.Alias := '30Bal';
DBText7.DataField := lCalcField.SQLFieldName;

lSQLBuilder.SearchCriteria.Add('Acct210', 'date', '<=',
datetostr(vDate.value));

lSQLBuilder.Free;


end;

I get an error when I try to preview this.

When I try to just use SQLBuilder (assuming that SQBuilder was a typo),
it won't compile saying it doesn't recognize SQLBuilder.

What I want to have is a report that has multiple queries in it, but
only asks for one date. That part works (adding the date to the
additioinal queries).

But because that date is just an entry the user makes, I want to edit
these queries in certain cases because they will be looking for "older
than 30 days" information, thus I have to massage the date with a - 30
to look for these...

I could run a separate query for each item (one query for acct A
balance, another for Acct A balance greater than 30 days ago, two more
for Acct B, etc, etc... But I figured that would get really slow, and I
could run it in SQL using a Case statement to check the date being older
than 30 days, add it to a second "field", and get them both at the same
time in one query (which, if I manually write the query - works fine,
but then I can't put it in the report...)

Let me know what else you come across

Thanks...

nardmoseley

I researched this further.

If you try Delphi code, it will work. The RAP code results in an AV due to a
bug. I have created a patch for RB 11.07 that fixes the issue. Registered RB
11.07 users can email support@ and request the patch.


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