3 Creating Data Sets

3.4.1 Understanding the Query Builder Process

To build a query, perform the following steps:

1. Select objects from the Object Selection pane.

2. Add objects to the Design pane and select columns.

3. Optional: Establish relationships between objects.

4. Add a unique alias name for any duplicate column.

5. Optional: Create query conditions.

6. Execute the query and view results.

3.4.2 Using the Object Selection Pane

In the Object Selection pane you can select a schema and search and filter objects.

To hide the Object Selection pane, select the control bar located between it and the Design pane. Select it again to unhide it.

3.4.3 Selecting a Schema

The Schema list contains all the available schemas in the data source. Note that you may not have access to all that are listed.

3.4.4 Searching and Filtering Objects

Use the Search field to enter a search string. Note that if more than 100 tables are present in the data source, you must use the Search feature to locate and select the desired objects.

3.4.5 Selecting Objects

The Object Selection pane lists the tables, views, and materialized views from the selected schema (for Oracle databases, synonyms are also listed). Select the object from the list and it displays on the Design pane. Use the Design pane to identify how the selected objects are used in the query.

3.4.6 Supported Column Types

Columns of all types display as objects in the Design pane. Note the following column restrictions:

• You can select no more than 60 columns for each query.

• Only the following column types are selectable:

  • VARCHAR2, CHAR

  • NUMBER

  • DATE, TIMESTAMP

    Note:

    The data type TIMESTAMP WITH LOCAL TIMEZONE is not supported.

  • Binary Large Object (BLOB)

    Note:

    The BLOB must be an image. When you execute the query in the Query Builder, the BLOB does not display in the Results pane; however, the query is constructed correctly when saved to the data model editor.

  • Character Large Object (CLOB)

    For more information about working with CLOB data in the data model, see Section 3.12, "Using Data Stored as a Character Large Object (CLOB) in a Data Model."

3.4.7 Adding Objects to the Design Pane

To add objects to the design pane:

1. Select an object.

   The selected object displays in the Design pane. An icon representing the datatype displays next to each column name.

2. Select the check box for each column to include in your query.

   When you select a column, it appears on the Conditions tab. Note that the Show check box on the Conditions tab controls whether a column is included in query results. Be default, this check box is selected.

   To select the first twenty columns, click the small icon in the upper left corner of the object and then select Check All.

3. To execute the query and view results, select Results.

   Tip:

   You can also execute a query using the key strokes CTRL + ENTER.

3.4.8 Resizing the Design and Results Pane

As you select objects, you can resize the Design and Results panes by selecting and dragging the gray horizontal rule dividing the page.

3.4.9 Removing or Hiding Objects in the Design Pane

To remove an object:

1. Select the Remove icon in the upper right corner of the object.

To temporarily hide the columns within an object:

1. Click the Show/Hide Columns icon.

3.4.10 Specifying Query Conditions

Conditions enable you to filter and identify the data you want to work with. As you select columns within an object, you can specify conditions on the Conditions tab. You can use these attributes to modify the column alias, apply column conditions, sort columns, or apply functions. Figure 3-5 shows the Conditions tab.

Figure 3-5 Conditions Tab

Table 3-1 describes the attributes available on the Conditions tab.

SELECT ename FROM emp WHERE deptno = 10

As you select columns and define conditions, Query Builder writes the SQL for you.

To view the underlying SQL:

1. Click the SQL tab.

3.4.11 Creating Relationships Between Objects

You can create relationships between objects by creating a join. A join identifies a relationship between two or more tables, views, or materialized views.

3.4.12 Saving a Query

Once you have built the query, click Save to return to the data model editor. The query appears in the SQL Query box. Click OK to save the data set.

To link the data from this query to the data from other queries or modify the output structure, see Chapter 4, "Structuring Data."

Figure 3-7 Query Displayed in SQL Query Box

3.4.13 Adding a Bind Variable to a Query

