ࡱ > ? A 4 5 6 7 8 9 : ; < = > { bjbjzz 1 B B 8 D , $ D P
.( ( V( V( V( 1) " S/ o1 א ِ ِ ِ ِ ِ ِ $ 8 2 1) 1) 2 2 V( V( Z > > > 2 V( V( א > 2 א > > J { \ o V( @Tѝ 55 B Ð H D V 7 o o T 2 2 > 2 2 2 2 2 g> p 2 2 2 D 2 2 2 2 2 2 2 2 2 2 2 2 2 B b :
Oracle BI Publisher
RTF Template Development Tutorial
Contents TOC \o "1-3" \h \z \u
HYPERLINK \l "_Toc169090624" Oracle BI Publisher PAGEREF _Toc169090624 \h 1
HYPERLINK \l "_Toc169090625" RTF Template Development Tutorial PAGEREF _Toc169090625 \h 1
HYPERLINK \l "_Toc169090626" Contents PAGEREF _Toc169090626 \h 2
HYPERLINK \l "_Toc169090627" Customer Balance Report Tutorial PAGEREF _Toc169090627 \h 3
HYPERLINK \l "_Toc169090628" Step 1: Load Sample XML Data PAGEREF _Toc169090628 \h 3
HYPERLINK \l "_Toc169090629" Step 2: Insert address block and report level dates and totals PAGEREF _Toc169090629 \h 3
HYPERLINK \l "_Toc169090630" Step 3: Preview Template PAGEREF _Toc169090630 \h 3
HYPERLINK \l "_Toc169090631" Step 4: Insert a table of Invoices PAGEREF _Toc169090631 \h 4
HYPERLINK \l "_Toc169090632" Step 5: Number & Date Formatting PAGEREF _Toc169090632 \h 6
HYPERLINK \l "_Toc169090633" Number formatting PAGEREF _Toc169090633 \h 6
HYPERLINK \l "_Toc169090634" Date formatting PAGEREF _Toc169090634 \h 7
HYPERLINK \l "_Toc169090635" Step 6: Dressing Up PAGEREF _Toc169090635 \h 7
HYPERLINK \l "_Toc169090636" Headers and Footers PAGEREF _Toc169090636 \h 7
HYPERLINK \l "_Toc169090637" Images PAGEREF _Toc169090637 \h 8
HYPERLINK \l "_Toc169090638" Page Numbering PAGEREF _Toc169090638 \h 8
HYPERLINK \l "_Toc169090639" Step 7: Adding Total PAGEREF _Toc169090639 \h 9
HYPERLINK \l "_Toc169090640" Adding Total PAGEREF _Toc169090640 \h 9
HYPERLINK \l "_Toc169090641" Step 8: Adding Page/Brought Forward/Carried Forward Total PAGEREF _Toc169090641 \h 9
HYPERLINK \l "_Toc169090642" Step 9: Adding Running Total PAGEREF _Toc169090642 \h 11
HYPERLINK \l "_Toc169090643" Step 10: For loop PAGEREF _Toc169090643 \h 12
HYPERLINK \l "_Toc169090644" Step 11: Page/Section Break PAGEREF _Toc169090644 \h 12
HYPERLINK \l "_Toc169090645" Step 12: Conditional formatting PAGEREF _Toc169090645 \h 13
HYPERLINK \l "_Toc169090646" Step 13: Adding a barcode PAGEREF _Toc169090646 \h 13
HYPERLINK \l "_Toc169090647" Step 14: Sorting PAGEREF _Toc169090647 \h 14
HYPERLINK \l "_Toc169090648" Step 15: Different Last Page PAGEREF _Toc169090648 \h 15
HYPERLINK \l "_Toc169090649" Step 16: Chart PAGEREF _Toc169090649 \h 15
HYPERLINK \l "_Toc169090650" Step 17: Add Table of Contents or Bookmark! PAGEREF _Toc169090650 \h 17
HYPERLINK \l "_Toc169090651" Table of Contents PAGEREF _Toc169090651 \h 17
HYPERLINK \l "_Toc169090652" Bookmark PAGEREF _Toc169090652 \h 18
HYPERLINK \l "_Toc169090653" Conclusion PAGEREF _Toc169090653 \h 19
Customer Balance Report Tutorial
This tutorial describes how you would typically create a layout template for the Receivables Customer Balance Report. It will use the following two sample data XML files
AR_SAMPLE_DATA.xml
Crosstab_Sample_Data.xml
AR_Sample_Data.xml is required for most of the training steps in this tutorial. Crosstab_Sample_Data.xml is required only for Crosstab training.
Before you start the following set of training steps, make sure that you have installed BI Publisher Desktop Add-in.
Step 1: Load Sample XML Data
From the Data menu select Load XML Data to load a sample XML document that contains the data fields you may want to insert into your RTF layout template. Select the XML data file called AR_SAMPLE_DATA.xml.
Step 2: Insert address block and report level dates and totals
You may want to select single data fields, such as the customer name and address at the header of the letter. Position the cursor in the Word document where you want to insert the Address. From the Insert menu select Field to open the dialog window shown below.
Select the field Customer Name and click Insert to insert a form field for the customer name into the document. You may also insert the remaining address fields (such as Address Line1, City, State and Zip). You can also just drag the Customer Name field from the dialog and drop it into the Word document.
Note: As a beginner, you should use Insert Fields only for data fields that are unique (not repeating) in your document. Refer to the Insert Table section for additional information on how to insert repetitive fields
Step 3: Preview Template
After adding fields to the document, you may want to test your template. Oracle BI Publisher Template Builder allows you to preview your template with your sample XML data. You can preview the output in Adobe Acrobat Reader (PDF), Microsoft Word file (RTF), Microsoft EXCEL (EXCEL) or a browser (HTML).
From the Preview menu select HTML to preview your template in a browser. From the Preview menu select PDF to preview your document in Acrobat Reader. You must have Adobe Acrobat Reader version 5.0 or higher installed to preview documents in PDF format. You can download Acrobat Reader for free at HYPERLINK "http://www.adobe.com/products/acrobat/readstep2.html" http://www.adobe.com/products/acrobat/readstep2.html.
The preview should show Vision Corporation instead of the field Customer Name.
Step 4: Insert a table of Invoices
You can build a table or a repeating section by inserting the elements and then adding additional form fields with processing instructions (for-each). Instead, the Template Builder offers an additional function that generates a table for you.
The document should include a table of all invoices that are not or only partially p a i d . O n t h e I n s e r t m e n u s e l e c t T a b l e / F o r m > A d v a n c e d t o o p e n t h e T a b l e / F o r m D i a l o g T h e f o l l o w i n g d i a l o g w i l l b e d i s p l a y e d :
0
S c r o l l i n t h e l e f t D a t a S o u r c e v i e w p a n e u n t i l y o u s e e t h e G I n v o i c e s e l e m e n t . P u s h t h e l e f t m o u s e b u t t o n a n d d r a g t h e G I n v o i c e s e l e m e n t o v e r t o t h e c e n t e r T e m p l a t e t r e e v i e w . W h e n y o u d r o p a n e l e m e n t w i t h c h i l d r e n ( o t h e r e l e m e n t s b e l o w t h e e l e m e n t ) , t h e f o l l o w i n g p o p - u p d i a l o g a p p e a r s : 0
N o t e : A N o d e i s a t e r m u s e d i n t r e e v i e w s f o r a n e l e m e n t i n t h e t r e e v i e w . F o r e x a m p le, Customer Name, G Currency and G Invoices are all nodes. Transaction Date is also called a child node of G Invoices, and G Invoices is referred to as a parent node of Transaction Date.
To add multiple nodes, click Drop All Nodes with the left mouse button. When you select the "G Invoices" node, the dialog box should now look like this:
0
W e o n l y w a n t t o s h o w t h e f i e l d s T r x N u m b e r , T r a n s T y p e , T r a n s a c t i o n D a t e , T r a n s A m o u n t , a n d T r a n s A m o u n t R e m a i n i n g . S e l e c t t h e f i e l d R e c e i p t A m o u n t i n t h e c e n t e r v i e w a n d p r e s s t h e D e l e t e b u t t o n o n y o u r k e y b o a r d . R e p e a t t h e p r o c e d u r e f o r t h e o t h e r f i e l d s that you do not want to be included in the table (as shown in the screenshot below).
You should now click again on the G Invoices element in the center view. The Properties region on the right shows how the data fields will be formatted. You should see the following information:
0
F o r e a c h g r o u p y o u c a n s e t t h e f o l l o w i n g p r o p e r t i e s t h a t d e s c r i b e h o w a g r o u p ( s u c h a s G I n v o i c e s ) s h o u l d b e r e n d e r e d b y t h e t e m p l a t e b u i l d e r :
S t y l e : S e l e c t T a b l e t o c r e a t e a t a b l e a r o u n d t h e d a t a f i e l d s .
G r o u p i n g : G r o u p i n g i s a n a d v a n c e d o p e r a t i o n t hat allows you to re-group the data for example by transaction date instead of currency. You can select the element that the data should be grouped by for this property. Please refer to the Oracle BI Publisher Users Guide for additional information on grouping.
Show Grouping Value: This property will only be shown if you have selected a node created by the Grouping functionality. You can choose for a group if the Data Field node used as a grouping criterion is shown in the table or form.
Sort By: You can select an element by which the data groups are sorted.
Sort Order: If you have selected an element for Sort By you can select, if the data should be sorted either ascending or descending.
Sort Data Type: If you have selected an element for Sort By the data is by default sorted as Text. That means that 12 will be shown after 111. If you need to sort numbers you should select Number as the sort data type
Break: This property allows you to insert a page break or a section break between every data group. If nothing is select, then the data groups will be shown continuously with no break. If you select New Page per Element, then a page break will be inserted between each element. If you select New Section per Element, a section break will be created for each data group. A section break allows changing the header/footer and resets the page number. You will typically use this option, if you want to print multiple documents (for example invoices or purchase orders) to a single PDF file.
The order in which the data elements are shown reflects the order of the columns in the table. If you want to reorder the columns you need to change the Insert Position box from Child to Same Level. Then drag the elements into the correct order.
Click the OK button to create the table. The inserted table should look like this:
Trx NumberTrans TypeTransaction DateTrans AmountTrans Amount Remaining FORMTEXT for-each G_INVOICES FORMTEXT TRX_NUMBER FORMTEXT TRANS_TYPE FORMTEXT TRANSACTION_DATE FORMTEXT TRANS_AMOUNT FORMTEXT TRANS_AMOUNT_REMAINING FORMTEXT end G_INVOICES
The Template Builder creates two kinds of form fields:
Form fields representing data elements
Form fields with processing instructions for repeating table rows or document section
Form fields representing data elements are replaced with the data when the template is processed. The for-each G_INVOICES and end G_INVOICES form fields indicate a repeating section. The section of the document encapsulated by these two form fields is repeated, if the associated data element G_INVOICE is found repetitively in the data. Please refer to the Oracle BI Publisher Users Guide for additional information on form fields.
You can now format these elements in Microsoft Word and add additional text. You can also move fields around as long as you keep them in the surrounding for-each G_INVOICES and end G_INVOICES processing instruction. If you remove one of these processing instructions your template may not work anymore with BI Publisher. You now can preview the template again to review your current template, this time try PDF as an output.
Step 5: Number & Date Formatting
You probably would like to change the alignment of the table columns or change the data type and format for the amounts and dates.
Number formatting
Select the TRANS_AMOUNT field, right-click and select Properties to see the Text Form Field Options dialog.
You may select the Number in the Type field, #,##0.00 for Number format and enter 1000 as the Default Number to achieve a more desirable format for currencies in US Dollar.
Date formatting
To format the date, open the form field dialog for the TRANSACTION_DATE and specify that it is a Date field. Then select the date format you want to use. It should be noted here that BI Publisher is expecting the date data as an XSD format date (Canonical Date Format) e.g. 2006-09-01 00:00:00.0. If the date is not in this format then BI Publisher will be unable to format it further.
Formatting options defined in this dialog box are understood by Oracle BI Publisher and used to format your fields. Now preview your template again to see the date and number formatting in action.
Step 6: Dressing Up
Headers and Footers
You will have noticed by now that you can use the MSWord header and footer functionalities. You can place a company logo, report title, and page numbering, etc, in the header and in the footer, and also you can place a BI Publishers boilerplates to display data in the header and footer in the generated reports. Only one problem is that MS Word doesnt let you place text form fields nor auto shapes in the area, so if you have such requirements that you need to, for example, display auto shapes or have conditions that you want to write by using the text form, then you can use BI Publishers tag, and .
Anything before tag would be considered as header area and after would be as footer area, and the between the two tags would be body content area. We will use the BI Publishers header/footer tags for this exercise.
Also its a best practice to use a table component to control the layout, so well use a table in the header section and another table in the footer section.
Type before CUSTOMER_NAME text form. and type after the table. (You can use a text form to have this tag as well.)
Insert a table in the header before the tag and another table after the tag. Choose 3 columns and 1 row for the each table.
From the right click menu choose Borders and Shading and make all the grid and table frame lines not to be displayed.
Type Customer Balance Report in the central column of the header table. Make that to be bold and center alignment.
Type Draft in the central column of the footer table. Make that to be bold and center alignment.
Now your template should look like this.
Customer Balance Report
FORMTEXT CUSTOMER_NAME
FORMTEXT ADDRESS_LINE1
FORMTEXT CITY, FORMTEXT STATE, FORMTEXT ZIP
Trx NumberTran FORMTEXT ADDRESS_LINE1s TypeTransaction DateTrans AmountTrans Amount Remaining FORMTEXT F FORMTEXT TRX_NUMBER FORMTEXT TRANS_TYPE FORMTEXT TRANSACTION_DATE FORMTEXT TRANS_AMOUNT FORMTEXT TRANS_AMOUNT_REMAINING FORMTEXT F
Draft
Images
Its a simple case of using the Insert>Picture> From File to insert an image into the header/footer or main region of the report. Lets insert Oracle company logo in the header region. You should have downloaded the file called company_logo.png onto your local machine by now, otherwise download it from the files online folder.
Page Numbering
You can use the MSWord autotext Page X of Y and other auto text objects if you use MSWords native header and footer functionalities. But this time were not using that, instead were using BI Publishers tags to support the header and footer. But we still can display such page number.
Place a cursor into the most right cell and choose Field from Insert tool bar menu and select Page.
Type of after the Page field.
Choose Field from Insert tool bar menu and select NumPages and place it right after the of.
Make the cell to be right alignment
Now your template should look like this.
Customer Balance Report PAGE \* MERGEFORMAT 1 of NUMPAGES \* MERGEFORMAT 1
FORMTEXT CUSTOMER_NAME
FORMTEXT ADDRESS_LINE1
FORMTEXT CITY, FORMTEXT STATE, FORMTEXT ZIP
Trx NumberTrans TypeTransaction DateTrans AmountTrans Amount Remaining FORMTEXT F FORMTEXT TRX_NUMBER FORMTEXT TRANS_TYPE FORMTEXT TRANSACTION_DATE FORMTEXT TRANS_AMOUNT FORMTEXT TRANS_AMOUNT_REMAINING FORMTEXT F
Draft
BI Publisher supports many of the MSWord native functions so spend some time changing the look and feel of your report using the MSWord dialogs. For example, font size, font color, table color, table lines, etc, etc
Step 7: Adding Total
Adding Total
Now you want to see the total amount of the whole transactions amount. This would be very simple.
Click Insert from Template Builder tool bar menu and choose Field
Select TRANS_AMOUNT element and choose SUM as a calculation
Click Insert button
You can add another SUM Total for TRANS_AMOUNT_REMAINING, too. In this tutorial, youll add another row at the last row of the table so that you can display total amount of the whole transaction at the last row of the table. So it would look like this.
Trx NumberTrans TypeTransaction DateTrans AmountTrans Amount Remaining FORMTEXT F FORMTEXT TRX_NUMBER FORMTEXT TRANS_TYPE FORMTEXT TRANSACTION_DATE FORMTEXT TRANS_AMOUNT FORMTEXT TRANS_AMOUNT_REMAINING FORMTEXT ETotal: FORMTEXT sum TRANS_AMOUNT FORMTEXT sum TRANS_AMOUNT_REMAINING
Step 8: Adding Page/Brought Forward/Carried Forward Total
In this exercise we also want to display page total, brought forward total and carried forward total of the transaction amount in each page. The brought forward total is a total amount of the previous page that is forwarded to the current page while the carried forward total is a total amount of the current page that is carried to the next page. Since we want to display these total values in every page, we will put the brought forward total in header area and the carried forward total in footer area.
Set Page Total
Insert another text form into TRANS_AMOUNT_REMAINING columns cell of the main table, place it right before the TRANS_AMOUNT_REMAINING text form.
Double click on the text form and type PT as a default value.
click Add Help Text and type the following in the box.
Click OK.
Trx NumberTrans TypeTransaction DateTrans AmountTrans Amount Remaining FORMTEXT F FORMTEXT TRX_NUMBER FORMTEXT TRANS_TYPE FORMTEXT TRANSACTION_DATE FORMTEXT TRANS_AMOUNT FORMTEXT PT FORMTEXT TRANS_AMOUNT_REMAINING FORMTEXT ETotal: FORMTEXT sum TRANS_AMOUNT FORMTEXT sum TRANS_AMOUNT_REMAINING
Display Page Total in Footer
Insert a row above the existing row in the Footer.
Type Page Total: and select right alignment in the central cell of the footer table.
Place mouse cursor at the right cell of the table and click Text Form Field icon from the tool bar to insert a Text Form.
Double click on the text form and click Add Help Text and type the following in the box.
Type Page Total in Default Text field and click OK.
Note that you need to specify a number format in the xdofo tag and MS Word number formatting functionality can not be used.
Page Total: FORMTEXT Page TotalDraft
Display Brought Forward Total in Header
Insert a row below the existing one in a header table
Select the entire row and select Boarders and Shading from right click menu
Under Boarder tag, set a boarder line for the bottom and click OK.
Type Brought Forward Total: and select right alignment in the central cell of the header bottom line.
Place mouse cursor at the right cell of the table and click Text Form Field icon from the tool bar to insert a Text Form.
Double click on the text form and click Add Help Text and type the following in the box.
Type Forward Total in Default Text field and click OK.
Customer Balance Report PAGE \* MERGEFORMAT 1 of NUMPAGES \* MERGEFORMAT 1Brought Forward Total: FORMTEXT Forward Total
Display Carried Forward Total in Footer
Insert an additional row under Page Total row in the Footer table
Type Carried Forward Total: and select right alignment in the central cell of the footer table.
Place mouse cursor at the right cell of the table and click Text Form Field icon from the tool bar to insert a Text Form.
Double click on the text form and click Add Help Text and type the following in the box.
Type Carried Total in Default Text field and click OK.
Page Total: FORMTEXT Page TotalCarried Forward Total: FORMTEXT Carried TotalDraft
And now you should have something like this.
Customer Balance Report PAGE \* MERGEFORMAT 1 of NUMPAGES \* MERGEFORMAT 1Brought Forward Total: FORMTEXT Forward Total
Trx NumberTrans TypeTransaction DateTrans AmountTrans Amount Remaining FORMTEXT F FORMTEXT TRX_NUMBER FORMTEXT TRANS_TYPE FORMTEXT TRANSACTION_DATE FORMTEXT TRANS_AMOUNT FORMTEXT PT; FORMTEXT TRANS_AMOUNT_REMAINING FORMTEXT E
FORMTEXT sum TRANS_AMOUNT_REMAINING
Page Total: FORMTEXT Page TotalCarried Forward Total: FORMTEXT Carried TotalDraft
Step 9: Adding Running Total
Ok, were almost done for this total stuff! Just one more, its very exciting one! Its a running total that will keep adding the previous value to the current value when it goes down the cells so the very last value should be the same as a total value. This requires a little trick, so it might sound a bit complicated, but once you understand it, its pretty straight forward! Basically what you need to do is to define a variable, and add the current value to whatever the value has been added at the previous cell.
Insert a Text Form at the beginning of the body space.
Double click on the text form and type RT as the default text.
Click Add Help Text and type the following.
Click OK.
Add a column to the right of Trans Amount Remaining and name the column header as Running Total.
Cut and past tag from the Trans Amount Remaining cell to the added cell so that it will be the last tag in the row of the table.
Insert a Text Form in the Running Totals cell.
Double click the text form and type Set as the default text.
Click Add Help Text and type the following. (this part gets the last value and add the current value to it, and set the accumulated value to the RT variable.)
Click OK.
Insert a Text Form right after the Set text form.
Double click the text form and change Type to Number and type 999.00 as the default text, and select #,##0.00 as the number format.
Click Add Help Text and type the following. (this part just displays the current accumulated value, which has been set by the functions that are set in Set text form.)
Click OK.
Ok, so hope you can make all the above steps set right, so by this point your report should look like this.
Customer Balance Report PAGE \* MERGEFORMAT 1 of NUMPAGES \* MERGEFORMAT 1Brought Forward Total: FORMTEXT Forward Total
FORMTEXT
T r x N u m b e r T r a n s T y p e T r a n s a c t i o n D a t e T r a n s A m o u n t T r a n s A m o u n t R e m a i n i n g R u n n i n g T o t a l F O R M T E X T F F O R M T E X T T R X _ N U M B E R F O R M T E X T T R A N S _ T Y P E F O R M T E X T T R A N S A C T I O N _ D A T E F O R M T E X T T R A N S _ A M O U N T F O R M T E X T P T ; F O R M T E X T T R A N S_AMOUNT_REMAINING FORMTEXT Set FORMTEXT 999.00 FORMTEXT E
FORMTEXT sum TRANS_AMOUNT_REMAINING
Page Total: FORMTEXT Page TotalCarried Forward Total: FORMTEXT Carried TotalDraft
Step 10: For loop
Now you might have already noticed that your report displays invoice information only for one customer but when you look into the XML data file there is much more customers information! This is because now you have only one for loop that is used for G_INVOICE node. In order to display multiple customer information you need to have another for-loop at G_CUSTOMER level.
You can simply insert a text form field by clicking text form field button into right before CUSTOMER_NAME filed, and double click the filed to open a dialog and click on Add Help Text button. And you can input the following.
As any other programming would do the same, you need to close the for-loop, so you need to insert another text form field to input the following.
And at this point, having the brought forward and carried forward doesnt really make sense anymore, so lets remove them just to make it simple. So now it should look like the below.
Customer Balance Report PAGE \* MERGEFORMAT 1 of NUMPAGES \* MERGEFORMAT 1
FORMTEXT F FORMTEXT CUSTOMER_NAME
FORMTEXT ADDRESS_LINE1
FORMTEXT CITY, FORMTEXT STATE, FORMTEXT ZIP
FORMTEXT
T r x N u m b e r T r a n s T y p e T r a n s a c t i o n D a t e T r a n s A m o u n t T r a n s A m o u n t R e m a i n i n g R u n n i n g T o t a l F O R M T E X T F F O R M T E X T T R X _ N U M B E R F O R M T E X T T R A N S _ T Y P E F O R M T E X T T R A N S A C T I O N _ D A T E F O R M T E X T T R A N S _ A M O U N T F O R M T E X T P T ; F O R M T E X T T R A N S_AMOUNT_REMAINING FORMTEXT Set FORMTEXT 999.00 FORMTEXT E
FORMTEXT sum TRANS_AMOUNT_REMAINING
FORMTEXT EF
Draft
Step 11: Page/Section Break
Now your report displays all the customers transaction data as one document, some of the customers information is displayed across the pages and starts at the middle of the page. But you might want to create it as multiple documents in one report book, which means that you want to have each customer data started at the top of the page so that you can separate each customers data into different documents and send them to each customer, for example.
The easiest way to achieve this is to insert a page break.
Place your mouse cursor between FORMTEXT sum TRANS_AMOUNT_REMAINING and FORMTEXT EF
Select Insert from Word tool bar and select Break menu, choose Page Break and click OK.
However, as you might notice it the page number is still continuous across pages so when you separate each customers document the page number wouldnt make sense. In order to have different set of page number for each customers document you can use a section break.
Double click on the first for-each for Customer Name text form.
Change the for-each loop to for-each@section
Example:
Before
After
Click OK.
Try Preview and make sure that your report displays customer data per customer and each customer section always starts from the top page. Also the page number starts with each customer and the total number of the pages are the number of pages for each customers.
Step 12: Conditional formatting
You can highlight certain values in your invoice table. Lets assume we want to use a red background on any amount remaining that is greater than $10000. To achieve this we need to add some XSL code to check the value and set the background color to be red if the value is greater than 10000.
The XSL code you need is:
10000?>
red
Step:
Insert a text form right before FORMTEXT TRANS_AMOUNT_REMAINING
Double click on the text form
Type Red as a default text
Click Add Help Text..
Insert the following tags.
100000?>
red
This tests the amount remaining value. If its over 1,000,000 then the background color attribute is set to red. This will override any other background color that may have been set.
Step 13: Adding a barcode
To add a barcode youll first need one. Install the Customer Balance Template/FREE3OF9.ttf font into your WINDOWS_HOME/fonts directory. Now restart MSWord, the font will now be available from the font drop down list.
Highlight the fields or text you want and select the barcode font. Now you need to define a mapping by creating a custom document property.
Select File ( Properties ( Custom Tab
In the Name field enter: xdo-font.Free 3 of 9.normal.normal notice the name of the font must match the name of the font in the MSWord font drop down
In the Value field enter: truetype.c:\windows\fonts\FREE3OF9.ttf
Now test your template using the Preview function and you should see the barcode rendered based on the data.
This works fine as long as you generate the report on your local machine, but note that it means it wouldnt work when you try to generate the report at a different machine unless the barcode font has been installed and the appropriate font mapping has already been defined. If you need to run the report at a different machine, for example at a server, which probably would be most of the cases, then you need to set such font configuration information. We dont go through that for this training but you can find the detail information how to set the font information in Users guide.
Step 14: Sorting
Now you want to display the transaction data from the lowest to the highest based on the transaction amount. BI Publisher supports a sorting function in the both ways, ascending and descending.
Double Click on the text form for G_INVOICES for-each loop, which is right before FORMTEXT TRX_NUMBER
Add the following
Trx NumberTrans TypeTransaction DateTrans AmountTrans Amount RemainingRunning Total FORMTEXT F FORMTEXT TRX_NUMBER FORMTEXT TRANS_TYPE FORMTEXT TRANSACTION_DATE FORMTEXT TRANS_AMOUNT FORMTEXT PT; FORMTEXT RED FORMTEXT TRANS_AMOUNT_REMAINING FORMTEXT Set FORMTEXT 999.00 FORMTEXT E
(Double click on F text form right before the FORMTEXT TRX_NUMBER, you can find the sorting tag.
First, you need to specify on which data you want to sort and select whether ascending or descending. When you preview at this point youll notice that the data is sorted based on the TRANS_AMOUNT value but its not quite right. For example, 120,653.20 is displayed before 12,375.00 although the sorting order should be ascending, which means from the lowest to the highest. This is because now the sorting is done based on the text value base sorting, not the number value base. You can change the data-type=text to data-type=number, then youll see the data is now sorted by TRANS_AMOUNT number value.
Step 15: Different Last Page
BI Publisher supports different last page header/footer or content. This is useful for documents such as checks, invoices, or purchase orders on which you may want certain content, such as check or summary, only in the last page. BI Publisher provides this ability. To utilize this feature, you must create a section break in your template to ensure the content of the final page is separated from the rest of the report and Insert the following syntax on the final page:
Any content above or below these two tags will appear only on the last page of the report. Also, note that because this command explicitly specifies the content of the final page, any desired headers or footers previously defined for the report must be reinserted on the last page.
Add another row in a table in the footer.
Type Trans Amount Remaining Total in the right cell.
Set the mouse cursor at the end of the first page.
Click Insert from tool bar and choose Break, and select Next Page under Section Break Type
Now you see another one page has been added. Move to the page.
Insert the following tags.
Copy the header and footer content from the first page and paste them to each of the header and footer area in the second page.
Click Insert from Template Builder menu and select Field.
Select Trans Amount Remaining from the tree and choose SUM from the calculation LOV, and click Insert button, so that you have the sum of Trans Amount Remaining in the right cell right after Remaining Transaction Amount Total:
Try Preview to see the result
You will notice that all the pages show total amount of Trans Amount Remaining in the footer area except for the first page for the customer that goes across more than one page. For example, look at a customer, Computer Service and Rentals, which has 2 pages of the report. Youll see that there is no Trans Amount Remaining total displayed in the footer area in the first page and only the second page has the Trans Amount Remaining total displayed in the footer. Thats what we are talking about! ;-)
The second page should look similar to this.
Customer Balance Report PAGE \* MERGEFORMAT 1 of NUMPAGES \* MERGEFORMAT 1Brought Forward Total: FORMTEXT Forward Total
Trans Amount Remaining: FORMTEXT TRANS_AMOUNT_REMAININGPage Total: FORMTEXT Page TotalCarried Forward Total: FORMTEXT Carried TotalDraftStep 16: Chart
To add a chart basically you need to do these 2 steps, the first is to insert a dummy image and the second is to define BI Beans Graph XML in Alternative Text area under Web tab of Format Picture dialog. But its not easy to understand this BI Beans Graph XML. Especially if youre just starting, (well, even youre experienced but still!) you might get overwhelmed by this. But dont worry! there is a Chart creation tool that can be accessed by the template builder tool menu. The following is the steps to insert a chart that shows customers transaction amount per customer in vertical bar graph.
Before you start the following steps, you need to take the section break out from the for-each@section, which you have done it at Step 11.
Click Chart under Insert menu of Template Builder
Drag and Drop CUSTOMER_NAME element from the left tree pane to Labels.
And check Grouping
Drag and Drop TRANS_AMOUNT from the left pane to main area or Value.
Select Bar Graph Vertical as Type
Select Comet as Style
Select SUM as Measure and choose
Click OK.
After you click OK youll see a dummy image inserted in your report. At this point, the image doesnt show the data in the Graph plot area though Customer names are displayed correctly. This is because inside the chart XSL definition the XPATH for TRANS_AMOUNT element is not set appropriately. Follow the following steps to modify the XSL definition.
Double click on the Chart image
Click Web tab
Find the following tags
|
Change it to
|
Notice the extra / between current-group() and TRANS_AMOUNT.
Try preview youll see the bar graph will be displayed with actual values.
Example:
You can also try different type of charts by using the Chart Creation Tool of the template builder.
There are whole a lot of different types of graphs are supported as long as BI Beans supports, but you need to understand the Graph XML and Data XML definition, you can find more information on HYPERLINK "http://bibeans.us.oracle.com/BIGraphSite/" BI Beans web site.
Ok, were almost done, all the contents are presented. But at the last thing, lets master this very cool technique that would attracts a lot of people! Its Table of Contents and Bookmark!
Step 17: Add Table of Contents or Bookmark!
Table of Contents
This is pretty easy and very straightforward. What you need to do is just to use MS Word native function.
Type and remove FORMTEXT CUSTOMER_NAME text form, this is because the Table of Content function can not handle the text form.
Change the font setting for the to be Heading 1.
Insert a section break before the first page header so that you will have a blank page as the first page. You can use MS Words section break for this. (Insert->Break->Section Break->Next Page)
Move to the inserted first page and place your mouse cursor into the first page.
Click Insert from the tool menu and select Reference-> Index and Tables
Click Table of Content tab and choose how you want to display your Table of Contents
Click OK.
Make sure that the table of contents has tag inserted.
So, you should see something like the following in the first page.
Step 17: Add Table of Contents or Bookmark!
As one of new features that have been introduced with 10.1.3.2 is a Crosstab support through Template Builder. Before, you still could create a crosstab, but you need to develop some logic to support dynamically generating columns and cells. Yes, if you have been there you know what Im talking about It was a lot of pain! But now, you can just insert the crosstab from the Template Builder tool bar and map required element in the UI, and you have the crosstab in your template! Its nothing!
In order to play with the crosstab, lets start with a new data and new template.
Create a new RTF template
Import
Bookmark
So the one last step! Now you have a table of contents displayed in the first page and sometimes that is what users want. But if youre familiar with PDF document you might want to make it as a bookmark that is displayed at left pane and makes so easy to move around the document you generate. Only you need to do is to add BI Publishers tag before and after the table of contents that has been added at the previous step.
Remove the section break that has been used to create the first page for the table of contents because the table of contents is not going to be displayed, it will be converted to a bookmark, so you dont want to have one blank page at the first page.
Add the following tag before the table of contents.
Add the following tag after the table of contents.
Now, try preview the document, you will see a bookmark that has links to each customers report. Pretty cool, huh?!
Step 17: Crosstab
One of the new features that have been introduced since R10.1.3.2 is a Crosstab UI development support through the Template Builder. Before, you could still develop the crosstab layout by manually developing some logic to generate dynamic columns and cells based on the data. If you have been there, you know what Im talking about Yes, it was such a pain to insert such logic based on your data. But now things got much better, you can just insert the crosstab object from the toolbar and drag and drop the data element in the new UI to generate the crosstab!
Before this exercise lets create a new template and load a new data for crosstab layout.
Create a new RTF template
Load Crosstab_Sample_Data.xml
Go to File->Page Setup and choose Landscape as Orientation
9 C D [ \ ] ^ _ { | } ~ ϻ|\H0H /j hUA h CJ OJ QJ UmH nH u&hUA h CJ OJ QJ mH nH u >j hUA h >*B*CJ OJ QJ UmH nH ph u #hUA h CJ OJ QJ mH nH u'hUA h 0J CJ OJ QJ mH nH u0j hUA h 0J CJ OJ QJ UmH nH u 'hUA h CJ OJ QJ aJ mH nH u%j hUA h CJ OJ QJ UaJ hUA h CJ OJ QJ aJ hUA h CJ, OJ QJ aJ, 9 : ] b $
e n
c
Z 6
n&
n&
n&
5 κΡ{{[{κAκΡ{ 2jw hUA h CJ OJ QJ UmH nH u >j hUA h >*B*CJ OJ QJ UmH nH ph u 'hUA h 0J CJ OJ QJ mH nH u#hUA h CJ OJ QJ mH nH u0j hUA h 0J CJ OJ QJ UmH nH u &hUA h CJ OJ QJ mH nH u /j hUA h CJ OJ QJ UmH nH u2j} hUA h CJ OJ QJ UmH nH u5 6 7 8 @ A B \ ] ^ _ ` a b c d ˲랆lZZ:랆 >j hUA h >*B*CJ OJ QJ UmH nH ph u #hUA h CJ OJ QJ mH nH u2jq hUA h CJ OJ QJ UmH nH u /j hUA h CJ OJ QJ UmH nH u&hUA h CJ OJ QJ mH nH u 0j hUA h 0J CJ OJ QJ UmH nH u >j hUA h >*B*CJ OJ QJ UmH nH ph u 'hUA h 0J CJ OJ QJ mH nH u
!
"
#
$
%
&
Һ캡{{[{A캡{ 2je hUA h CJ OJ QJ UmH nH u >j hUA h >*B*CJ OJ QJ UmH nH ph u 'hUA h 0J CJ OJ QJ mH nH u#hUA h CJ OJ QJ mH nH u0j hUA h 0J CJ OJ QJ UmH nH u /j hUA h CJ OJ QJ UmH nH u2jk hUA h CJ OJ QJ UmH nH u &hUA h CJ OJ QJ mH nH u&
B
C
D
E
ٹٌtZtt:ٌ >j hUA h >*B*CJ OJ QJ UmH nH ph u 2j_ hUA h CJ OJ QJ UmH nH u /j hUA h CJ OJ QJ UmH nH u&hUA h CJ OJ QJ mH nH u 0j hUA h 0J CJ OJ QJ UmH nH u >j hUA h >*B*CJ OJ QJ UmH nH ph u 'hUA h 0J CJ OJ QJ mH nH u#hUA h CJ OJ QJ mH nH u
! C D E _ ` a b c d e ӹ砎zzZz@砎 2jS hUA h CJ OJ QJ UmH nH u >j hUA h >*B*CJ OJ QJ UmH nH ph u 'hUA h 0J CJ OJ QJ mH nH u#hUA h CJ OJ QJ mH nH u0j hUA h 0J CJ OJ QJ UmH nH u 2jY hUA h CJ OJ QJ UmH nH u &hUA h CJ OJ QJ mH nH u /j hUA h CJ OJ QJ UmH nH u e f g ӡӍu[uu; >j hUA h >*B*CJ OJ QJ UmH nH ph u 2jM hUA h CJ OJ QJ UmH nH u /j hUA h CJ OJ QJ UmH nH u&hUA h CJ OJ QJ mH nH u >j hUA h >*B*CJ OJ QJ UmH nH ph u #hUA h CJ OJ QJ mH nH u'hUA h 0J CJ OJ QJ mH nH u0j hUA h 0J CJ OJ QJ UmH nH u : ; < = L M N h i j k l ץzzZ@ 2jA
hUA h CJ OJ QJ UmH nH u >j hUA h >*B*CJ OJ QJ UmH nH ph u #hUA h CJ OJ QJ mH nH u0j hUA h 0J CJ OJ QJ UmH nH u 2jG hUA h CJ OJ QJ UmH nH u /j hUA h CJ OJ QJ UmH nH u&hUA h CJ OJ QJ mH nH u 'hUA h 0J CJ OJ QJ mH nH u l m n o p u[uu 2j; hUA h CJ OJ QJ UmH nH u /j hUA h CJ OJ QJ UmH nH u&hUA h CJ OJ QJ mH nH u >j
hUA h >*B*CJ OJ QJ UmH nH ph u 'hUA h 0J CJ OJ QJ mH nH u#hUA h CJ OJ QJ mH nH u0j hUA h 0J CJ OJ QJ UmH nH u
8
9
:
;
A
B
C
]
dzm[dz[;dz >j hUA h >*B*CJ OJ QJ UmH nH ph u #hUA h CJ OJ QJ mH nH u2j5 hUA h CJ OJ QJ UmH nH u /j hUA h CJ OJ QJ UmH nH u&hUA h CJ OJ QJ mH nH u 'hUA h 0J CJ OJ QJ mH nH u0j hUA h 0J CJ OJ QJ UmH nH u >j hUA h >*B*CJ OJ QJ UmH nH ph u]
^
_
`
a
b
c
d
e
κΡ{{[{κAκΡ{ 2j) hUA h CJ OJ QJ UmH nH u >j
hUA h >*B*CJ OJ QJ UmH nH ph u 'hUA h 0J CJ OJ QJ mH nH u#hUA h CJ OJ QJ mH nH u0j hUA h 0J CJ OJ QJ UmH nH u &hUA h CJ OJ QJ mH nH u /j hUA h CJ OJ QJ UmH nH u2j/
hUA h CJ OJ QJ UmH nH u
) * + , 8 9 : ˲랆lZZ:랆 >j hUA h >*B*CJ OJ QJ UmH nH ph u #hUA h CJ OJ QJ mH nH u2j# hUA h CJ OJ QJ UmH nH u /j hUA h CJ OJ QJ UmH nH u&hUA h CJ OJ QJ mH nH u 0j hUA h 0J CJ OJ QJ UmH nH u >j hUA h >*B*CJ OJ QJ UmH nH ph u 'hUA h 0J CJ OJ QJ mH nH u : T U V W X Y Z [ \ x y z { Һ캡{{[{A캡{ 2j hUA h CJ OJ QJ UmH nH u >j hUA h >*B*CJ OJ QJ UmH nH ph u 'hUA h 0J CJ OJ QJ mH nH u#hUA h CJ OJ QJ mH nH u0j hUA h 0J CJ OJ QJ UmH nH u /j hUA h CJ OJ QJ UmH nH u2j hUA h CJ OJ QJ UmH nH u &hUA h CJ OJ QJ mH nH u / 0 1 3 4 5 6 7 8 T U V W h i ٹٌtZtt:ٌ >j hUA h >*B*CJ OJ QJ UmH nH ph u 2j hUA h CJ OJ QJ UmH nH u /j hUA h CJ OJ QJ UmH nH u&hUA h CJ OJ QJ mH nH u 0j hUA h 0J CJ OJ QJ UmH nH u >j hUA h >*B*CJ OJ QJ UmH nH ph u 'hUA h 0J CJ OJ QJ mH nH u#hUA h CJ OJ QJ mH nH u i j ӹ砎zzZz@砎 2j hUA h CJ OJ QJ UmH nH u >j hUA h >*B*CJ OJ QJ UmH nH ph u 'hUA h 0J CJ OJ QJ mH nH u#hUA h CJ OJ QJ mH nH u0j hUA h 0J CJ OJ QJ UmH nH u 2j hUA h CJ OJ QJ UmH nH u &hUA h CJ OJ QJ mH nH u /j hUA h CJ OJ QJ UmH nH u M ^ t 1 / <
&