SQL at runtime.

rbuser

Hello,

This is probably the question that is being asked the most, but I can't find
the right answer. Please bear with me.

I have a pipeline which gives me access to certain data. I need to access
though some data which is not available via a pipeline, i.e. I want to write
a custom SQL which needs to take a parameter from the pipeline.

For example, the pipeline gives me access to the CLIENTS table, but in just
ONE particular report I need to print some extra information about the
client, which comes from a different table, to which I DO NOT have (and I
don't want to have) a pipeline. The additional SQL should be something like
this:

select extra_info from clients_extra where clients_extra.id=:id

where the :ID parameter comes from the ID of the current record in the
pipeline.

How do I do that IN RAP (!!)?

See, the problem is that different reports need to access all kind of data,
related to the main data pipeline. I do not know what data each different
report might need and it is not possible to simple have many additional
pipelines. If I say it differently, I have different clients who require all
kind of different reports. I do not want to alter my EXE for each individual
client . That's the main idea to use RB right ;-)?

Ah, one more problem eventually might be that of course, once I get the data
from the SQL I need to print it, so can I dynamically create a pipeline to
it, or will I have to write an OnPrint event for each control in order to
access the data?

Please note, I need all of this using only RAP, the delphi application does
not "know" anything about the report.

Thank you very much!

Dimitar

nardmoseley

- A report definition typically consists of layout elements, data access
elements, and code. Using RB Enterprise, all of these can be created via the
report designer and saved externally (i.e. outside of the .exe).

- RAP can be extended with custom classes and functions. See the RAP thread
in the Tech Tips newsgroup for an article titled Extending RAP.

- Each report/subreport can traverse a datapipeline (i.e. a query result
reset). My point here is that the two go hand in hand. If you generate SQL,
then perhaps you are also wanting to generate a layout you can print
something on the report? One option is to create sub-layouts that contain a
single query and layout. And then at run-time assemble a main report that is
made up subreport objects. For each subreport, dynamically load one of the
sub-layouts. (mySubreport.Report.Template.Load). The report designer can
also be used to load subreport (.rtms) by selecting the workspace for the
subreport and then choosing File | Load Subreport.

- Loading layouts or creating layouts in code is something that you should
prior to running the report. Use the Report.OnIntializeParameters event.

rbuser

I am trying really hard to decode your tips, but so far I am having trouble.
I would really appreciate if you could please be more specific.

Are you saying that I need to create an SQL object in RAP (in which event
;(?? ), then in onInitializeParameters I could pass the parameters to the
query? I don't really need to create a custom layout at runtime; the
components may very well be in the template itself (I am keeping them in the
database), just their datasource (pipeline) is the query, whose fields are
static and know at report level; it is the data that is being fetched from a
source, generated by a parametrized SQL.

May I post an example of some kind here?

Your help is greatly appreciated.

Thanks.

Dimitar

nardmoseley

- Please do not post attachments to the newsgroups. If you would like to
create a simple example that demonstrates what you are trying to accomplish
you can email to support@digital-metaphors.com in zip format. Please use
standard Delphi components, ReportBuilder, and the DBDemos data or a simple
MS Access database that we can run here. Please do not send your actual
project and reports, keep the example simple and focused.

- ReportBuilder's AutoSearch feature provides a good solution for handling
parameterized SQL. You can use the Query Designer to add search criteria for
the query and designate them as AutoSearch. RB will automatically generate
an ask at runtime dialog, display it to the user, and then modify the SQL.

- Typically when the DBText are already part of the layout, then they are
bound to a DataPipeline and DataField at design-time. At run-time the SQL is
modified with the parameter value.

rbuser

Hi,

How to access, in report, the values specified by user in "autosearch"
dialog (at runtime) ?

I need this information to print it on footer of report.

Thanks and sorry my poor english.

Samuel

Nard Moseley (Digital Metaphors) escreveu:

nardmoseley

For an example, check out RBuilder\Demos\AutoSearch\Build Description of
AutoSearch Settings


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