Now you have a basic query, but in the report you want users to be able to pass a parameter to the query to limit the results. For example, in the employee listing, you want users to be able to choose a specific department.

You can add the variable using either of the following methods:

• Add the bind variable using the Query Builder Conditions tab

  To add the bind variable in the Query Builder Conditions tab:

  1. Add the following Condition for the column:

     in (:P_DEPTNAME)
     

     where P_DEPTNAME is the name you choose for the parameter. This is shown in Figure 3-8.

  Figure 3-8 Adding the Bind Variable Using the Query Builder Conditions Tab

  
  

• Update the SQL query directly in the text box.

  Important:

  After manually editing the query, the Query Builder can no longer parse it. Any further edits must also be made manually.

  To update the SQL query directly in the text box:

  1. Add the following after the where clause in your query:

     and "COLUMN_NAME" in (:PARAMETER_NAME)
     

     for example:

     and "DEPARTMENT_NAME" in (:P_DEPTNAME)
     

     where P_DEPTNAME is the name you choose for the parameter. This is shown in Figure 3-9.

     Figure 3-9 Editing the Generated SQL Query

     
     

  2. When you select Save, the data model editor asks whether to create the parameter that you entered with the bind variable syntax, as shown in Figure 3-10.

     Figure 3-10 Create a Bind Parameter Dialog

     
     

  3. Click OK to have the data model editor create the parameter entry for you.

To define the parameter properties, see Chapter 5, "Adding Parameters and Lists of Values."

3.4.14 Editing a Saved Query

When you have saved the query from the Query Builder to the data model editor, you can also use the Query Builder to edit the query.

To use the Query Builder to edit the query:

1. Select the SQL data set.

2. Click the Edit Selected Data Set toolbar button.

3. This launches the Edit Data Set dialog. Click Query Builder to load the query to the Query Builder.

   Note:

   If you have made modifications to the query, or did not use the Query Builder to construct it, you may receive an error when launching the Query Builder to edit it. If the Query Builder cannot parse the query, you can edit the statements directly in the text box.

4. Edit the query and click Save.

3.7.1 About Supported Excel Files

Following are guidelines for the support of Microsoft Excel files as a data set type in BI Publisher:

• The Microsoft Excel files must be saved in the Excel 97-2003 Workbook (*.xls) format by Microsoft Excel. Files created by a third party application or library are not supported.

• The source Excel file might contain a single sheet or multiple sheets.

• Each worksheet may contain one or multiple tables. A table is a block of data that is located in the continuous rows and columns of a sheet.

  In each table, BI Publisher always considers the first row to be a heading row for the table.

• The data type of the data in the table may be number, text, or date/time.

• If multiple tables exist in a single worksheet, the tables must be identified with a name for BI Publisher to recognize each one. See Section 3.7.2, "Guidelines for Accessing Multiple Tables per Sheet."

• If all tables in the Excel file are not named, only the data in the first table (the table located in the upper most left corner) is recognized and fetched.

• When the data set is created, BI Publisher truncates all trailing zeros after the decimal point for numbers in all cases. To preserve the trailing zeros in your final report, you must apply a format mask in your template to display the zeroes. For more information about format masks, see the section "Number, Date, and Currency Formatting" in Oracle Fusion Middleware Report Designer's Guide for Oracle Business Intelligence Publisher.

3.7.2 Guidelines for Accessing Multiple Tables per Sheet

If the Excel worksheet contains multiple tables that you want to include as data sources, then you must define a name for each table in Excel.

To define a name for the table in Excel:

1. Insert the table in Excel.

2. Define a name for the table as follows:

   Using Excel 2003: Select the table. On the Insert menu, click Name and then Define. Enter a name that is prefixed with "BIP_".

   Using Excel 2007: Select the table. On the Formulas tab, in the Defined Names group, click Define Name, then enter the name in the Name field. The name you enter appears on the Formula bar.

   Tip:

   You can learn more about defined names and their usage in the Microsoft Excel 2007 document "Define and use names in formulas." at the following URL:

   http://office.microsoft.com/en-us/excel/HA101471201033.aspx

