You are here: BI Publisher Displaying items by tag: RTF Template
Displaying items by tag: RTF Template
Tuesday, 07 August 2012 15:10

RTF Template : Working with variables

 Let’s see how we can use the variables to store temporary data or use for calculation.  This is achieved using  “xdoxslt:” function. These are the BI Publisher extension of standard xslt functions.  

Use xdoxslt:set_variable () function to set /initialize the variable  and xdoxslt:get_variable() function to get the variable value.  $_XDOCTX is the System variables to set the XDO Context.

 

 

/*initialize a variables*/

<?xdoxslt:set_variable($_XDOCTX, ‘counter’, 0)?>

/*update the variable’s value by adding the current value to MY_CNT, which is XML element */

<?xdoxslt:set_variable($_XDOCTX, ‘counter’, xdoxslt:get_variable($_XDOCTX, ‘counter’) + MY_CNT)?>

/* accessing the variables */

<?xdoxslt:get_variable($_XDOCTX, ‘counter’)?>

 

/*Working in a loop*/

<?xdoxslt:set_variable($_XDOCTX, ‘counter’, 0)?>

<?for-each:G1?>

/*increment the counter*/

<?xdoxslt:set_variable($_XDOCTX, ‘counter’, xdoxslt:get_variable($_XDOCTX, ‘counter’) + 1)?>

<?end for-each?>

<?xdoxslt:get_variable($_XDOCTX, ‘counter’)?>

 

Hope this help in understanding variable gimmicks.

Need help on RTF template design or BI Publisher implementation, please contact This e-mail address is being protected from spambots. You need JavaScript enabled to view it

 

  

Published in BI Publisher
Tuesday, 07 August 2012 14:33

How to re-group XML data in RTF Template

 If we need the grouping, it is always advisable to generate the XML as per the required hierarchy. This makes the RTF design simple and perform better.  But there are situation when we do not have control over XML structure and need to work on flat XML.  Lets see how we can create multiple nested grouping in RTF template.

I am using  following simple flat XML which is generated using scott.emp and scott.dept table.

Lets first design the simple table with default group

It generates simple listing report.

 

Lest add a group based on Depno. This is achieved by adding following loop

  <?for-each-group:ROW;./DEPTNO?>  --- <?end for-each?> and iterate the detail record within the current group.

This introduce the Dept Group ..
Let’s add another nested group for Job within the department group.
Hope this will help to design those nasty nested groups based on flat XML date.
Need help on RTF template design or BI Publisher implementation, please contact This e-mail address is being protected from spambots. You need JavaScript enabled to view it
Published in BI Publisher

Using some arithmetic along with the mod() and floor() functions, you can calculate the quarter of a fiscal year based on a given date. The calculation we will use assumes that the fiscal year begins on the first of the month, and not some other day. We will first demonstrate this for a fiscal year that starts in January, and then show how to modify the formula for any given start month.


Sample XML 

<?xml version="1.0" encoding="iso-8859-1"?>

<List_Date>

   <G_Date>

     <Date>01/25/2001</Date>

   </G_Date>

   <G_Date>

     <Date>02/25/2001</Date>

   </G_Date>

   <G_Date>

     <Date>03/25/2001</Date>

   </G_Date>

   <G_Date>

     <Date>04/25/2001</Date>

   </G_Date>

   <G_Date>

     <Date>05/25/2001</Date>

   </G_Date>

   <G_Date>

     <Date>06/25/2001</Date>

   </G_Date>

   <G_Date>

     <Date>07/25/2001</Date>

   </G_Date>

   <G_Date>

     <Date>08/25/2001</Date>

   </G_Date>

   <G_Date>

     <Date>09/25/2001</Date>

   </G_Date>

   <G_Date>

     <Date>10/25/2001</Date>

   </G_Date>

   <G_Date>

     <Date>11/25/2001</Date>

   </G_Date>

   <G_Date>

     <Date>12/25/2001</Date>

   </G_Date>

</List_Date>

 RTF Design

Output:


Field Browser:

 

Adjust the formula for a different starting month Based on requirement

