Need to create custom Pipelines - Sanity Checking

rbuser

I will try to be brief, but it will help if there is some understanding in
what I am trying to do. While I have had some success with the built-in
query tools, I have often found that they do not provide me with what I
need for complex business queries. For example, I might want to call a
stored procedure and use the autosearch processes to allow for an end-user
to provide runtime parameters. I have also been working with MySQL which
does not currently provide stored procedures, but I have needs for queries
that are built from multiple queries including the extensive use of
temporary tables.

So I have developed some procedures and components which allow multi-step
queries to be produced outside of the Report Builder framework. This has
several advantages, including the fact that I have some developers who are
well-versed in creating extremely complex queries, but not adept at
formatting the output in ReportBuilder.

I have created a special program in a manner similar to the end user
framework, where a report designer can select a multi-step query on which
to base their report (the queries are stored in the database). Before
instantiating the report designer, in code, I create an array of queries,
datasources, and dbpipelines to match the multi-step query (including
actually firing off the queries results sets from test values). So for
example, by the time the designer is up and running, it may have 5 data
pipelines defined and ready for designing.

At runtime, the queries are placed into an array. Also stored in the
database are parameters which are fed into code to create the appropriate
autosearch elements. After the autosearch dialog is closed, the values are
set as parameters for the array of queries, and the report actually opens
the queries as it is run.

For the enterprise version, this is working extremely well. I now want to
bring this framework into the Server environment.

I need to create the various queries, datasources, and dbpipelines and I
need to create them at the right moment. I am currently using the
ReportExplorerVolume component to display the directories of reports to the
client programs.

I have investigated the various events to try and determine the best events
to use for my purposes. I have to construct the pipelines before the
report is instantiated. The only success I have had so far is with the
'OnLoadReportStart' event. If the pipelines are constructed in this event,
the report appears to have access to all the right data.

But this event does not tell me which report has been selected by the user,
so I have no basis on which to load the queries. All the other events that
I can use for this purpose seem to be called AFTER the 'OnLoadReportStart'
event. I would have thought, for example, that I might be able to use the
'OnAuthenticateReportAccess' event, but this appears to be fired after the
report is loaded.

It would be helpful to have a better understanding of the order in which
some of these events are fired, as there is no source code to step through,
so I have had to go through some trial-and-error here.

I have thought of an approach, and before beginning an extensive amount of
work in this area, thought I would ask for a sanity check. I also have a
question about freeing elements that I create.

APPROACH
When the report is designed in the report designer, I will use the 'custom
information area' in the stream that is stored with the template to store
information that will allow me to retrieve my queries (all I need is a
query_id).

In the 'OnLoadReportStart' event, I will read this query_id and create the
query components, datasources, and dbpipelines. I will assign the
'BeforeAutoSearchDialogCreate' and 'OnGetAutoSearchValues' of the report to
processes which will define and interpret the parameters for the queries
involved.

Can you foresee any problems with this approach?

Specifically, if I am creating the query, datasource, and dbpipeline
components at runtime in the 'OnLoadReportStart' event, do I need to worry
about freeing them? Can I make them owned by the report and will they be
freed accordingly?

Are there any multi-threaded issues to contend with that might be a
problem? The queries would be assigned to a database that has a Session
component associated with it.

Thanks in advance for any ideas, advice, or help.

--
Bob McClintock

nardmoseley

1. Try using something like the code below.....

procedure
TdmReportTemplateVolume.rsReportTemplateVolume1LoadReportStart(Sender:
TObject; Stream: TStream);
var
lReportTemplate: TppReportTemplate;
lReport: TppReport;
begin
if (Sender is TppReportTemplate) then
begin
lReportTemplate := TppReportTemplate(Sender);
lReport := TppReport(lReportTemplate.Report);

ShowMessage('report template name = ' + lReportTemplate.FileName);
end;


end;

2. The datamodule that contains the report volume component should have
separate sesssion/database connection components that are configured to be
thread-safe. All additional data access component that you create should be
'Owned' by the datamodule and should connect to the thread-safe session.





--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com

rbuser

I am also using mysql and found a company that develops very nice mysql
components that behave just like the BDE table components and have made my
life with mysql much easier (No Queries need to be created). If your
interested you can check out their web site at
http://www.microolap.com/products/dac/mysqldac.htm Their trial components
have an annoying message that pops up to tell you this is a trial pack.
That message will hang your server as it waits for you to click okay when
you can't see the button since it's running over a webtier. The paid for
components work great.

rbuser

Thanks Nard, this got me off in the right direction and I now have a
solution that seems to be working very well, but have one further
question.

I have a placeholder for 'cleaning up' the queries that support the
pipelines after the report has run (which usually involves deleting any
temp tables that were created while constructing the query for example).

I figured that in the LoadReportStart method that I could assign a
procedure to the AfterReport event and clean up the queries there.
However, this event does not seem to be assignable in the ReportServer
environment.

I have a workaround for this, but was curious whether I was doing
something wrong here. I am successfuly assigning other procedures to the
report's events (such as BeforeAutoSearchDialogCreate).

Thanks again for your help.

nardmoseley

Place the code to clean up the queries in the datamodule.OnDestroy or
ReportVolume.OnDestroy events.

A separate instance of the DataModule/Form that contains the ReportVolume
will be created to execute each report. When the client session times out or
the client session request a different report, this datamodule will be
destroyed.

The Server is using the Report.PrintToDevices method which may cause a few
of the report events, such as Report.AfterPrint to not be triggered.


--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Thanks Nard, as always a quick and a helpful reply. Putting the code in
the Datamodule.OnDestroy seems to do the trick. I tried the
ReportVolume.OnDestroy event first, but the database connection I am
trying to clean up is already gone before this event fires...

--
Bob McClintock