Figure 3-13 shows how to use the Define Name command in Microsoft Excel 2007 to name a table "BIP_Salaries".

Figure 3-13 Using the Define Name Command in MIcrosoft Excel

3.7.3 Using a Microsoft Excel File Stored in a File Directory Data Source

Note that to include parameters for your data set, you must define the parameters first, so that they are available for selection when defining the data set. See Chapter 5, "Adding Parameters and Lists of Values."

To create a data set using a Microsoft Excel file from a file directory data source:

1. Click the New Data Set toolbar button and select Microsoft Excel File. The Create Data Set - Excel dialog launches.

2. Enter a name for this data set.

3. Click Shared to enable the Data Source list.

4. Select the Data Source where the Excel File resides.

5. Click the browse icon to browse for the Microsoft Excel file in the data source directories. Select the file.

6. If the Excel file contains multiple sheets or tables, select the appropriate Sheet Name and Table Name for this data set, as shown in Figure 3-14.

   Figure 3-14 Selecting the Sheet Name

   
   

7. If you added parameters for this data set, click Add Parameter. Enter the Name and select the Value. The Value list is populated by the parameter Name defined in the Parameters section. See Chapter 5, "Adding Parameters and Lists of Values."

8. Click OK.

To link the data from this query to the data from other queries or modify the output structure, see Chapter 4, "Structuring Data."

3.7.4 Uploading a Microsoft Excel File Stored Locally

Note that to include parameters for the data set, you must define the parameters first, so that they are available for selection when defining the data set. See Chapter 5, "Adding Parameters and Lists of Values."

To create a data set using a Microsoft Excel file stored locally:

1. Click the New Data Set toolbar button and select Microsoft Excel File. The Create Data Set - Excel dialog launches.

2. Enter a name for this data set.

3. Select Local to enable the upload button.

4. Click the Upload icon to browse for and upload the Microsoft Excel file from a local directory. If the file has been uploaded to the data model, then it is available for selection in the File Name list.

5. If the Excel file contains multiple sheets or tables, select the appropriate Sheet Name and Table Name for this data set, as shown in Figure 3-15.

   Figure 3-15 Defining Excel Spreadsheet for Data Set

   
   

6. If you added parameters for this data set, click Add Parameter. Enter the Name and select the Value. The Value list is populated by the parameter Name defined in the Parameters section. See Chapter 5, "Adding Parameters and Lists of Values."

7. Click OK.

To link the data from this query to the data from other queries or modify the output structure, see Chapter 4, "Structuring Data."

3.7.4.1 Refreshing and Deleting an Uploaded Excel File

After uploading the file, it displays on the Properties pane of the data model under the Attachments region, as shown in Figure 3-16.

Figure 3-16 Attachments Region of the Properties Pane

See Section 2.8, "Setting Data Model Properties" for information about the Properties pane.

To refresh the local file in the data model:

1. Click Data Model in the component pane to view the Properties page.

2. In the Attachment region of the page, locate the file in the Data Files list.

3. Click Refresh.

4. In the Upload dialog, browse for and upload the latest version of the file. The file must have the same name or it will not replace the older version.

5. Save the data model.

To delete the local file:

1. Click Data Model in the component pane to view the Properties page.

2. In the Attachment region of the page, locate the file in the Data Files list.

3. Click Delete.

4. Click OK to confirm.

5. Save the data model.

3.8.1 Additional Notes on Oracle BI Analysis Data Sets

Parameters and list of values are inherited from the BI analysis and they will display at runtime.

The BI Analysis must have default values defined for filter variables. If the analysis contains presentation variables with no default values, it is not supported as a data source by BI Publisher.

If you want to structure the data based on Oracle BI Analysis Data Sets, the group breaks, data links and group-level functions are not supported.

