Re: Dynamic Reports in Reports Version3?

From: Mario Zgela <mario_at_hnb.hr>
Date: 22 Dec 1998 11:17:48 GMT
Message-ID: <01be2d9d$670133c0$236faaaa_at_mzgela>


Here is how we tried to solve that problem:

We used DBMS_SQL package for dynamic creation of SQL insert query. That query inserted
records and columne names in temporary table which has one additional column (USER_NAME) which served for making distinction among users. All columns in temporary table are VARCHAR2, because you never know which column types, user will choose from original table.
Report has fixed structure, which means it allways selects all columns from original table, and just does row selection ( in fact, row selection is done by DBMS_SQL constructed query). Of course, such approach has a few limitations:
you can not get nice formatting in report, user can have maximum of one report at time in temporary table (although this could be improved by using Oracle sequence on report level), you can have performance problems because of selecting AND inserting (
because INSERT INTO TEMP SELECT x,y,... FROM ORIGINAL WHERE ...) AND selecting again (from reports).

This solution is implemented on Reports 2.5 and Oracle 7.3.3 database.

It is possible that Reports 3.0, which I'm not familiar with, support receiving record groups which are dynamically created in Forms, which will be much better solution, because you don't have to use temporary table, DBMS_SQL etc.

I would like to hear from you when you solve the problem.

Bye,
Mario

Kurt Manecke <kmanecke_at_ee.net> wrote in article <75mjb2$f9c_at_news1.ee.net>...
> I have been given a task of developing somewhat of a dynamic report.
There
> is one table involved. The users will see a screen which lets them pick
> which columns to include in the report, parameters for each column (i.e.
> search criteria), which columns to sort on, as well as which field(s) to
> perform a summary on (breaks). Is something like this possible with
Reports
> Version 3? Can I change the SQL query/Order by at runtime? Can I add
breaks
> at runtime? This sounds to me like I should recommend they purchase an
> end-user reporting tool, but my boss wants it done in Reports. I know I
> could give them Reports, and tell them to use the Report Wizard, but that
is
> not an option. Any pointers on how to approach this would be greatly
> appreciated.
>
> Kurt Manecke
> kmanecke_at_ee.net
>
>
>
>
Received on Tue Dec 22 1998 - 12:17:48 CET

Original text of this message