Home RAP
New Blog Posts: Merging Reports - Part 1 and Part 2

Modifying SQL in RAP

edited August 2009 in RAP
I am using Report Builder Enterprise 11.05. I am converting my existing
reports to use Report Explorer, End User Report Designer and RAP.

I wish to create a report based on a query similar to the one below.

SELECT
Client.ID,
ClientEpisode.EpisodeDate,
(SELECT Result FROM TestResult AS TR1
WHERE TR1.ClientEpisodeID = ClientEpisode.ID
AND TR1.TestNo = 1) AS Test1

FROM
Client
INNER JOIN ClientEpisode
ON Client.ID = ClientEpisode.ClientID

WHERE ClientEpisode.ID = :ClientEpisodeID;

It appears that I cannot create the query below in the report designer
because of the SELECT clause in the 3rd column. I can enter the SQL
statement directly into the SQL tab of the Query Designer. However, it
disapproves of the parameter in the WHERE clause.

I wish to use the report's parameter to assign a value to the
ClientEpisodeID parameter for the above query.

I thought it would be possible to use the OnInitializeParameters event
to build the required SQL.

procedure ReportOnInitializeParameters(var aCancel: Boolean);
var
aParameter: TppParameter;
S: string;

begin
if Report.Parameters.Count = 1 then
begin
aParameter := Report.Parameters.Items['ClientEpisodeID'];
// Assign the required SQL text to something

end;

aCancel := true;
end;

Doe you have any suggestions on how I can create a report based on the
above query?

Richard Harding.

Comments

  • edited August 2009
    Hi Richard,

    You should be able to construct the entire SQL query you have below using
    the TdaSQLBuilder object.

    1. For the sub-select, simply use the TdaSQLCalcFields.AddExpression
    routine and enter the select statement directly as the "expression"
    parameter.

    2. For the Join, use the TdaSQLSelectTables.AddJoin routine.

    3. For the parameterized search, you can use the
    TdaSQLCriteriaList.AddAutoSearchWithParam or AddParamSearch routines.

    See the RB help on the above objects for more information and example code
    and take a look at the following articles...

    http://www.digital-metaphors.com/rbWiki/DADE/SQLBuilder

    --
    Regards,

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

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited August 2009
    Thanks Nico.

    I did make some progress but come unstuck a bit further along.
    Here is a portion of the query - a little changed from last time.

    SELECT
    Client.ID, Client.LastName, Client_2.LastName LastName_2,
    (SELECT Result FROM TestResult
    WHERE TestResult.ClientEpisodeID = ClientEpisode.ID
    AND TestResult.TestNo = 1) AS Test1
    FROM
    ClientPractitioner ClientPractitioner
    RIGHT OUTER JOIN Client ON
    (Client.ID = ClientPractitioner.ClientID)
    LEFT OUTER JOIN Client Client_2 ON
    (Client_2.ID = ClientPractitioner.PractitionerID)
    INNER JOIN ClientEpisode ON
    (ClientEpisode.ClientID = Client.ID)


    Changing Table Aliases
    ----------------------
    Is it possible to change the Table Aliases "Client" and "Client_2"? It
    does not appear to be possible in the "Tables" tab of Query Designer.

    CROSS JOINS
    -----------
    If a Table is added to the query, it appears that a JOIN operator is
    required. Is it possible to have a FROM clause with 2 tables without an
    operator? For example, FROM TableA, TableB.


    I am converting existing reports to use RAP and DADE. Most of the time
    it is very easy. I am only having trouble with a few reports. Is it
    possible to directly assign the SQL (to something) without using
    TdaSQLBuilder to construct it?

    In the Query Designer, it is possible to directly enter the SQL. Can I
    do the same in RAP?


    ----------
    Richard Harding




  • edited August 2009
    Hi Richard,

    In some cases (as the queries become more advanced) it may be easier to edit
    the SQL as text like you mention.

    You can access the SQL object using the TdaSQLBuilder as well. Setting the
    TdaSQLBuilder.SQL.SQLText property will directly edit the SQL text sent to
    the DB. This is also possible from RAP.

    --
    Regards,

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

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited August 2009
    Thank you Nico.

    It is working now.

    Below is the code in case anyone is interested for future reference.

    procedure ReportOnInitializeParameters(var aCancel: Boolean);
    var
    aSQLBuilder: TdaSQLBuilder;
    aParameter: TppParameter;

    begin
    { Append WHERE clause to existing SQL }
    if Report.Parameters.Count = 1 then
    begin
    aParameter := Report.Parameters.Items['ClientEpisodeID'];
    aSQLBuilder := TdaSQLBuilder.Create(ClientEpisode);
    aSQLBuilder.SQL.SQLText.Text :=
    aSQLBuilder.SQL.SQLText.Text +
    ' WHERE ClientID = ' +
    IntToStr(aParameter);
    aSQLBuilder.ApplyUpdates;
    end;

    aCancel := false;
    end;


    Richard Harding


This discussion has been closed.