Oracle Reports Tutorial:

Arturo T.

Oracle Reports is a powerful tool aimed at the design and generation of reports.
It allows the creation of reports in jsp (Java Server Pages) files, rdf, xml, rtf among others, but being
the most used ones mentioned above. Likewise, it allows sending the result of the reports to
text files, pdf, html, xml, rtf, delimited text, among others, which allows for their reading and
publication in various formats.

Let's get into the subject on this topic.

Like oracle forms, this tool contains an object navigator from which you can
access each of the elements that make up the definition of a report.

In figure 1, the elements that make up a report can be seen. The data model is an area
where the content of the report is mainly defined; that is, the definition of queries, calculations,
functions that will return the data for the respective report.

Paper layout is an area where the visual part of the report is primarily defined; that is, it is
define the types of letters, sizes, location of columns, colors, spaces, headers, and in
general, everything that has to do with the presentation of the report.

The parameters screen is an area where you can customize the input of the parameters to
report. For example, if there is a report about the items sold by a company, they could
Set parameters for the start date and end date for which you want to know this information.

Report triggers, it is a block where actions are defined that are triggered mainly before
whether the report is generated before or after its execution depending on the policies of
business.

Program units and Libraries connected just like oracle forms is an area where you can
define in the first instance, procedures, functions, packages that can be used from the models of
data, and in the second, links to pll libraries or bookstores, where the functions can be found,
procedures or packages.

Fig 1. Elements of a report

Next, we will look at these areas in detail.

As previously indicated, this area is responsible for defining the data that will be displayed in
the report. For which it is necessary to define the queries to the different sources (tables, views,
stored procedures, xml files, olap modules), define the aggregations and schedule them
additional functions that may be necessary in the report.

Figure 2 shows the tools of the data model.

Fig 2. Object model tools

The summary column tool

It allows defining summarization operations such as counting, sum, average, maximum value, value
minimum, variance, standard deviation, among others, whose resulting column can be incorporated into the
report. For the same example of the sales report, it may be necessary to include at the end of each day the
total value sold, and at the end of the selected range, the total value sold and average value; for which
this tool could be used.

The formula column tool

It allows the incorporation of PL/SQL blocks for the definition of additional procedures and functions.
that may be necessary for the calculation of specific operations. The value returned by this
Column can also be shown in the report. Returning to the example of sales, let’s suppose that
It is necessary to compare if the total sold per day exceeds a value X, in which case the company should not.
to worry; but if sales do not exceed that value X, then oh what happens?!

For this, the report can include a column that compares the total value with value X and if total value
if valortotal >= X then return "there are no problems", but if valortotal < X then return "on this day we did not fare well"
very good. Of course, this is an example of a toy that shows a minimal range of what it could
get formula columns.

If you want to delve deeper into PL/SQL, you can refer to thePL/SQL manual

The SQL query tool

We are going to do an example in the tool to show the previous three tools:

1. Let's define the structure of the data sources.

2. Let us assume that the table structure is:The text provided does not contain any translatable content.

3. Now, it is desired to generate a report that shows for each student, the subjects taken with their
respective qualification

4. We run the oracle reports tool, and proceed to create a new report.

Being in the data model window, we select the SQL query option and place it on the
editing sheet (for this, just click on the tool and then click on the background of the sheet)

A window appears requesting the query we wish to execute. It is here where we have the
two options: use the query builder or personally dive into writing the SQL query. For the example
we will use the query builder.

In order to access the query builder, it is necessary to authenticate as a user of the Oracle database, therefore it
You must register the username, password, and connection string.

Once the connection is validated, a list of the objects available to the user is displayed (tables,
views, synonyms, snapshots), from which the objects to be used in the query must be selected. In
In our case, the tables student, subject, and enrollment will be selected.

Then, after this, click the include option.

With the above, the definitions of the selected tables are included in the query builder screen.
For our example, we will select the fields as shown in the following figure, which are
Basically, the fields we want to display in the report: from the student table the fields id, name and
last name; from registration, the fields id_materia and grade, and from the subject table we want to show the
name of the subject.
Do not worry about the relationships, the tool builds them automatically based on the keys.
foreign keys defined for each table.

Then click accept, we now have the SQL query ready! (Of course, if we know the details of the language
We can write the statement directly without the need to call the query builder.

And once again accept to view the generated data model. The following figure shows the group of
data generated through the query that has just been defined. The concept of group is important because
define the way in which the data will be displayed later. In this case, we only have a
group made up of the student's ID, the student's first name and last name, the subject ID, the name
of the subjects taken and the grade. If the student took 10 subjects, some of this data such as id, name
the student's last name will be repeated 10 times.
Here it would be necessary to implement a way to view the report in a master-detail format.

To divide this group into two groups, it is only necessary to select the fields that are part of the detail and
take them out of the group (or remove them from the group), which would leave us with something like this:

Let's now add a summary column that shows us the average of the grades.
obtained by each student. For this, we select the summary column tool and place it
inside the first group (the student group), and we place another summary column outside of the groups
(CS_2), leaving us with something like this:
We are going to explain what we want by including two summary columns CS_1 and CS_2, it can be seen that
CS_1 is a summary at the group level G_Name and what we want with CS_2 is to have a summary at
level of the entire report. That is, we want a summary of the average grades per student and a
summary of the overall average.

Now, we select the CS_1 field and open the properties window (right-click on the)
mouse - property inspector option) and we set the values as indicated in the following figure:

As can be seen, for the property function allows selecting from a list of operations (counting,
sum, maximum value, ...)