The following are supported:

• Global level functions

• Setting the value for elements if null

• Group Filters

For more information about the above supported features, see Chapter 4, "Structuring Data."

3.9.1 Additional Notes on View Object Data Sets

To structure data based on view object data sets, the group breaks, data links and group-level functions are not supported.

The following is supported: Setting the value for elements if null.

For more information about this supported feature, see Chapter 4, "Structuring Data."

3.10.1 Adding a Simple Web Service: Example

This example shows how to add a Web service to BI Publisher as a data source. The Web service returns stock quote information. The Web service passes one parameter: the quote symbol for a stock.

The WSDL URL is:

http://www.webservicex.net/stockquote.asmx?WSDL

If you are not familiar with the available methods and parameters in the Web service to call, you can open the URL in a browser to view them. This Web service includes a method called GetQuote. It takes one parameter, which is the stock quote symbol.

To add the Web service as a data source:

1. Click the New Data Set toolbar button and select Web Services. The Create Data Set - Web Service dialog launches, as shown in Figure 3-19.

   Figure 3-19 Creating a Simple Web Service Data Set

   
   

2. Enter a name for this data set.

3. Enter the Data Set information:

   • Select False for Complex Type.

   • Enter the WSDL URL: http://www.webservicex.net/stockquote.asmx?WSDL

   • Enter the Method: GetQuote

   • If desired, enter a Time Out period in seconds. If the BI Publisher server cannot establish a connection to the Web service, the connection attempt times out after the specified time out period has elapsed.

4. Define the parameter to make it available to the Web service data set.

   Select Parameters on the Data Model pane and click the Create New Parameter button. Enter the following:

   • Identifier — Enter an internal identifier for the parameter (for example, Quote).

   • Data Type — Select String.

   • Default Value — If desired, enter a default for the parameter (for example, ORCL).

   • Parameter Type — Select Text

5. In the Text Setting region, enter the following:

   • Display label — Enter the label you want displayed for your parameter (for example: Stock Symbol).

   • Text Field Size — Enter the size for the text entry field in characters.

     Figure 3-20 Creating the Parameter

     
     

6. Select the options you want to apply:

   • Text field contains comma-separated values — Select this option to enable the user to enter multiple comma-delimited values for this parameter.

   • Refresh other parameters on change — Performs a partial page refresh to refresh any other parameters whose values are dependent on the value of this one.

7. Return to your Web service data set and add the parameter.

   • Click the data set name Stock Quote. Click Add Parameter. The Quote parameter you specified is now available from the list.

   • Click the Edit Selected Data Set button.

   • In the Edit Data Set dialog, click Add Parameter. The Quote parameter displays, as shown in Figure 3-21.

     Figure 3-21 Adding the Parameter to Web Service Data Set

     
     

   • Click OK to close the data set.

8. Click Save.

9. To view the results XML, select Get XML Output.

10. Enter a valid value for your Stock Symbol parameter, select the number of rows to return, and click the Run button. Figure 3-22 shows the data returned from the example.

    Figure 3-22 Data Returned from Stock Quote Example

    
    

3.10.2 Adding a Complex Web Service

A complex Web service type internally uses soapRequest / soapEnvelope to pass the parameter values to the destination host.

To use a complex Web service as a data source, select Complex Type equal True, then enter the WSDL URL. After loading and analyzing the WSDL URL, the Data Model Editor displays the available Web services and operations. For each selected operation, the Data Model Editor displays the structure of the required input parameters. By choosing Show Optional Parameters, you can see all optional parameters as well.

If you are not familiar with the available methods and parameters in the Web service, open the WSDL URL in a browser to view them.

To add a complex Web service as a data source:

