Creating Data Sets

Entering SQL Queries

To enter a SQL query:

1. Click New Data Set and then click SQL Query. The Create Data Set - SQL dialog opens, as shown in Figure 14-3.

   Figure 14-3 New Data Set - SQL Query Dialog

   
   

2. Enter a name for the data set.

3. The data source defaults to the default data source that you selected on the Properties page. If you are not using the default data source for this data set, select the Data Source from the list.

   You can also use your private data source connections as data sources for SQL query data sets. See Managing Private Data Sources for information about private data source connections.

4. The SQL type defaults to Standard SQL used for normal SELECT statements interpreted to understand database schema. See Creating Non-Standard SQL Data Sets for information on using other types of SQL.

5. Enter the SQL query or click Query Builder to launch the Query Builder page. See Using the SQL Query Builder for more information about the Query Builder utility.

6. If you are using Flexfields, bind variables, or other special processing in your query, edit the SQL returned by the Query Builder to include the required statements.

   
   

   Note: If you include lexical references for text that you embed in a SELECT statement, then you must substitute values to get a valid SQL statement.

   
   

7. After entering the query, click OK to save. For Standard SQL queries the data model editor validates the query.

   If your query includes a bind variable, you are prompted to create the bind parameter. Click OK to have the data model editor create the bind parameter.

   See Chapter 16, "Adding Parameters and Lists of Values" for more information on editing parameters.

Creating Non-Standard SQL Data Sets

In addition to creating data sets using basic SQL commands, you can create data sets using more complex commands as follows:

Procedure Call

Use this query type to call a database procedure. For example, for PL/SQL for Oracle, statements start with BEGIN. Note that when you use this SQL data type, no metadata is displayed on the data model structure tab, therefore you cannot modify the data structure or data fields. To construct your SQL with a procedure call either enter the code directly in the text box or copy and paste from another SQL editor; the Query Builder cannot be used to modify or build these types of queries.

Figure 14-4 Procedure Call SQL Type

Non-standard SQL

Use this query type to issue SQL statements that can include the following:

• Cursor statements that return nested results sets

  For example:

    Ex:SELECT TO_CHAR(sysdate,'MM-DD-YYYY') CURRENT_DATE ,
    CURSOR
     (SELECT d.order_id department_id,
             d.order_mode department_name ,
             CURSOR
             (SELECT e.cust_first_name first_name,
               e.cust_last_name last_name,
               e.customer_id employee_id,
               e.date_of_birth hire_date
             FROM customers e
             WHERE e.customer_id IN (101,102)
             ) emp_cur
      FROM orders d
      WHERE d.customer_id IN (101,102)
    ) DEPT_CUR FROM dual
  

• Functions returning REF cursors

  For example:

  create or replace PACKAGE REF_CURSOR_TEST AS
    TYPE refcursor IS REF CURSOR;
    pCountry  VARCHAR2(10);
    pState   VARCHAR2(20);
    FUNCTION GET( pCountry IN VARCHAR2, pState   IN VARCHAR2) RETURN  REF_CURSOR_TEST.refcursor;
    END;
  
  

    create or replace PACKAGE BODY REF_CURSOR_TEST AS
    FUNCTION GET(
     pCountry  IN VARCHAR2,
     pState    IN VARCHAR2)
    RETURN REF_CURSOR_TEST.refcursor
    IS
    l_cursor REF_CURSOR_TEST.refcursor;
    BEGIN
       IF ( pCountry = 'US' ) THEN
         OPEN l_cursor FOR 
         SELECT TO_CHAR(sysdate,'MM-DD-YYYY') CURRENT_DATE ,
             d.order_id department_id,
             d.order_mode department_name
         FROM orders d
         WHERE d.customer_id IN (101,102);
       ELSE
         OPEN l_cursor FOR 
         SELECT * FROM EMPLOYEES;    
       END IF;   
       RETURN l_cursor;
     END GET;
    END REF_CURSOR_TEST;
  
  

  To use REF cursor in BI Publisher:

  create SQL dataset with query as  SELECT REF_CURSOR_TEST.GET(:PCNTRY,:PSTATE) AS CURDATA FROM DUAL
  

