Topics:
The Data Model diagram helps you to quickly and easily define data sets, break groups, and totals for a report based on multiple data sets.
If you don't link the data sets (or queries), the data engine produces a multipart unrelated query data set.
For example, in the data model, image shown below, one query selects products and another selects customers. There's no relationship between the products and customers.
The result is shown in the data structure as depicted in the following image.
Data fetched for one part of a data set or query can be determined by the data fetched for another part. The result is often called a master/detail, or parent/child relationship that's defined with a data link between two data sets or queries.
When you run a master/detail data model, each row of the master (or parent) query executes a query against the detail (or child) to retrieve only matching rows.
In the example, image below, two data sets are linked by the element Customer ID. The Orders data set is a child of the Customers data set.
The example produces the data structure shown in the following image.
Certain guidelines are recommended for building data models.
Reduce the number of data sets or queries in your data model as much as possible. In general, the fewer data sets and queries you have, the faster your data model will run. While multi-query data models are often easier to understand, single-query data models tend to execute more quickly. It is important to understand that in parent-child queries, for every parent, the child query is executed.
You should only use multi-query data models in the following scenarios:
To perform functions that the query type, such as a SQL query, does not support directly.
To support complex views, for example, distributed queries or GROUP BY queries.
To simulate a view when you do not have or want to use a view.
The data model editor enables you to combine data from multiple data sets into a single XML data structure.
Data sets from multiple data sources can be merged either as sequential XML or at line-level to create a single combined hierarchical XML. Using the data model editor you can easily combine data from the following data set types: SQL query, OLAP (MDX query), LDAP, and Microsoft Excel.
The data model editor is designed with a component pane on the left and work pane on the right. Selecting a component on the left pane launches the appropriate fields for the component in the work area.
The data model editor supports the following
Group data - Create groups to organize the columns in your report. Groups can do two things: separate a query's data into sets, and filter a query's data.
When you create a query, the data engine creates a group that contains the columns selected by the query; you can create groups to modify the hierarchy of the data appearing in a data model. Groups are used primarily when you want to treat some columns differently than others. For example, you create groups to produce subtotals or create breaks.
Link data - Define master-detail links between data sets to group data at multiple levels.
Aggregate data - Create group level totals and subtotals.
Transform data - Modify source data to conform to business terms and reporting requirements.
Create calculations - Compute data values that are required for your report that are not available in the underlying data sources.
The data model editor provides functions at the element level, the group level, and the global level. Note that not all data set types support all functions. See the Important Notes section that accompanies your data set type for limitations. The figure below highlights some of the features and actions available in the data model editor.
By default, the data sets that you created are shown in the Diagram View as separate objects.
The data set structure builder has three views:
Diagram View - The Diagram View displays data sets and enables graphically creating links and filters, adding elements based on expressions, adding aggregate functions and global-level functions, editing element properties, and deleting elements. The Diagram View is typically the view you use to build your data structure.
Structure View - The Structure View has two modes:
Table View and Output
The table view displays element properties in a table and enables updating XML element alias names, presentation names of the elements, sorting, null values, and reset options. The image below shows the structure Table View.
The Output view provides a clear view of the XML structure that is generated. The Output view cannot be updated. The figure shows the Output view.
Code View - The Code View displays the data structure code created by the data structure builder that is read by the data engine. You can update the content in code view. The figure shows the code view.
You can use the BI Publisher data engine to combine and structure data after you extract it from the data source.
Joining and structuring data at the source into one combined data set is sometimes not possible. For example, you cannot join data at the source when data resides in disparate sources such as Microsoft SQL Server and an Oracle Database. Even if your data is coming from the same source, if you are creating large reports or documents with potentially hundreds of thousands of rows or pages, structuring your data so that it matches the intended layout optimizes document generation.
Create a link to define a master-detail or parent-child relationship between two data sets. You can create links as element-level links or group-level links. The resulting, hierarchical XML data is the same. Creating links as element-level links is the preferred method. Group-level links are provided for backward compatibility with data templates from earlier versions of Oracle BI Publisher.
A data link or parent-child relationship relates the results of multiple queries. A data link can establish these relationships:
Between one query's column and another query's column.
Between one query's group and another query's group, useful when you want the child query to know about its parent's data.
Element-level links create a bind (join) between two data sets and define a master-detail (parent-child) relationship between them.
Create element-level links, the preferred method, to define master detail relationships between data sets. When you use element-level links to link data sets, you do not need to code a join between the two data sets through a bind variable.
Group-level links determine how data sets are structured as hierarchical XML, but lack the join information that the data engine needs to execute the master and detail queries.
When you define a group-level link, you must update your query with a link between the two data sets through a unique bind variable.
Link data sets to define a master-detail or parent-child relationship between two data sets.
Defining an element-level link enables you to establish the binding between the elements of the master and detail data sets.
A group-level link defines a master-detail relationship between two data sets.
The following figure shows two data sets with a group-level link defined. Next to the data sets the resulting XML data structure is shown.
In addition to creating parent-child structures by linking two data sets, you can also group elements in the same data set by other elements.
Creating subgroups might be helpful if your query returns data that has header data repeated for each detail row. By creating a subgroup you can shape the XML data for better, more efficient document generation.
Once you've created a group within your data set, two new options display on the element action menu that enable you to move elements between the parent and child groups.
For the element that you want to move, click the element action icon to open the menu. If the element is in the parent group and you want to move it to the child group, select Move selected elements to Child Group.
If the element is in the child group and you want to move it to the parent group, select Move selected elements to Parent Group. In the figure below, the element action menu for OFFICE_DSC displays the option to move the element to the parent group.
Before moving an element be aware of any dependencies on other elements.
You can use the data model editor to aggregate data at the group or report level.
For example, if you group sales data by Customer Name, you can aggregate sales to get a subtotal for each customer's sales. You can only aggregate data for at the parent level for a child element.
The aggregate functions are:
Average - Calculates the average of all the occurrences of an element.
Count - Counts the number of occurrences of an element.
First - Displays the value of the first occurrence of an element in the group.
Last - Displays the value of the last occurrence of an element in the group.
Maximum - Displays the highest value of all occurrences of an element in the group.
Minimum - Displays the lowest value of all occurrences of an element in a group.
Summary - Sums the value of all occurrences of an element in the group.
Filters enable you to conditionally remove records selected by your queries.
Groups can have two types of filters:
Expression — Create an expression using predefined functions and operators
PL/SQL Function — Create a custom filter
Click Menu, and then select Create Group Filter.
The Edit Group Filter dialog is displayed as shown below.
Select the Group Filter Type: Expression or PL/SQL.
Note:
For PL/SQL filters, you must first specify the PL/SQL Package as the Oracle DB Default Package in the data model properties. See Setting Data Model Properties.
Enter the Filter:
To enter an expression, select the elements and click the shuttle button to move the element to the Group Filter definition box. Click the predefined functions and operators to insert them in the Group Filter box.
See Function Reference for a description of the available functions.
Click Validate Expression to ensure that the entry is valid.
To enter a PL/SQL function, select the PL/SQL package from the Available box and click the shuttle button to move the function to the Group Filter box.
Your PL/SQL function in the default package must return a Boolean type.
After you have added the group filter, the data set object displays the filter indicator, as shown.
You can perform various functions at the element level.
Group by an element to create a subgroup, as described in Creating Subgroups
Create element-level links between data sets, as described in Creating Element-Level Links
Set element properties, as described in Setting Element Properties
You can set properties for individual elements.
Note that these properties are also editable from the Structure View. If you need to update multiple element properties, it may be more efficient to use the Structure View. See Using the Structure View to Edit Your Data Structure.
To set element-level properties using the element dialog:
Sorting is supported for parent group break columns only.
For example, if a data set of employees is grouped by department and manager, you can sort the XML data by department. Within each department you can group and sort data by manager. If you know how the data should be sorted in the final report, you specify sorting at data generation time to optimize document generation.
To apply a sort order to a group:
This section describes how to perform group-functions.
Topics include:
The Menu button is available at the group level and enables to perform various functions.
Create and delete group links, as described in Creating Group-Level Links
Create, edit, and delete group filters, as described in Creating Group Filters
Add an element to the group based on an expression, as described in Adding a Group-Level or Global-Level Element by Expression
Edit the data set, as described in Editing the Data Set
Remove elements from the group, as described in Removing Elements from the Group
Edit group properties, as described in Editing the Group Properties
The group-level Menu button is shown below.
Launch the data set editor to modify properties of selected data sets.
See the appropriate section for the data set type in Creating Data Sets for more information.
You can remove elements from groups as needed.
To remove an element from the group:
Note:
You can only remove elements added as a group function (sum, count, and so on) or added as an expression.
The Global Level Functions object enables you to add elements to your report data set at the top report level.
You can add the following types of elements as top-level data:
Elements based on aggregate functions
Elements based on expressions
Elements based on PL/SQL statements (for Oracle Database data sources)
Note:
If you select a data type of Integer for any calculated element and the expression returns a fraction, the data is not truncated.
The Global Level Functions object is shown below. To add elements based on aggregate functions, drag the element to the "Drop here for aggregate function" space of the object. To add an element based on an expression or PL/SQL, click Menu, and choose the appropriate action.
You can add global-level aggregate functions based on selected elements.
Available functions are as follows:
Count
Average
First
Last
Maximum
Minimum
Summary
You can add group-level or global-level aggregate functions by expressions.
The Structure view enables you to preview the structure of your data model.
The Data Source column displays the date elements in a hierarchical tree that you can collapse and expand. Use this view to verify the accuracy of the data model structure and to perform the following edits:
The Structure view is shown below.
Use the Structure page to define user-friendly names for elements in the data model.
You can rename both the XML element tag name (XML View) and the name that displays in the report layout tools (Business View). The figure below shows renaming the Data Source elements to friendlier Business View names.
The table below describes the usage of supported functions available from the Add Element by Expression dialog and the Edit Group Filter dialog.
Function | Description | Syntax | Example |
---|---|---|---|
IF |
Logical IF operator Evaluates boolean_expr, and returns true_return if boolean_expr is true, and false_return if boolean_expr is false. |
IF (boolean_expr, true_return, false_return) |
IF (G_1.DEPARTMENT_ID == 10, 'PASSED', 'FAIL')returns 'PASSED' if DEPARTMENT_ID = 10, otherwise returns 'FAIL' |
NOT |
Logical NOT operator Evaluates boolean_expr, and returns true if boolean_expr is false. |
STRING(NOT(boolean_expr)) |
STRING(NOT(G_1.JOB_ID == 'MANAGER'))returns 'TRUE' if JOB_ID = MANAGER, otherwise returns 'FALSE' |
AND |
Logical AND operator Evaluates boolean_expr1 and boolean_expr2, and returns true if both Boolean expressions are true, otherwise returns false. |
STRING(AND(boolean_expr1, boolean_expr2, ...)) |
STRING(AND (G_1.JOB_ID == 'MANAGER', G_1.DEPARTMENT_ID == 10))returns 'TRUE' if both JOB_ID = MANAGER and DEPARTMENT_ID = 10, otherwise returns 'FALSE' |
&& |
Logical AND operator Evaluates boolean_expr1 and boolean_expr2, and returns true if both Boolean expressions are true, otherwise returns false. |
STRING(boolean_expr1 && boolean_expr2) |
STRING(G_1.JOB_ID == 'MANAGER' && G_1.DEPARTMENT_ID == 10) returns 'TRUE' if both JOB_ID = MANAGER and DEPARTMENT_ID = 10, otherwise returns 'FALSE' |
|| |
Logical OR operator Evaluates boolean_expr1 and boolean_expr2 and returns true if both Boolean expressions are true, otherwise returns false. |
STRING(OR(boolean_expr1, boolean_expr2) |
STRING(OR (G_1.JOB_ID == 'MANAGER', G_1.DEPARTMENT_ID == 10)) returns 'TRUE' if either JOB_ID = MANAGER or DEPARTMENT_ID = 10, otherwise returns 'FALSE' |
MAX |
Returns the maximum value of the element in the set. |
MAX(expr1, expr2, expr3, ...) |
MAX(G1_Salary, 10000) returns max of salary or 10000 |
MIN |
Returns the minimum value of the element in the set. |
MIN(expr1, expr2, expr3, ...) |
MIN(G1_Salary,5000) returns min of salary or 5000 |
ROUND |
Returns a number rounded to the integer places right of the decimal point. |
ROUND(number[,integer]) If integer is omitted, number is rounded to 0 places. Integer can be negative to round off digits left of the decimal point. Integer must be an integer. |
ROUND(2.777) returns 3 ROUND(2.777, 2) returns 2.78 |
FLOOR |
Returns the smallest integer equal to or less than n. |
FLOOR(n) |
FLOOR(2.777) returns 2 |
CEILING |
Returns the largest integer greater than or equal to n. |
CEILING(n) |
CEILING(2.777) returns 3 |
ABS |
Returns the absolute value of n. |
ABS(n) |
ABS(-3) returns 3 |
AVG |
Returns the average value of the expression. |
AVG(expr1, expr2, expr3, ...) |
AVG(G_1.SALARY,G_1.COMMISSION_PCT*G_1.SALARY) returns the average of SALARY and COMMISSION For example, if SALARY = 14000 and COMMISSION_PCT = .4, the expression evaluates to 9800.0 |
LENGTH |
Returns the length of an array. The LENGTH function calculates the length using characters as defined by the input character set. If char is null, the function returns null. If char is an array, it returns the length of the array. |
LENGTH(expr) |
Example to return the length of an array: LENGTH{1, 2, 4, 4}) returns 4 Example to return the length of a string: LENGTH('countries') returns 9 |
SUM |
Returns the sum of the value of the expression. |
SUM(expr1, expr2, ...) |
SUM (G_1.SALARY, G_1.COMMISSION_PCT*G_1.SALARY) returns sum of salary and commission For example, if SALARY = 14000 and COMMISSION_PCT =.4, the expression evaluates to 19,600.0 |
NVL |
Replaces null (returned as a blank) with a string in the results of a query. |
NVL(expr1, expr2) If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1. |
NVL(G_1.COMMISSION_PCT, .3) returns .3 when G_1.COMMISSION_PCT is null |
CONCAT |
Returns char1 concatenated with char2. |
CONCAT(char1, char2) |
CONCAT(CONCAT(First_Name, ' '), Last_Name) where First_Name = Joe and Last_Name = Smith returns Joe Smith |
STRING |
Returns char as a string data type. |
STRING(expr) |
STRING(G1_SALARY) where salary = 4400 returns 4400 as a string |
SUBSTRING |
Extracts a substring from a string. |
SUBSTRING(string, start_pos, end_pos) string is the source string. start_pos is the position to start the extraction. end_pos is the end position of the string to extract (optional). |
SUBSTRING('this is a test', 5, 7) returns "is" (that is, characters 6 through 7)SUBSTRING('this is a test', 5) returns "is a test" |
INSTR |
Returns the position/location of the first character of a substring in a string. |
INSTR(string1, string2) string1 is the string to search. string2 is the substring to search for in string1. |
INSTR('this is a test', 'is a') returns 5 |
DATE |
Converts a valid Java date string to a date data type in canonical format. |
DATE(char, format_string) where (1) char is any valid Java date string (for example, 13-JAN-2013)(2) format_string is the Java date format of the input string (for example, dd-MMM-yyyy) The input and format strings must be a valid Java date format string. |
DATE(01-Jan-2013,'dd-MMM-yyyy') returns 2013-01-01T08:00:00.000+00:00 |
FORMAT_DATE |
Converts a date argument in the Java date format to a formatted string. |
FORMAT_DATE(date,format_string) |
FORMAT_DATE(SYSDATE,'dd-MMM-yyyy') where the value of SYSDATE = 2013-01-24T16:32:45.000-08:00returns 24-Jan-2013 |
FORMAT_NUMBER |
Converts a number or numeric string to a string in the specified number format. |
FORMAT_NUMBER(number,format_string) |
FORMAT_NUMBER(SOME_NUMBER, '$9,999.00') where the value of SOME_NUMBER = 12345.678returns $12,345.68 |
DECODE |
Replaces the value of an expression with another value based on the specified search and replace criteria. |
DECODE(expr, search, result [, search, result]...[, default]) |
DECODE(PROD_FAMILY_CODE,100,'Colas',200,'Root Beer',300,'Cream Sodas',400,'Fruit Sodas','Other')returns(1) 'Colas' if PROD_FAMILY_CODE = 100(2) 'Root Beer' if PROD_FAMILY_CODE = 200(3) 'Cream Sodas' if PROD_FAMILY_CODE = 300(4) 'Fruit Sodas' if PROD_FAMILY_CODE = 400(5) 'Other' if PROD_FAMILY_CODE is any other value |
REPLACE |
Replaces a sequence of characters in a string with another set of characters. |
REPLACE(expr,string1,string2) where string1 is the string to search for and string2 is the string to replace. |
REPLACE(G_1.FIRST_NAME,'B','L') where G_1.FIRST_NAME = Barry returns Larry |