creating-data-model-for-a-report

There are 3 steps to build a report with oracle report builder.

·          Create a new report definition

·          Define the data model. Data along with the relationships and calculations can be specified to product report output.

·          Specify Layout.

            To specify data for the report, a data model should be defined. A Data model is composed of some or all of the following data definition objects:

Writing Queries

            Queries provide the data for report. We can create a query using the

Report Wizard,

Data Wizard, or

Manually using the query tools in the Data Model tool palette.

Query can select data from any data source (Oracle, XML, JDBC, Text, Oracle Express, Oracle OLAP, or your own data source that you can access through the pluggable data source (PDS) API). We can also use a REF CURSOR to create queries. This Statement s is fired each time the report is run.

Creating a query

To create a query, we can use any of the following tools:

·         Report Wizard (single-query reports only)

·         Data Wizard

·         SQL Query tool (to create a query that selects data from an Oracle relational database)

·         XML Query tool (to access an XML data file, if you have the DTD file)

·         JDBC Query tool (to access any JDBC-enabled data source)

·         Text Query tool (to create a query that selects data from a text pluggable data source)

·         Express Server Query tool (to create a query that selects data from an Express Server)

·         OLAP Query tool (to create a query that selects from multidimensional Oracle OLAP (on-line analytical processing) data stored in an Oracle database)

·         REF CURSOR Query tool (to use REF Cursors)

Creating a query using Report Wizard

To create a query using the Report Wizard:

·         On the Data page of the Report Wizard, enter the query statements for the type of data source previously selected. For example, if you selected the SQL Query data source, you can enter your SQL query in any of the following ways:

·         Type the SELECT statement in the Data Source definition field.

·         Click Query Builder for a graphical method of creating a query without knowledge of SQL.

·         Click Import Query to use a query that has been written by someone else or to use the text editor of your choice.

To create a query using the Data Wizard: 

1.      In the Data Model view, choose Insert > Query to display the Data Wizard.

2.      Follow the wizard to create the first query for the data model.

3.      Repeat Steps 1 and 2 for each query you want to create.

4.      Modify the resulting data model in the Data Model view.

5.      To re-enter the Data Wizard, do either of the following:

·         Right-click, and choose Data Wizard.

·         Click the query, then choose Edit > Settings.

Note: The Data Wizard does not support creating links between queries. To define parent/child query relationships, you can create a data link manually.

Creating a query using SQL Query tool

To create a SQL query:

1.      In the Data Model view, single-click the SQL Query tool in the tool palette.

2.      Click in the main area (canvas region) of the window.

3.      In the SQL Query Statement dialog box, define a SELECT statement for the query:

·         To use Query Builder for an easy graphical method of creating a query without a knowledge of SQL, click Query Builder.

·         To import a query from a file, click Import Query.

·         To enter the SELECT statement yourself, type it in the SQL Query Statement field.

4.      Click OK.

5.      Refine the query using either of the following methods:

·         Right-click the query object, then choose Property Inspector to set desired properties.

·         Right-click the query object, then choose Data Wizard to specify which fields to display, group fields, and any totals.

6.      Repeat Steps 1 through 5 for each query you want to create.

Note: If you define multiple queries in the Data Model view of your report, the Data page does not appear when you invoke the Report Wizard to default the layout.

Creating a query: XML Query tool

To create an XML query:

1.      In the Data Model view, single-click the XML Query tool in the tool palette.

2.      Click in the main area (canvas region) of the window.

3.      In the Define XML Query dialog box, define the XML query based on the fields defined in the data definition file (DTD) selected. The query is run against the data in the XML data file. If an XSL file is specified, it translates the XML data file before running the query.

4.      Click OK.

Creating a query using JDBC Query tool

To create a JDBC query:

1.      In the Data Model view, single-click the JDBC Query tool in the tool palette.

2.      Click in the main area (canvas region) of the window.

3.      In the JDBC Query dialog box, define the JDBC query or procedure and the connection parameters for the data source. Click Help for assistance.

4.      Click OK.

Creating a query using Text Query tool

To create a Text query:

1.      In the Data Model view, single-click the Text Query tool in the tool palette.

2.      Click in the main area (canvas region) of the window.

3.      In the Define Text Query dialog box, specify the data definition and data source for the text query. Click Help for assistance.

4.      Click OK.

Creating a query using  Express Server Query tool

To create an Express Server query:

1.      In the Data Model view, single-click the Express Server Query tool in the tool palette.

2.      Click in the main area (canvas region) of the window.

3.      In the dialog box, specify the definition for the Express Server query. Click Help for assistance.

4.      Click OK.

Creating a query using OLAP Query tool

To create an OLAP query:

1.      In the Data Model view, single-click in the tool palette.

2.      Click in the main area (canvas region) of the window.

3.      In the dialog box, specify the definition for the OLAP query. Click Help for assistance.

4.      Click OK.

Creating a query using REF CURSOR Query tool

To create a REF CURSOR query:

1.      Create a package that defines a REF CURSOR type in one of the following ways:

·         Create a local program unit

·         Create an external PL/SQL library

·         Create a stored program

2.      If the package created in Step 1 is an external PL/SQL library, you must attach it to the report before referencing it.

3.      In the Data Model view, single-click the REF CURSOR Query tool in the tool palette.

4.      Click in the main area (canvas region) of the window.

5.      In the PL/SQL Editor, type the PL/SQL for a function that opens a cursor and returns a cursor variable of the REF CURSOR type you defined in the package.

For example:

empCur rcPackage.empCurType;

BEGIN

  OPEN empCur FOR SELECT * FROM emp;

  RETURN empCur;

END;

/* Note, rcPackage is a local program unit defined as: */

PACKAGE rcPackage IS

  TYPE empCurType IS REF CURSOR RETURN emp%ROWTYPE;

END;

6.      Refine the query as desired:

·         Click the REF CURSOR query object, then choose Tools > Property Inspector to modify properties.

·         Click the query object, then choose Tools > Data Wizard to specify which fields to display, group fields, and any totals.

7.      Repeat Steps 1 through 6 for each REF CURSOR query you want to create.

Creating a query using Data Wizard

Creating a query using Data Wizard

Creating a query using Data Wizard

Creating a query using SQL Query tool

Creating a query using SQL Query tool

Creating a query: XML Query tool