Passed In values in a SQL call

rbuser

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

nicocizik

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

rbuser

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.

rbuser

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.

nicocizik

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

rbuser

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

nardmoseley

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

rbuser

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.

nicocizik

http://www.digital-metaphors.com/rbWiki/General/Installation/Tech_Tip:_Demo_Location


Regards,

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