SQL Editing and Calc field issues

rbuser

Hi,

We have Report Builder 10.07 for Dephi 2006. We provide canned reports
(created with RB) in our application for our customers to use. However, we
also provide options for them to filter and group/order data by certain
fields. We retrieve the SQL for the report before running it and change it
to meet our customers criteria. Aftewards, we apply the SQL back to the
report using the following procedure:

function SetReportSQL(const strSQL : String; const ppReport : TppReport;
const strQueryDataView : String = '') : Boolean;
var
daQueryDataView : TdaQueryDataView;
begin
SetReportSQL := false;
// If report is valid...
if ppReport <> nil then
begin
// Get query data view
daQueryDataView := GetReportQueryDataView(ppReport, strQueryDataView);
// If view is valid, get SQL; if SQL is valid, set SQL string
if daQueryDataView <> nil then with daQueryDataView do if SQL <> nil then
try
SQL.EditSQLAsText := true;
SQL.SQLText.Text := strSQL;
OutOfSync;
SetReportSQL := true;
except
end;
end;
end;

The problem occurs when we reference a field during an OnCalc event within
the report using a variable. Statements such as

Value := History['Taxes']

will result in a null value, while

Value := 10;

will work correctly. The reports work fine as long as we do not attempt to
change the SQL before running them. So I am wondering if our SetReportSQL
is incorrect or flawed in some manner. Any help on this would be greatly
appreciated.

Thank you.

Brian

nicocizik

Hi Brian,

The latest version of ReportBuilder includes the TdaSQLBuilder object which
makes accessing and altering the SQL object much easier and efficient. I
would recommend using this feature to gain access to the SQL object in your
application. Start by taking a look at the help topic for the TdaSQLBuilder
class and related classes. These topics provide an extensive explaination
and code examples of how it is to be used.

--
Regards,

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

rbuser

This is a multi-part message in MIME format.

nicocizik

This is a multi-part message in MIME format.

rbuser

Hi Nico,

Thanks for the info. We have run into an issue using the SQLBuilder with
many of our reports.

We have numerous reports that contain sophisticated queries that of which
cannot be designed using the query wizard. Therefore, we manually wrote
these queries and pasted them into the "SQL" area of the dataview on the
"Data" tab. It appears as if we are unable to use the SQLBuilder to add
search criteria to these reports since they were not designed using the
wizard.

To summarize our problems, editing the SQL as text adversely affects the
calc fields on the report. Using the SQLBuilder approach limits our ability
to provide search criteria on reports that have custom SQL.

Could you suggest another alternative? Thank you.

Thank you in advance.

Brian

nicocizik

Hi Brian,

If your queries are too complex to create using the Query Designer (or
SQLBuilder), then you will need to create then manually in the SQL tab as
you mentioned. This however brings up a limitation in DADE that you will
loose all dataview linking support (a warning is given before DADE allows
you to edit the SQL text). You can then manually link your datasets in code
if you need (see the example below).

It is currently on our todo list for the next major release to enhance DADE
to retain linking support with editied SQL code.

http://www.digital-metaphors.com/tips/EditSQLAndLink.zip

--
Regards,

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

rbuser

> It is currently on our todo list for the next major release to enhance
DADE

O yes :-) !!. Do you accept pre-orders? (or beta testers)

Regards,
Jeroen R?ttink

rbuser

I don't know if this will solve your problem, but we ran into the same
issues. What fixed the problem was upgrading to Report Builder 10.08 and
removing the following line from our code:

SQL.EditSQLAsText := true;

Hope this helps.

Doug