Oracle BI Publisher 11g: A Practical Guide to Enterprise Reporting — Save 50%
Create and deliver improved snapshots in time of your Enterprise data using Oracle BI Publisher 11g with this hands-on book and eBook guide
Oracle BI Publisher is Oracle's reporting XML-based technology, which generates highly formatted data output using multiple data sources. BIP also offers different types of Layout Templates for designing a report.
In this article by Daniela Bozdoc, author of Oracle BI Publisher 11g: A Practical Guide to Enterprise Reporting, we will look at the XPT format, which is a new template option provided by BIP. It is designed in a totally BIP-integrated layout design interface. We will also see how to use this interface to design a complete report.
(For more resources on Oracle 11g, see here.)
We will cover the following topics in this article:
- The Layout Editor presentation
- Designing a Layout
- Export options
The Layout Editor
First, you have to choose a predefined layout from the Create Report Interface. As you can see in the following screenshot, this interface displays a list of predefined layouts:
You can add your own predefined layouts to this list and make them available for your later use or even for all the users.
After choosing a layout from the Basic Templates or the Shared Templates group, the Layout Editor Interface is displayed.
Designing a Layout
In the Layout Editor Interface, as shown in the following screenshot, you have tools to perform activities such as:
- Insert a component: Select the desired component from the Components pane on the left or from the Insert tab of the toolbar and drag-and-drop it into the design area
- Set component properties: Set the component properties from the Properties pane on the left or from the component-specific tab of the toolbar (only the most commonly used components)
- Insert a data element: Drag the element from the Data Source pane to the design area
- Drop Value Here
- Drop Label Here
- Drop Series Here
- Set page layout options: In order to set the page layout options, use the Page Layout tab and the Properties pane
- Save the Layout: Use the activity icons from the toolbar on the right side
As shown in the preceding screenshot, a precise dropping area is marked. For example, in a chart you have the following marked areas:
In the following sections, a few elements will be inserted to complete our report design. You will also see the steps that you need to follow when inserting and setting the properties of these components.
In order to make changes in the settings of Text elements, follow the steps given here:
- Click on the Insert tab and choose the Text Item component from the toolbar, as shown in the following screenshot:
- Click on the Text tab and set a font color for your text using the Font Color icon from the toolbar, as shown in the following screenshot:
- Set the text margins using the Properties panel, as shown in the following screenshot:
In this way, we obtain the desired report title, as you can see in the following screenshot:
In order to insert data elements in our report's components, we will use the following Data Model:
In order to create Charts, follow the steps given here:
- Click on the Insert tab and choose the Chart component from the toolbar, as shown in the following screenshot.
- Select the newly inserted chart and go to the Chart tab on the toolbar to set the chart type (Vertical Bar in this example) and the chart style (Project in this example).
- Drag the LOAN_PERIOD and the PRICE ﬁelds from the Data Source (from the left pane) over the Drop Value Here area of the design view.
- Drop the TITLE ﬁeld from the Data Source over the Drop Label Here area:
In order to create Data tables , follow the steps given here:
- Click on the Insert tab and choose the Data Table component from the toolbar, as shown in the following screenshot.
- Drag the ﬁelds LOAN_DATE, LOAN_PERIOD, TITLE, and YEAR from the Data Source over the area marked as Drop a Data Item Here.
- Select the LOAN_DATE column and in the Properties pane set the Formatting Mask to yyyy-mm-dd.
- For each column of the table, enter a suitable value for Width, in the Properties pane. For example, the ﬁ rst column has a width of 1.00 inch:
eBook Price: $29.99
Book Price: $49.99
(For more resources on Oracle 11g, see here.)
Now, we will bring all the previously described components into a report, and create an interaction between the chart and the table. When viewing in an interactive mode, the events triggered by the user will determine the layout components to respond to these conﬁgured events.
For example, in order to ﬁlter the table rows according to the chart selection and to show only the selected section of the chart, follow the steps given here:
- On the Page Layout tab of the toolbar, click on the Conﬁgure Events option. You will get the Conﬁgure Events interface.
- Select Chart 1 in the Components list. A list of events and a list of targets will be displayed, as shown in the following screenshot:
- Check on the appropriate options in the Events and the Targets lists respectively, as shown in the preceding screenshot.
- Click on the Save icon to save your layout.
- Click on Return to see your result.
- Click on a section of the chart.
As you can see in the following screenshot, only the selected section of the chart is displayed along with only those rows of the table that meet the selection criteria:
Notice that the data details are displayed when you mouseover a chart item.
Don't check the Show Selection Only option if you want to keep all the values of the chart and ﬁ lter only the table values. The result will be as follows:
Repeating sections is used to group report data using a data element with multiple occurrences. In order to have all the loans of our example grouped by their titles, follow the steps given here:
- Click on the Insert tab of the toolbar and choose Repeating Section to insert a Repeating Section, as shown in the following screenshot:
- In the Insert Repeating Section dialog, enter TITLE as the repeating / grouping by element, as shown in the following screenshot:
- Save and return to check the elegant and interactive manner of BIP that displays repeating sections, as shown in the following screenshot:
In case of nested sections, you can use Group Detail as the repeating / grouping by element.
In order to see the difference, the same report is displayed in a PDF format, which is s hown in the following screenshot. Seven groups are displayed following each other, or on different pages according to the page break option settings:
Using your BIP Layout Template, you can generate reports in the following formats:
When viewing your report, click on Actions and then select Export to choose the desired export format, as shown in the following screenshot:
After completing this chapter, you should know how to use the Layout Editor Interface, how to add layout elements and set properties for these elements, and how to take advantage of some of the BIP Template features such as automatic ﬁltering and repeating sections.
The Template Builder for MS Word will be the main topic for the next chapter as this is an important tool available for RTF layouts design. It is suitable even when coding is a necessary part of the layout design.
- Oracle BI Publisher 11g: Working with Multiple Data Sources [Article]
- Getting Started with Oracle GoldenGate [Article]
- External Tables in Oracle 10g/11g Database: Part 2 [Article]
- Oracle: RDBMS Log Miner Utility, FRA, and AUM [Article]
eBook Price: $29.99
Book Price: $49.99
About the Author :
Daniela Bozdoc is an IT professional with experience of almost a decade working as a software developer, analyst, data and software architect on different technologies. These range from MS .NET Framework to Sybase Power Builder, Oracle and MS SQL Server, and continuing as an ERP consultant and report developer - MS dynamics NAV, Oracle EBS, and BI Publisher respectively in the present.
She is a graduate of Babes-Bolyai University of Cluj-Napoca, Romania, with a B.D. in Computer Science.
Daniela lives in Romania, where she has full family support of her career and enjoys taking pictures of beautiful landscapes and nature catching pieces.