1. Enter the Data Set information:

   • Enter a Name for the Data Set and select Web Service as the Type.

   • Select True for Complex Type.

   • Select a security header:

     • Disabled — Does not insert a security header.

     • 2002 — Enables the "WS-Security" Username Token with the 2002 namespace:

       http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd

     • 2004 — Enables the "WS-Security" Username Token with the 2004 namespace:

       http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText

   • Username and Password — Enter the username and password for the Web service, if required.

   • If desired, enter a Time Out period in seconds. If the BI Publisher server cannot establish a connection to the Web service, then connection attempt times out after the specified time out period has elapsed.

   • Enter a WSDL URL. When you enter the WSDL, the Web Service list populates with the available Web services from the WSDL.

   • Choose a Web Service from the list. When you choose a Web service from the list, the Method list populates with the available methods.

   • Select the Method. When you select the method, the Parameters display. If you want to see optional parameters as well, then select Show Optional Parameters.

   • Response Data XPath — If the start of the XML data for the report is deeply embedded in the response XML generated by the Web service request, then use this field to specify the path to the data to use in the BI Publisher report.

2. Define the parameter to make it available to the Web service data set.

   Select Parameters on the Report definition pane and click New to create a parameter. Enter the following:

   • Name — Enter an internal identifier for the parameter.

   • Data Type — Select the appropriate data type for the parameter.

   • Default Value — If desired, enter a default value for the parameter.

   • Parameter Type — Select the appropriate parameter type.

   • Display label — Enter the label you want displayed for your parameter.

   • Text Field Size — Enter the size for the text entry field in characters.

3. Return to the Web service data set and add the parameter.

   • Select the Web service data set and then click

     Edit Selected Data Set to launch the Edit Data Set dialog.

   • In the entry field for the Parameter, enter the following syntax: ${Parameter_Name} where Parameter_Name is the value you entered for Name when you defined the parameter to BI Publisher. Figure 3-23 shows an example of entering the parameters.

     Figure 3-23 Entering Parameters for Complex Web Service

     
     

4. To test the Web service, see Section 3.14, "Testing Data Models and Generating Sample Data."

3.10.3 Additional Information on Web Service Data Sets

There is no metadata available from Web service data sets, therefore grouping and linking are not supported.

3.11.1 Additional Information on File Data Sets

There is no metadata available from XML file data sets, therefore grouping and linking are not supported.

3.12.1 How the Data Is Returned

When you execute the query, if the CLOB column contains well-formed XML, and you select the XML data type, the data engine returns the XML data, structured within the CLOB column tag name.

Example output when data type is XML

Note the <DESCRIPTION> element contains the XML data stored in the CLOB column, as shown in Figure 3-27.

Figure 3-27 Example Data Structure When the Data Type is XML

Example output when data type is CLOB

If you select to return the data as the CLOB data type, the returned data is structured as shown in Figure 3-28.

Figure 3-28 Example Data Structure When Data Type Is CLOB

3.12.2 Handling XHTML Data Stored in a CLOB Column

BI Publisher can retrieve data stored in the form of XHTML documents stored in a database CLOB column and render the markup in the generated report. To enable the BI Publisher report rendering engine to handle the markup tags, you must wrap the XHTML data in a CDATA section within the XML report data that is passed by the data engine.

It is recommended that you store the data in the database wrapped with the CDATA section. You can then use a simple select statement to extract the data. If the data is not wrapped in the CDATA section, then you must include in your SQL statement instructions to wrap it.

The following sections describe how to extract XHTML data in each case:

• Retrieving XHTML Data Wrapped in CDATA

• Wrapping the XHTML Data in CDATA in the Query

To display the markup in a report, you must use the syntax described in "Rendering HTML Formatted Data in a Report" in the Oracle Fusion Middleware Report Designer's Guide for Oracle Business Intelligence Publisher. This section also describes the supported HTML formats. Rendering the HTML markup in a report is supported for RTF templates only.

3.12.2.1 Retrieving XHTML Data Wrapped in CDATA

Assume you have the following data stored in a database column called "CLOB_DATA":