If you have a fiscal year with a starting month other than January, the formula is easily modified to account for this. You will note the highlighted part of the formula : floor(mod(((substr(Date,1,2) +11) div 3),4)) + 1. This 11 should be decremented for each successive month. So if your fiscal year begins in February, then you would replace it with 10. March would be 9, and so on until you reach 0 for December.
Once you've adjusted the formula to calculate the correct quarter, you need to update the string in the Calculated Value control to be "Feb-AprMay-JulAug-OctNov-Jan" for a fiscal year starting in February, and so on to "Dec-FebMar-MayJun-AugSep-Nov" for a fiscal year starting in December.

"If you found this article useful, please rate the same"

If you need any assistance in BI Publisher implementation or RTF Template design, we can

help you out. please send your query to   This e-mail address is being protected from spambots. You need JavaScript enabled to view it

Published in BI Publisher

As we display the measures as columns in a table, we can also modify this display structure in output to display these measure columns as rows.


Consider below XML

<GROUP_PERCENTAGE>

    <PERCENTAGE>

                <DEPARTMENT>CSE</DEPARTMENT>

                <CRETERIA>PASSPERCENTAGE</CRETERIA>

                <MALE>70</MALE>

                <FEMALE>30</FEMALE>

   </PERCENTAGE>

      <PERCENTAGE>

                <DEPARTMENT>IT</DEPARTMENT>

                <CRETERIA>PASSPERCENTAGE</CRETERIA>

                <MALE>65</MALE>

                <FEMALE>35</FEMALE>

   </PERCENTAGE>

        <PERCENTAGE>

                <DEPARTMENT>ECE</DEPARTMENT>

                <CRETERIA>PASSPERCENTAGE</CRETERIA>

                <MALE>55</MALE>

                <FEMALE>45</FEMALE>

   </PERCENTAGE>

     <PERCENTAGE>

                <DEPARTMENT>EEE</DEPARTMENT>

                <CRETERIA>PASSPERCENTAGE</CRETERIA>

                <MALE>75</MALE>

                <FEMALE>25</FEMALE>

   </PERCENTAGE>

   <PERCENTAGE>

                <DEPARTMENT>CSE</DEPARTMENT>

                <CRETERIA>FAILPERCENTAGE</CRETERIA>

                <MALE>30</MALE>

                <FEMALE>70</FEMALE>

   </PERCENTAGE>

 <PERCENTAGE>

                <DEPARTMENT>IT</DEPARTMENT>

                <CRETERIA>FAILPERCENTAGE</CRETERIA>

                <MALE>35</MALE>

                <FEMALE>65</FEMALE>

   </PERCENTAGE>

<PERCENTAGE>

                <DEPARTMENT>ECE</DEPARTMENT>

                <CRETERIA>FAILPERCENTAGE</CRETERIA>

                <MALE>45</MALE>

                <FEMALE>55</FEMALE>

   </PERCENTAGE>

<PERCENTAGE>

               <DEPARTMENT>EEE</DEPARTMENT>

               <CRETERIA>FAILPERCENTAGE</CRETERIA>

               <MALE>25</MALE>

               <FEMALE>75</FEMALE>

  </PERCENTAGE>

</GROUP_PERCENTAGE>

In general, when we build an RTF template for the above xml and the output is as below 

 

In order to display measures as rows in a table structure for easy analysis we should change the fields of table structure as below:

 

The above one is the table structure to display the measures as rows in a table.Now you can observe the below output

 

Below are the form fields where we modified them for displaying in the above way

 

For :                <?for-each-group@column:PERCENTAGE;CRETERIA?>

To group the data by CRETERIA; and the @column context command to create a table column for each group (CRETERIA).

 CRETERIA:    <?CRETERIA?>

Placeholder for the CRETERIA element.

 End:                <?end for-each-group?>

Closes the for-each-group loop.

 Department:  <?for-each-group:PERCENTAGE;./DEPARTMENT?><?variable@incontext:G1;current-group()?><?DEPARTMENT?>

