Home RAP

Passed In values in a SQL call

edited April 2011 in RAP
RB 12.03 Server / Delphi 2007 / Oracle 10G

Using a report parameter I'm able to pass in the 'effective' date to a
report. I'm able to print tyhe value passed in using a label etc. Now I
want to use that passed in effective date in a SQL function call I have
defined on the SQL CALCS tab of the data pipeline.


Right now I'm able to use the SYSDATE and the report works as expected using
the current database system date:
EQP_CUR_RATE(RateID, trunc(sysdate))


I want to be able to do this or anything like it that gets the job done:
EQP_CUR_RATE(RateID, EffectiveDate) where EffectiveDate is a Global Report
variable filled from a Report.Parameter at initialization.


Thanks for any assistance - Jon Gray

Comments

  • edited April 2011
    Hi Jon,

    For RB 12 we added support for the use of parameters inside a calculated
    field. You should be able to do something like the following.
    EffectiveDate being a valid report parameter.

    Note that it is not possible to use global RAP variables in DADE.

    EQP_CUR_RATE(RateID, :EffectiveDate)


    Regards,

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

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited April 2011
    Thanks Nico - I think I'm almost there however I forgot to say -

    The EQP_CUR_RATE function is an Oracle function, not an internal report
    function or pass through.

    Can a report parameter be used in DADE? Or can I make the call somehow from
    a calculated field? The function itself does oracle lookups so I don't
    think I can internalize it in the report.

    Is there a way to join a single unrelated value from the database (I could
    write the value someplace and retrieve it) to each row in my query and use
    that value in the DADE expression?

    ORACLE.EQP_CUR_RATE(RateID, :EffectiveDate)

    Thanks for your help.

  • edited April 2011
    Thanks Nico - I think I'm almost there however I forgot to say -

    The EQP_CUR_RATE function is an Oracle function, not an internal report
    function or pass through.

    Can a report parameter be used in DADE? Or can I make the call somehow from
    a calculated field? The function itself does oracle lookups so I don't
    think I can internalize it in the report.

    Is there a way to join a single unrelated value from the database (I could
    write the value someplace and retrieve it) to each row in my query and use
    that value in the DADE expression?

    ORACLE.EQP_CUR_RATE(RateID, :EffectiveDate)

    Thanks for your help.

  • edited April 2011
    Hi Jon,

    The Query Designer is simply a tool for creating a SQL statement that is
    sent directly to the DB. If you use a built-in Oracle function inside
    the Calculated fields tab, it should work as designed when executing the
    query. Using the entry you posted below, you would simply need to
    create a report parameter that represents the EffectiveDate, give it a
    value and that will be passed to the Oracle function when the query is
    executed.


    Regards,

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

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited April 2011
    Yes, now I see the report parameter needs to be defined in the report itself
    for me to able use it as ":EffectiveDate". This is actually very powerful,
    I wish I had paid closer attention to the release notes.

    Now this brings up a new problem trying to pass the values form the client
    side. Right now I'm building ALL report parameters on the fly in the client
    by issuing:

    trsClientReport.ReportParameters.Clear;
    trsClientReport.ReportParameters['Name'].AsString := 'Joe Programmer';
    trsClientReport.ReportParameters['Rank'].AsString := 'Student';
    tsClientReport.ReportParameters['EffectiveDate'].AsString := '01-01-2011';
    ...and so on...

    On the server in the TemplateVolumeBeforePublishReport procedure I'm
    transferring the report parameters to the server instance of the report by
    issuing:

    if (aEventParams.Report is TppReport) then
    begin
    lReport := tppReport(aEventParams.Report);
    lReport.Parameters.Add(aEventParams.ReportParameters);
    end;

    Because the server RTM definition already includes the EffectiveDate
    parameter I'm getting this error:

    " TppParameterList.Add: a Parameter named, EffectiveDate' already exists. "


    It must be possible to only set the value of the existing server side
    parameter instead of adding them but I'm unable to come up with the code
    that will do the job. Is there a sample or code fragment someplace that
    would do this that I missed in the Wiki and Docs?

    Thanks - Jon Gray



  • edited April 2011

    The following is from the help topic for TrsClientReport...

    (This is just one small fragment of the help topic.)

    ---------------------

    Custom ReportParameters can be received and sent to the server using the
    GetReportParameters and ValidateReportParameters methods. ReportParameters
    can be used to implement report specific processing on the server.

    For an example of using custom parameters see \RBServer\Demos\Clients\Custom
    Parameters.

    ---------------------

    Therefore you need to call GetReportParameters to retrieve the parameters
    from the server. Then set the parameters in code as you are doing now. And
    then call ValidateReportParamters to send the parameter values to the
    server. The Report.Parameters on the server side are updated for you, you
    don't have to write the code to that.


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

    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
  • edited April 2011
    Nard - thanks for your help.

    I'm not able to locate the demos, are they included in the 12.03 install?
    I don't have a demo folder in either the RBServer or RBuilder folders.


This discussion has been closed.