The source property indicates the field that is involved in the summary.

The Reset property in this case indicates that it will perform the defined operation for each student.

In conclusion, the average value of the rating field will be found for each student.

Now, we select the CS_2 field, we open the properties palette again and set the
next values
In this configuration, we are indicating that the summary should be made at the level of the entire report, and
let's average the students' averages.

Ready. Now we only need to generate the visual part of our report. For this, we select the
option program/run paper layout. This assistant helps to configure the visual part of
report and what concerns the data.

One of the steps within this assistant is defining the style of report to create. For neutral.
For example, we will select the option to group at the top. By clicking next, the data source is edited (the
which we have already defined), then the data tab where the fields to be displayed are selected.
in the report. We will not always want all the fields in the select clause to be displayed. The
groups are already defined. Finally, if you want to generate the report with a template, it can be
selected, or if on the contrary you do not wish to use a template, there is also the possibility to select
this decision.

Ready, once the assistant is finished, our report would look like:
Very well, now the task is solely to make our report look nice. From the object browser,
enter the paper disposal area and customize the report in terms of font, colors, location
This first delivery does not extend the explanation of the paper disposal area. This will be done further.
forward in another delivery)

The Data Link tool

Allows linking fields between different queries. If there are 3 queries a, b, and c, and from a to b there is a
link, and between a and c there is another link, the query a is executed first, then query b and then the
consult c, linking by the defined field. An example that illustrates this idea would be: there are several
tables that model a person's resume, a PERSON table stores personal data
idPerson
performed by each person (whose fields could be idPerson, studyType, degreeObtained, duration,)
another table EXPERIENCE keeps the history of each person's jobs (the fields for this table
they could be idPerson, companyWorked, duration, position, .), another table PRODUCTION stores the writings.
work, research conducted by each person.

Assuming that it is necessary to print the resume of each person, four queries can be defined to
each of the previous tables and then make links by the idPersona field in order for the queries
filter the data for each of the records in the person table. For this example, the structure of
report would be:

Personal data:
…
…
Studies conducted:
…

Work experience:
…
….

Production:
…

In practice, the previous example would be developed with the following steps:

1. Taking this into accounttable structure.

2. Run the Oracle Reports tool, create a new report.

3. In the data model, create the following 3 SQL queries

a. select * from person

b. select * from studies

c. select * from jobs

With which, the model of acts would be as follows:

Now, using the link tool, we are going to join the fields of the 3 queries by the idpersona field.
The procedure is very simple; it is enough to take the data link tool (making
click on this), then merge the fields of the queries by dragging the mouse from the field of the
consult the origin up to the field of the destination query on which we want to make the link. For this
case from the field idPersona to the field IdPersona1, and another from the field IdPersona to the
Field IdPersona2. The data model would now look like:
Ready, now executing the paper arrangement (style: group at the top, groups: group of people)
transversal/desc, descending study group, descending job group, fields:
select them all, template: none), and by adding a few tags, it would finally have the
next report:

As can be seen, this data linking functionality allows for linking the results of several
queries by one or several fields, showing in each block of results, the respective records
associated with the linked field. Assuming that for person X there are no records in the table
jobs, the jobs block would not be displayed for this person X.

The jdbc query tool

It allows the definition of SQL queries to be executed on data sources using the driver.
jdbc; therefore such sources can be oracle, db2, access, mysql, sql server, etc.
The most important thing in this option is to define the connection string to the data source to be used.
synthesis, this option is highly recommended when wanting to incorporate data that is not available within
our oracle db, but which are equally important for our business.

Let's carry out the following practice to see the operation of jdbc queries:

We are going to create a database in Microsoft Access called [Link]

We create a table called tarifa with the following fields cod_tarifa (numeric data type), value (type
of numeric data

We proceed to populate that table with some values

2. Then, we create an ODBC data source (for Windows systems, go to the control panel)
call miodbc_tarifa that has as source the file [Link] that we just created in the point
anterior.

3. We run the oracle reports tool, create a new report, and from the object model,
we take the JDBC query tool and place it on the editing sheet. Once this is done, it appears
a dialog box requesting the SQL query to execute and the connection to use. In the box for
we will write a query

select * from rate

to define the connection we click on the connect button, which shows another dialog box
to specify the connection parameters to our ODBC.
We select jdbc-odbc as the type of driver and in the database box we write the name.
assigned to the ODBC miodbc_tarifa

Ready, now click on connect, and then accept. If there are no syntax issues in the SQL query and it
properly define the type of controller and the name of the database, it should appear in the
data model the following model:

4. We only need to create the visual part or paper layout. For this, go to the option
program/run paper layout, which will guide you through this process.

The Text Query Tool

Allows reading a data file in txt, log format, comma-separated or with fixed-length fields, and
show them in the report. It should be noted that this option is only used in very
particular since access to files is a computationally expensive task, which results in the
response time, and it is directly related to the size of the data file.

Let's see the respective example:

1. let's assume you have the following data [Link](you can download it and save it in a
folder on your pc)

2. run the oracle reports tool and proceed to create a new report. In the data model,
select the text query tool and then place it in the model's editing sheet. The following
A dialog box will appear to define the file name that we are going to upload and the file type.
(Apache server log, txt file separated by commas or txt file separated by tabs)

For this case, we select a txt file separated by tabs and indicate the full path of the
data file. At the end, we click accept to continue.

Then you can see something like this in the data model:

Now we just need to create the paper layout. For this, proceed in the same way as in the previous example.