Begins the group to create a table row for each DEPARTMENT.

 MALE:            <?for-each-group@cell://PERCENTAGE;./CRETERIA?><?sum ($G1[(./CRETERIA=current()/CRETERIA)]/MALE)?><?end for-each-group?>

To group the data by CRETERIA; and the @cell context command to create a table cell for each group (CRETERIA).

Sums the sales for the current group (CRETERIA).

 FEMALE:       <?for-each-group@cell://PERCENTAGE;./CRETERIA?><?sum ($G1[(./CRETERIA=current()/CRETERIA)]/FEMALE)?><?end for-each-group?>

To group the data by CRETERIA; and the @cell context command to create a table cell for each group (CRETERIA).

Sums the sales for the current group (CRETERIA).

 

"If you found this article useful, please rate the same"

If you need any assistance in BI Publisher implementation or RTF Template design, we can

help you out. please send your query to   This e-mail address is being protected from spambots. You need JavaScript enabled to view it

Published in BI Publisher
Wednesday, 14 December 2011 06:24

Working with XPath in RTF Template

X Path

  •   XPath is a syntax used for selecting parts of an XML document
  •   The way XPath describes paths to elements  is similar to the way an operating system describes paths to files
  •   XPath is almost a small programming language; it has functions, tests, and expressions
  •   XPath is a W3C standard
  •   XPath is not itself written as XML, but is used heavily in XSLT


Terminology

<INSTITUTE>

    <DEPARTMENT>

          <SECTION>

          </SECTION>

           <SECTION>

                <STUDENT>

                     <ROLL_NO> </ROLL_NO>

                      <MARKS>

                              <SUBJECT1></SUBJECT1>

                              <SUBJECT2></SUBJECT2>

                              <SUBJECT3></SUBJECT3>

                       </MARKS>

                </STUDENT>

          </SECTION>

      </DEPARTMENT>

</INSTITUTE>

 

  •   INSTITUTE is the parent of DEPARTMENT; DEPARTMENT is the parent of the two SECTIONS
  •   The two SECTIONS are the children of DEPARTMENT, and the STUDENT is the child of the second SECTION
  •   The two SECTIONS of the DEPARTMENT are siblings (they have the same parent)
  •   INSTITUTE, DEPARTMENT, and the second SECTION are the ancestors of the STUDENT
  •   The two SECTIONS, the STUDENT, and the ROLL_NO are the descendents of the DEPARTMENT

 

Paths

Operating system:

XPath:

/  = the root directory

/INSTITUTE  = the root element (if named INSTITUTE )

/users/dave/foo = the file named foo in dave in users

/INSTITUTE/DEPARTMENT/SECTION/STUDENT  = every STUDENT element in a SECTION in every DEPARTMENT in the INSTITUTE

foo  = the file named foo in the current directory

STUDENT  = every STUDENT element that is a child of the current element

. = the current directory

.  = the current element

.. = the parent directory

 .. = parent of the current element