• Anonymous blocks/Stored procedures

  BI Publisher supports executing PL/SQL anonymous blocks. You can write DML or DDL commands, populate temp tables, or perform calculations in the PL/SQL block and return the result set. BI Publisher uses callable statements to execute anonymous blocks.

  Note the following requirements:

  • The PL/SQL block must return a result set of type REF cursor

  • You must declare the out variable with name "xdo_cursor"; if not, the first bind variable is treated as an out variable type and binds with REF cursor

  • Declare the data model parameter with name "xdo_cursor". This name is reserved for out variable type for procedure/anonymous blocks.

  Example:

  DECLARE
         type refcursor is REF CURSOR;
         xdo_cursor refcursor;
         empno number;
      BEGIN
        INSERT into procTest values (:P1); 
        UPDATE EMPLOYEES SET COMMISSION_PCT = .28
         WHERE EMPLOYEE_ID = :P2;
        OPEN :xdo_cursor FOR 
         SELECT * 
         FROM procTest d, EMPLOYEES E
         WHERE E.EMPLOYEE_ID = :P2;
         COMMIT;
      END;
  

• Conditional queries can be executed if you use an if-else expression. You can define multiple SQL queries in a single data set, but only one query will execute at run time depending on the expression value. The expression validates and returns a boolean value, which if true, executes that section of the SQL query.

  Note the following limitations:

  • The following syntax is supported to evaluate expressions: $if{, $elseif{, $else{

  • The expression must return true, false

  • Only the following operators are supported:

    == <= >= < >

   Example:
     create sql dataset with following query
     $if{ (:P_MODE == PRODUCT) }$
                  SELECT PRODUCT_ID
                  ,PRODUCT_NAME
                  ,CATEGORY_ID
                  ,SUPPLIER_ID
                  ,PRODUCT_STATUS
                  ,LIST_PRICE
                  FROM PRODUCT_INFORMATION 
                  WHERE ROWNUM < 5
     $elsif{(:P_MODE == ORDER )}$
                  SELECT ORDER_ID
                    ,ORDER_DATE
                    ,ORDER_MODE
                    ,CUSTOMER_ID
                    ,ORDER_TOTAL
                    ,SALES_REP_ID
                  FROM ORDERS
                  WHERE ROWNUM < 5
     $else{ 
                 SELECT PRODUCT_ID
                 , WAREHOUSE_ID
                 ,QUANTITY_ON_HAND
                 FROM INVENTORIES
                 WHERE ROWNUM < 5
                 }$
     $endif$
  

When your data set is created using non-standard SQL statements, no metadata is displayed on the data model structure tab, therefore you cannot modify the data structure or data fields. The Query Builder cannot be used to modify or build these types of queries.

Figure 14-5 shows a sample Non-standard SQL query.

Figure 14-5 Non-standard SQL Query

Using the SQL Query Builder

Use the Query Builder to build SQL queries without coding. The Query Builder enables you to search and filter database objects, select objects and columns, create relationships between objects, and view formatted query results with minimal SQL knowledge.

This section describes how to use the Query Builder and includes the following topics:

• Overview of the Query Builder

• Understanding the Query Builder Process

• Using the Object Selection Pane

• Selecting a Schema

• Searching and Filtering Objects

• Selecting Objects

• Supported Column Types

• Adding Objects to the Design Pane

• Resizing the Design and Results Pane

• Removing or Hiding Objects in the Design Pane

• Specifying Query Conditions

• Creating Relationships Between Objects

• Saving a Query

• Editing a Saved Query

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 14-7 shows the Conditions tab.

Figure 14-7 Conditions Tab

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

Table 14-1 Attributes Available on the Conditions Tab

Condition Attribute	Description
Up and Down Arrows	Controls the display order of the columns in the resulting query.
Column	Displays the column name.
Alias	Specify an optional column alias. An alias is an alternative column name. Aliases are used to make a column name more descriptive, to shorten the column name, or prevent possible ambiguous references. Note that multibyte characters are not supported in the alias name.
Object	Displays the object name.
Condition	The condition modifies the query's WHERE clause. When specifying a column condition, you must include the appropriate operator and operand. All standard SQL conditions are supported. For example: >=10 ='VA' IN (SELECT dept_no FROM dept) BETWEEN SYSDATE AND SYSDATE + 15
Sort Type	Select ASC (Ascending) or DESC (Descending).
Sort Order	Enter a number (1, 2, 3, and so on) to specify the order in which selected columns should display.
Show	Select this check box to include the column in your query results. You do not need to select Show to add a column to the query for filtering only. For example, to create following query: SELECT ename FROM emp WHERE deptno = 10 To create this query in Query Builder: From the Object list, select EMP. In the Design Pane, select ename and deptno. For the deptno column, in Condition enter =10 and clear the Show check box.
Function	Available argument functions include: Number columns — COUNT, COUNT DISTINCT, AVG, MAXIMUM,. MINIMUM, SUM VARCHAR2, CHAR columns — COUNT, COUNT DISTINCT, INITCAP, LENGTH, LOWER, LTRIM, RTRIM, TRIM, UPPER DATE, TIMESTAMP columns- COUNT, COUNT DISTINCT
Group By	Specify columns to be used for grouping when an aggregate function is used. Only applicable for columns included in output.
Delete	Deselect the column, excluding it from the query.

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

To view the underlying SQL:

1. Select the SQL tab.

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.

Figure 14-9 Query Displayed in SQL Query Box

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 14-10.

  Figure 14-10 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 14-11.

     Figure 14-11 Editing the Generated SQL Query

     
     

  2. When you click Save, the data model editor prompts you to create the parameter that you entered with the bind variable syntax, as shown in Figure 14-12.

     Figure 14-12 Add Parameter Dialog

     
     

  3. Select the parameter, and click OK to enable the data model editor create the parameter entry for you. See Chapter 16, "Adding Parameters and Lists of Values" for more information on defining parameter properties.

Adding Lexical References to SQL Queries

You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, or HAVING. Use a lexical reference when you want the parameter to replace multiple values at runtime. Lexical references are supported in queries against Oracle applications only.

Create a lexical reference in the SQL query using the following syntax:

&parametername

To define a lexical parameter:

1. Before creating your query, define a parameter in the PL/SQL default package for each lexical reference in the query. The data engine uses these values to replace the lexical parameters.

2. In the data model editor, on the Properties page, specify the Oracle DB Default Package.

3. In the data model editor, create a Before Data event trigger to call the PL/SQL package. See Adding Before Data and After Data Triggers for more information about procedures.

4. Create your SQL query containing lexical references.

5. When you click OK to close your SQL query, you are prompted to enter the parameter.

For example, create a package called employee. In the employee package, define a parameter called where_clause:

Package employee
AS
 where_clause varchar2(1000);
  .....
 
Package body employee
 AS
 .....
where_clause := 'where DEPARTMENT_ID=10';
.....

Reference the lexical parameter in the SQL query where you want the parameter to be replaced by the code defined in the package. For example:

select     "EMPLOYEES"."EMPLOYEE_ID" as "EMPLOYEE_ID",
         "EMPLOYEES"."FIRST_NAME" as "FIRST_NAME",
         "EMPLOYEES"."LAST_NAME" as "LAST_NAME",
         "EMPLOYEES"."SALARY" as "SALARY",
 from   "OE"."EMPLOYEES" "EMPLOYEES"
 &where_clause

When you click OK on the Create SQL Data Set dialog box, the lexical reference dialog box prompts you to enter a value for lexical references you entered in the SQL query, as shown in Figure 14-13. Enter the value of the lexical reference as it is defined in the PL/SQL package.

Figure 14-13 Lexical Reference Dialog

At runtime, the data engine replaces &where_clause with the contents of where_clause defined in the package.

Defining SQL Queries Against the Oracle BI Server

When you launch the Query Builder against the Oracle BI server, the Query Builder displays the subject areas from the catalog. You can drag the subject areas to the Query Builder workspace to display the columns. Select the columns to include in your data model.

Keep the following points in mind when creating a data set against the Oracle BI server:

• When you create a SQL query against the Oracle BI server using the SQL Data Editor or the Query Builder, logical SQL is generated, not physical SQL like other database sources.

• Hierarchical columns are not supported. The highest level is always returned.

• Within a subject area, the join conditions between tables are already created; it is therefore not necessary to create joins in the Query Builder. The Query Builder does not expose the primary key.

  It is possible to link data sets using the data model editor's Create Link function. See Creating Element-Level Links. For data sets created from the BI Server, there is a limit of two element-level links for a single data model.

• In the Query Builder, the functions Sort Order and Group By shown on the Conditions tab are not supported for queries against the Oracle BI server. If you enter a Sort Order or select the Group By check box, the Query Builder will construct the SQL, and write it to the BI Publisher SQL Query text box, but when you attempt to close the Data Set dialog, the query will fail validation.

  To apply grouping to the data retrieved by the SQL query, you can use the data model editor's Group by function instead. See Creating Subgroups.

• If you pass parameters to the Oracle BI server and you choose Null Value Passed for Can Select All, you must ensure that you handle the null value in your query.

To create a SQL query against the Oracle BI server:

1. In the data model editor, click New Data Set and then click SQL Query.

2. Enter a name for the data set.

3. From the Data Source drop-down list, select the Oracle BI server connection (usually shown as Oracle BI EE).

4. Click Query Builder to launch the Query Builder page. See Using the SQL Query Builder for more information about the Query Builder utility.

   You can also enter the SQL syntax manually in the SQL Query text box, however you must use the logical SQL syntax used by the Oracle BI server.

5. From the Catalog drop-down list, select a subject area as shown in Figure 14-14. The list displays the subject areas defined in the Oracle BI server.

   Figure 14-14 Selecting a Subject Area in the Query Builder

   
   

6. Select tables and columns for the query.

7. Click Save.

8. Click OK to return to the data model editor. The SQL that is generated is logical SQL that follows a star schema. It is not physical SQL.

9. Save your changes to the data model.

Creating a Data Set Using a MDX Query

To create a data set using a MDX query:

1. On the toolbar, click New Data Set and then select MDX Query. The New Data Set - MDX Query dialog launches as shown in Figure 14-15.

   Figure 14-15 New Data Set - MDX Query Dialog

   
   

2. Enter a name for the data set.

3. Select the data source for the data set. Only the data sources defined as OLAP connections are displayed in the list.

   Any private OLAP data source connections that you created will also be available in the Data Source drop-down list. For more information on creating private data source connections, see Managing Private Data Sources.

4. Enter the MDX query or click Query Builder. For more information on using MDX Query Builder, see Using MDX Query Builder.

5. Click OK to save. The data model editor validates the query.

For more information on writing MDX queries, see "Writing MDX Queries" in Oracle Essbase Database Administrators Guide.

Understanding the MDX Query Builder Process

To use MDX Query Builder to build a MDX query:

1. On the toolbar, click New Data Set and then select MDX Query to launch the New Data Set - MDX Query dialog.

2. Enter a name for the data set.

3. Select a data source.

4. Launch MDX Query Builder.

5. Select an Essbase cube for the query.

6. Select dimensions and measures by dragging and dropping them to the Columns, Rows, Slicer/POV, and the Pages axes.

7. (Optional) Use actions to modify the query.

8. (Optional) Apply filters.

9. Set the query options and save the query.

Using the Select Cube Dialog

In the Select Cube dialog, select the Essbase cube that you want to use to build the MDX query.

The MDX data source connection that you selected previously drives which Essbase cubes are available for selection.

Selecting Dimensions and Measures

You build MDX queries by selecting dimensions for the Columns, Rows, Slicer/POV, and Pages axes.

Account dimension members are listed individually by member name. All other dimension members are represented by generation name as shown in Figure 14-16.

You can drag dimension generations and individual measures from the Account dimension to the Columns, Rows, Slicer/POV, and Pages axes.

Figure 14-16 Dimensions as Displayed in MDX Query Builder

Build the query by dragging dimension members or measures from the Dimensions panel to one of the following axes areas:

• Columns— Axis (0) of the query

• Rows — Axis (1) of the query

• Slicer/POV— The slicer axis enables you to limit a query to only a specific slice of the Essbase cube. This represents the optional WHERE clause of a query.

• Axis — Axis (2) of the query

You can nest dimension members in the Columns and Rows axes, but you can only add a single dimension to the Slider/POV axis.

Performing MDX Query Actions

The MDX Query Builder toolbar contains the following buttons for modifying the MDX query:

• Click Swap Rows and Columns to flip dimensions between columns and rows.

• Click Actions to display the following menu items for selection:

  • Select Cube — Selects a different Essbase cube for the query.

  • Set Alias Table — Selects the alias table used for dimension display names. Alias names are used for display only and are not used in the query.

  • Auto Refresh — Displays the results as dimension members are placed in the Columns, Rows, Slicer/POV, and Pages axes and automatically refreshes the MDX query syntax.

  • Clear Results — Clears the results and removes member selections from all of the axes and any filters added to the query.

  • Show Empty Columns — Displays columns that do not contain data.

  • Show Empty Rows — Displays rows that do not contain data.

  • Show Query — Displays the MDX query syntax resulting from how the dimensions are placed in the Columns, Rows, Slicer/POV, and Pages axes.

Applying MDX Query Filters

You can create filters for dimensions on the Columns, Rows, and Pages axes in MDX Query Builder to further streamline your MDX query.

To create a filter, click the down-arrow button to the right of a dimension in the Columns, Rows, or Pages axes to display it in the Filters area. You create the filter by selecting the desired dimension member as shown in Figure 14-17.

Figure 14-17 MDX Query Builder Filters

You can create multiple filters for a query, but you can only create one filter for each Columns, Rows, or Pages axis.

Selecting MDX Query Options and Saving MDX Queries

Once you have built the query, click Save to display the Options dialog as shown in Figure 14-18.

Figure 14-18 Options Dialog

Use the Options dialog to select the dimension properties to include in the query for each of the dimensions in the Columns, Rows, and Pages axes. By default, none of the properties are selected.

The dimension properties are as follows:

• Member Alias — Dimension member alias names as listed in the Essbase outline.

• Ancestor Names — Ancestor dimension names as listed in the Essbase outline.

• Level Number — Dimension level numbers as listed in the Essbase outline.

• Gen Number — Generation number of the dimensions as listed in the Essbase outline.

For example, if you select the Member Alias and Level Number properties for Columns, the MDX query results are as follows:

SELECT
NON EMPTY Hierarchize([Market].Generations(2).Members)
PROPERTIES MEMBER_ALIAS,LEVEL_NUMBER ON Axis(0),
NON EMPTY CROSSJOIN(Hierarchize([Product].Generations(3).Members),
{[Accounts].[Margin],[Accounts].[Sales],[Accounts].[Total_Expenses]})ON Axis(1),
NON EMPTY [Year].Generations(3).Members ON Axis(2) FROM Demo.Basic

For more information on Essbase dimension properties, see "Querying for Properties" in Oracle Essbase Database Administrator's Guide.

After you select options for the MDX query, click OK to return to the New Data Set - MDX Query dialog and review the MDX query output as shown in Figure 14-19.

Figure 14-19 MDX Query Output

Click OK to return to the data model editor, and save your changes.

Additional Notes on Oracle BI Analysis Data Sets

Parameters and list of values are inherited from the BI analysis and they display at run time.

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 15, "Structuring Data."

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 15, "Structuring Data."

Creating a Data Set Using a Web Service

Web service data sources can be set up in the following ways:

• On the Administration page

  Connections to Web service data sources can be set up on the Administration page and then used in multiple data models. For more information, see "Setting Up a Connection to a Web Service."

• As a private data source

  You can also set up a private connection accessible only to you. See Managing Private Data Sources for information about private data source connections.

You must set up the connection before you create the data model.

BI Publisher supports data sets that use simple and complex Web service data sources to return valid XML data:

• Creating a Data Set Using a Simple Web Service

• Creating a Data Set Using a Complex Web Service

Additional Information on Web Service Data Sets

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

About Supported XML Files

The following are guidelines for the support of XML files as a data set type in BI Publisher:

• The XML files that you use as input to the BI Publisher data engine must be UTF-8 encoded.

• Do not use the following characters in XML tag names: ~, !, #, $, %, ^, &, *, +, `, |, :, \", \\, <, >, ?, ,, /. If your data source file contains any of these characters, use the data model editor Structure tab to change the tag names to an acceptable one.

• The XML file must be valid. Oracle provides many utilities and methods for validating XML files.

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

Using a XML File Stored in a File Directory Data Source

To create a data set using a XML file from a file directory data source:

1. On the toolbar, click New Data Set and select XML File. The New Data Set - XML File dialog launches, as shown in Figure 14-27.

   Figure 14-27 New Data Set - XML File Dialog

   
   

2. Enter a name for the data set.

3. Click Shared to enable the Data Source list. This is the default selected option.

4. Select the Data Source where the XML file resides. The list is populated from the configured File Data Source connections.

5. To the right of File Name, click Browse to connect to the data source and browse the available directories. Select the file.

6. Click OK.

7. (Required) Save sample data to the data model. See Testing Data Models and Generating Sample Data.

Uploading a XML File Stored Locally

To create a data set using a XML file stored locally:

1. On the toolbar, click New Data Set and select XML File. The New Data Set - XML File dialog launches, as shown in Figure 14-28.

   Figure 14-28 New Data Set - XML File Dialog

   
   

2. Enter a name for this data set.

3. Select Local to enable the Upload button.

4. Click Upload to browse for and upload the XML 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. Click Upload.

6. Click OK.

7. (Required) Save sample data to the data model. See Testing Data Models and Generating Sample Data.

Refreshing and Deleting an Uploaded XML File

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

Figure 14-29 Attachments Region of the Properties Pane

See Setting Data Model Properties for more information about the Properties pane.

To refresh the local file in the data model:

1. In the component pane, click Data Model 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. In the component pane, click Data Model 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.

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 can contain a single sheet or multiple sheets.

• Each worksheet can 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 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 "Formatting Numbers, Dates, and Currencies" in Oracle Fusion Middleware Report Designer's Guide for Oracle Business Intelligence Publisher.

• Single value parameters are supported, but multiple value parameters are not supported.

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 14-30 shows how to use the Define Name command in Microsoft Excel 2007 to name a table "BIP_Salaries".

Figure 14-30 Using the Define Name Command in Microsoft Excel

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 16, "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 New Data Set - Microsoft Excel File 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 Microsoft Excel File resides.

5. To the right of the File Name field, 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 14-31.

   Figure 14-31 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. Only single value parameters are supported. See Chapter 16, "Adding Parameters and Lists of Values."

8. Click OK.

9. Link the data from this query to the data from other queries or modify the output structure. For more information on linking queries, see Chapter 15, "Structuring Data."

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 16, "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 14-32.

   Figure 14-32 Creating a Data Set from an Excel Spreadsheet

   
   

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. Only single value parameters are supported. See Chapter 16, "Adding Parameters and Lists of Values."

7. Click OK.

8. Link the data from this query to the data from other queries or modify the output structure. For more information on linking queries, see Chapter 15, "Structuring Data."

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 14-33.

Figure 14-33 Attachments Region of the Properties Pane

See 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.

About Supported CSV Files

The following are guidelines for the support of CSV files as a data set type in BI Publisher:

• The following CSV file delimiters are supported: Comma, Pipe, Semicolon, and Tab.

• If your CSV file contains headers, the header names are used as the XML tag names. The following characters are not supported in XML tag names: ~, !, #, $, %, ^, &, *, +, `, |, :, \", \\, <, >, ?, ,, /. If your data source file contains any of these characters in a header name, use the data model editor Structure tab to edit the tag names.

• CSV data sets support editing the data type assigned by the data model editor. See Editing the Data Type for more information. If you update the data type for an element in the data set, you must ensure that the data in the file is compliant with the data type that you selected.

• The CSV files that you use as input to the BI Publisher data engine must be UTF-8 encoded and cannot contain empty column headers.

• Group breaks, data links, expression and group-level functions are not supported.

• Data fields in CSV files must be in the canonical ISO date format for mapped date elements (for example, 2012-01-01T10:30:00-07:00), and ######.## for mapped number elements.

• No data validation is provided for CSV files.

Using a CSV File Stored in a File Directory Data Source

To create a data set using a CSV file from a file directory data source:

1. On the data model editor toolbar, click New Data Set and select CSV File. The New Data Set - CSV File dialog launches, as shown in Figure 14-34.

   Figure 14-34 New Data Set - CSV File Dialog

   
   

2. Enter a name for this data set.

3. Click Shared to enable the Data Source list.

4. Select the Data Source where the CSV file resides. The list is populated from the configured File Data Source connections.

5. Click Browse to connect to the data source and browse the available directories. Select the file.

6. (Optional) Select The first row a column header to specify if the first row in the file contains column names. If you do not select this option, the columns are assigned a generic name, for example, Column1, Column2. The XML tag names and display names assigned can be edited in the data model editor Structure tab.

7. Select the CSV delimiter used in the file. The default selection is Comma (,).

8. Click OK.

Uploading a CSV File Stored Locally

To create a data set using a CSV file stored locally:

1. On the toolbar, click New Data Set and select CSV File. The New Data Set - CSV File dialog launches, as shown in Figure 14-35.

   Figure 14-35 New Data Set - CSV File Dialog

   
   

2. Enter a name for this data set.

3. Select Local to enable the Upload button.

4. Click Upload to browse for and upload the CSV 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. (Optional) Select The first row a column header to specify if the first row in the file contains column names. If you do not select this option, the columns are assigned a generic name, for example, Column1, Column2. The XML tag names and display names assigned can be edited in the data model editor Structure tab.

6. Select the CSV Delimiter used in the file. The default selection is Comma (,).

7. Click OK.

Refreshing and Deleting an Uploaded CSV File

After uploading the file, it is displayed on the Properties pane of the data model under the Attachments region, as shown in Figure 14-36.

Figure 14-36 Attachments Region of the Properties Pane

See Setting Data Model Properties for more information about the Properties pane.

To refresh the local file in the data model:

1. In the component pane, click Data Model 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. In the component pane, click Data Model 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.

Creating a Data Set from an HTTP XML Data Set

HTTP (XML Feed) data sources can be set up by two different methods:

• On the Administration page

  Connections to HTTP data sources can be set up on the Administration page and then used in multiple data models. For more information, see "Setting Up a Connection to a HTTP XML Feed."

• As a private data source

  You can also set up a private connection accessible only to you. See Managing Private Data Sources for information about private data source connections.

To create a data set from an HTTP XML feed:

1. On the toolbar, click New Data Set and select HTTP (XML Feed). The New Data Set - HTTP (XML Feed) dialog launches, as shown in Figure 14-37.

   Figure 14-37 New Data Set - HTTP (XML Feed) Dialog

   
   

2. Enter a name for this data set.

3. Select a data source.

4. Enter the URL Suffix for the source of the RSS or XML feed.

5. Select the Method: GET.

6. To add a parameter, 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 16, "Adding Parameters and Lists of Values."

7. Click OK to close the data set dialog.

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 14-40.

Figure 14-40 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 14-41.

Figure 14-41 Example Data Structure When Data Type Is CLOB

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 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.

Retrieving XHTML Data Wrapped in CDATA

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

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

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 14-42.

Figure 14-42 Set Data Type to XML

<p><font style="font-style: italic; font-weight: bold;" size="3">
<a href="http://www.oracle.com">oracle</a></font> </p>
<p><font size="6"><a href="http://docs.oracle.com/">Oracle Documentation</a>
</font></p>

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

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>

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

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

select EMPLOYEES.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