<![CDATA[<font style="font-style: italic; font-weight: bold;" size="3"><ahref="http://www.oracle.com">oracle</a></font> <br/><font size="6"><a href="www.oracle.com">www.oracle.com</a></font><br/><br/>]]>

Retrieve the column data using a simple SQL statement, for example:

select CLOB_DATA as "RTECODE"  from MYTABLE 

In the data model editor, set the data type of the RTECODE column to XML, as shown in Figure 3-29.

Figure 3-29 Set Data Type to XML

<font style="font-style: italic; font-weight: bold;" size="3"><ahref="http://www.oracle.com">oracle</a></font> <br/><font size="6"><a href="www.oracle.com">www.oracle.com</a></font><br/><br/>

select '<![CDATA' || '['|| CLOB_DATA || ']' || ']>' as "RTECODE"from MYTABLE

3.13.1 Additional Information on Data Sets Created from HTTP XML Feed

There is no metadata available from HTTP XML feed data sets, therefore grouping and linking are not supported.

3.15.1 Adding the User System Variables as Elements

To add the user information to the data model, you can define the variables as parameters and then define the parameter value as an element in your data model. Or, you can simply add the variables as parameters then reference the parameter values in your report.

The following query:

select
:xdo_user_name as USER_ID,
:xdo_user_roles as USER_ROLES,
:xdo_user_report_oracle_lang as REPORT_LANGUAGE,
:xdo_user_report_locale as REPORT_LOCALE,
:xdo_user_ui_oracle_lang as UI_LANGUAGE,
:xdo_user_ui_locale as UI_LOCALE
from dual

returns the following results:

<?xml version="1.0" encoding="UTF-8"?>
<! - Generated by Oracle BI Publisher - >
<DATA_DS>
<G_1>
<USER_ROLES>XMLP_TEMPLATE_DESIGNER, XMLP_DEVELOPER, XMLP_ANALYZER_EXCEL, XMLP_ADMIN, XMLP_ANALYZER_ONLINE, XMLP_SCHEDULER </USER_ROLES>
<REPORT_LANGUAGE>US</REPORT_LANGUAGE>
<REPORT_LOCALE>en_US</REPORT_LOCALE>
<UI_LANGUAGE>US</UI_LANGUAGE>
<UI_LOCALE>en_US</UI_LOCALE>
<USER_ID>administrator</USER_ID>
</G_1>
</DATA_DS>

3.15.2 Sample Use Case: Limit the Returned Data Set by User ID

The following example limits the data returned by the user ID:

selectthroughEMPLOYEES.LAST_NAME as LAST_NAME,
  EMPLOYEES.PHONE_NUMBER as PHONE_NUMBER,
  EMPLOYEES.HIRE_DATE as HIRE_DATE,
 :xdo_user_name as USERID 
from   HR.EMPLOYEES EMPLOYEES
where lower(EMPLOYEES.LAST_NAME) = :xdo_user_name

Notice the use of the lower() function, the xdo_user_name is always be in lowercase format. BI Publisher does not have a USERID so you must use the user name and either use it directly in the query; or alternatively you could query against a lookup table to find a user id.

xdo_<attribute name>

xdo_memberof
xdo_SAMACCOUNTNAME
xdo_primaryGroupID
xdo_mail

SELECT
:xdo_user_name AS USER_NAME ,
:xdo_user_roles AS USER_ROLES,
:xdo_user_ui_oracle_lang AS USER_UI_LANG,
:xdo_user_report_oracle_lang AS USER_REPORT_LANG,
:xdo_user_ui_locale AS USER_UI_LOCALE,
:xdo_user_report_locale AS USER_REPORT_LOCALE,
:xdo_SAMACCOUNTNAME AS SAMACCOUNTNAME,
:xdo_memberof as MEMBER_OF,
:xdo_primaryGroupID as PRIMARY_GROUP_ID,
:xdo_mail as MAIL
FROM DUAL