/users/dave/* = all the files in /users/dave

/INSTITUTE/DEPARTMENT/SECTION/*  = all the elements in /INSTITUTE/DEPARTMENT/SECTION

 

Slashes

  •   A path that begins with a  /  represents an absolute path, starting from the top of the document

  Example:  /email/message/header/from

  Note that even an absolute path can select more than one element

  A slash by itself means “the whole document”

  •   A path that does not begin with a  represents a path starting from the current element

  Example:  header/from

  •   A path that begins with  //  can start from anywhere in the document

  Example:  //header/from selects every element from that is a child of an element header

  This can be expensive, since it involves searching the entire document

Brackets and last()

  •   A number in brackets selects a particular matching child (counting starts from 1, except in Internet Explorer)

  Example:  /institute/department[1] selects the first department of the institute

  Example:  //student/roll_no[2] selects the second roll_no of every student in the XML document

  Example: //department/section[1]/student[2]

  Only matching elements are counted; for example, if a department has both sections and student, the latter are ignored when counting student

  •    The function last() in brackets selects the last matching child

  Example: /institute/department/section[last()]

  • You can even do simple arithmetic

  Example: /institute/department/section[last()-1]

 

Stars

  •   A star, or asterisk, is a “wild card”--it means “all the elements at this level”

  Example: /institute/department/section/* selects every child of every section of every department in the institute

  Example: //department/* selects every child of every department (sections, students, etc.)

  Example: /*/*/*/student selects every student that has exactly three ancestors

  Example: //*  selects every element in the entire document

Arithmetic expressions

           +                             add

           -                              subtract

           *                             multiply

          div                          (not /) divide

           mod                       modulo (remainder)

 

     Equality tests

  =                      “equals”          (Notice it’s not  ==)

!=                     “not equals”

But it’s not that simple!

             value = node-set  will be true if the node-set contains any node with a value that matches value

             value != node-set  will be true if the node-set contains any node with a value that does not match value

Hence,

             value = node-set  and  value != node-set  may both be true at the same time!

     Other boolean operators

and                  (infix operator)

or                     (infix operator)

              Example: count = 0 or count = 1

not()                 (function)

The following are used for numerical comparisons only:

<            “less than”                                  Some places may require &lt;

<=          “less than or equal to”             Some places may require &lt;=

 >            “greater than”                             Some places may require &gt;

         >=          “greater than or equal to”        Some places may require &gt;=

 

Some XPath functions

  XPath contains a number of functions on node sets, numbers, and strings; here are a few of them:

  count(elem) counts the number of selected elements

  Example: //section[count(student)=1] selects section with exactly two students children


  name() returns the name of the element

  Example: //*[name()='student'] is the same as //student


  starts-with(arg1, arg2) tests if arg1 starts with arg2

  Example: //*[starts-with(name(), 'stu']


  contains(arg1, arg2) tests if arg1 contains arg2

  Example: //*[contains(name(), 'tud']

 Example:

Here is the example of an institute which has one department and a few students in it.

<INSTITUTE>

<DEPARTMENT>

     <DEPARTMENT_NAME>COMPUTER SCIENCE ENGINEERING</DEPARTMENT_NAME>

          <SECTION>

                <STUDENT>

                     <ROLL_NO>1201</ROLL_NO>

                      <MARKS>

                              <SUBJECT1>45</SUBJECT1>

                              <SUBJECT2>55</SUBJECT2>

                              <SUBJECT3>65</SUBJECT3>

                      </MARKS>

                </STUDENT>

                 <STUDENT>

                     <ROLL_NO>1205</ROLL_NO>

                      <MARKS>

                              <SUBJECT1>56</SUBJECT1>

                              <SUBJECT2>48</SUBJECT2>

                              <SUBJECT3>58</SUBJECT3>

                      </MARKS>

                </STUDENT>

                <STUDENT>

                     <ROLL_NO>1257</ROLL_NO>

                      <MARKS>

                              <SUBJECT1>51</SUBJECT1>

                              <SUBJECT2>49</SUBJECT2>

                              <SUBJECT3>53</SUBJECT3>

                      </MARKS>

                </STUDENT>

          </SECTION>

     </DEPARTMENT>

</INSTITUTE>

 

Now let’s display all the student details using a table with department name, roll number and marks.

1.JPG


Output:

 

2.JPG

 


Why is the department name missing?

When we say <?for-each:STUDENT?> the control is at <STUDENT> , (as you can see in the below diagram) but department name is not the child of student so we have to move the cursor to appropriate tag using xpath.

6.JPG

3.JPG


Output:


4.JPG


Here we used <?../../DEPARTMENT_NAME?> instead of <?DEPARTMENT_NAME?> so that the control jumps to two levels up in hierarchy and displays the DEPARTMENT NAME.

7.JPG


Now, let’s calculate totals marks secured by each student. To do so, add a column for Total and use <?sum(./MARKS/*)?> syntax to calculate TOTAL.

<?sum(./MARKS/*)?> syntax implies that we are using sum function and calculating all the children in marks

8.JPG

Output:

9.JPG

"If you found this article useful, please rate the same"

Published in BI Publisher
Saturday, 10 December 2011 06:40

Working with RTF Sub Templates

RTF Sub Template

A sub template is a piece of formatting functionality that can be defined once and used multiple times within a single layout template or across multiple layout template files. This piece of formatting can be an RTF file format or an XSL file format. RTF sub templates are easy to design as you can use Microsoft Word native features. XSL sub templates can be used for complex layout and data


Creating an RTF Sub template File

 

An RTF sub template consists of one or more <?template?> definitions. Following are the steps to apply RTF sub template in RTF template. Open an RTF file. Use the following syntax to create a template
1.JPG
If more templates are required, they can all be defined in the same way. Sequence of template definition within the sub template file does not matter.

Import Sub template RTF File

To import, save the Sub template in local system or we can use HTTP. Using HTTP sub-template needs to be on a web server and it should be accessible from other machines with HTTP. To use in a local system save the RTF sub template in your local drive. In my case it is saved in c:\sub_template\subtemplate.rtf.
Syntax for importing rtf
<?import:file:/sub_template/subtemplate.rtf?>

Calling a RTF Sub template without parameters from your Main Template

2.JPG
3.JPG
Output:

5.JPG
Call RTF Sub Template with parameters

6.JPG

4.JPG

Output:
7.JPG

The <?template?> can now be called similar to other template call; however, to handle parameters we will have to enclose the parameters between <?call@inlines?> and <?end call?> tags.
Note: 
Import statement is not required if sub template is defined in the RTF template file.  In this case, the sub template definition can be shared with other RTF templates.  You cannot conditionalize the import statement for the subtemplate file. Instead, you import one subtemplate file and conditionalize the call statement. You define multiple <?template?> options in the single subtemplate file.

Use of RTF Subtemplates

Multiple reports require the same header and footer content frequently. Hence, by using an RTF subtemplate, any global changes will be simplified and requires only updating the subtemplate instead of each individual layout.

 

"If you found this article useful, please rate the same"

Published in BI Publisher

The objective of this post is to explain the “sorting” capabilities available in the RTF. Many might already be aware of the standard BI Publisher syntax for sorting in the RTF template:

<?sort:VENDOR_NAME?>

 

 

The above construct sorts the data by the VENDOR_NAME in ascending order – an alphabetic sort is performed. However, what if the requirement was to sort on a number field. For example, if you want to sort by the VENDOR_NUMBER, then the above will produce incorrect results as an aphabetic sort would be applied to a numeric field. The following BIP construct can be used to specifc the order and the sort type:

<?sort:VENDOR_NUMBER;'ascending';data-type='number'?>

 

The above BIP construct performs a numeric sort on the vendor_number. That's rather simple to do. Let's say we would like to sort on a field but the field name is not known at design time. The field name exists in the xml data. Let's say the element P_SORT_BY specifies the field on which sorting must be done. So, if the value of P_SORT_BY is VENDOR_NAME, sorting must be performed on the field VENDOR_NAME. This can be acheived by using the following XSL:

<xsl:sort select=".//node()[local-name()=P_SORT_BY]" order="ascending" data-type="number"/>

Let's try to decipher the above xsl syntax. The above XSL has three parts – select, order and data-type. While order and data-type are self explanatory, the Xpath in the select actually does the magic. Let's try to understand this XPath in detail. The function node() returns an node. Specifying just node() will return all the nodes in the XML data. In order to filter, square brackets [] are used and the appropriate filter condition are specified inside. The call local-name() returns the name of the node. Therefore, the condition local-name()=P_SORT_BY will return all nodes whose name matches the value specified by P_SORT_BY.

 

The “order” and “data-type” fields can also use xdoxslt functions in them. Thus, if the sort type is known at run time, the following can be used to set the sort type at runtime:

<?xdoxslt:set_variable($_XDOCTX, ’sort_datatype’, value)?>

<xsl:sort select=".//node()[local-name()=P_SORT_BY]" order="ascending" data-type="{xdoxslt:get_variable($_XDOCTX, 'sort_datatype')}"/>

The above sorts by the field whose value is specified by the P_SORT_BY and the sorting type is decided based on the variable sort_datatype. Similarly, the order can also be obtained from a xdoxslt variable.
 

Published in BI Publisher