Tuesday, May 14, 2013

BI / XML Publisher - Fixed number of records per page

Requirement
* Display fixed number of records per page.
* On each page, display header
* Display footer on last page
* In case if footer is spanning across pages, move one record(last record) to next page and display footer at bottom of the page.
Fig1. Pages others than last one

Fig2. Display Footer on last page


Fig 3. Special condition where footer spans across pages

Fig 4. To handle footer spanning across pages, move last record to next page and then display footer


Solution
Download complete template
code
(open it and save it as rtf)
Download sample data
XML Data is of form
<ROOT>
 <A>
  <B1>lineB11</B1>
  <B2>lineB12</B2>
  <B3>lineB13</B3>
  <B4>lineB14</B4>
 </A>
..
..
</ROOT>

Lets quickly freshen up our XML Publisher Reports knowledge

How to declare variables?
XSL variable declaration
<? variable : a ; number(1) ?>
XML Publisher variable declaration
<? xdoxslt:set_variable($_XDOCTX,'i',1) ?>

How to use conditional logic in template?
 <?if@inlines: [condition] ?>  [value]  <? end if ?>
Example
<?if@inlines: $a = 1 ?>   Text    <?end if?>
@Inline is used to print text on the same line, else its displayed on next line

How to use Looping constructs?
Looping for particular XML tag

for-each:current_group()
Looping n number of times<? for-each@inlines : xdoxslt:foreach_number ( $_XDOCTX,1,3,1) ?>This is an Example

<?end for-each?>


"This is and Example" text will be printed 3 times in same line(notice @inline).

Parameters for foreach_number function are same like C programming for loop - initial value, end value and increment value

How to use Page Breaks?Below tags work only in Word form field. Other declarations can be entered like normal text, but this tags should be put in Word Form field tag.

<xsl:attribute name="break-after">page
<xsl:attribute name="break-before">page

Use this inside inline IF condition to give appropriate page breaks.


Now, Lets solve our problem.

1. Decide Number of lines per page : There is no formula that works for all scenarios. Number of lines that need to be displayed per page has to be decided based on
header size,
font size and
other factors.

Create a simple for-each loop in table to find out how many rows a page can accommodate and assign that value to the variable nlpp.

Lets say after the header which I have decided(refer Fig1) a page can accommodate 46 records. Declare number of lines per page in a a variable nlpp

<?variable:nlpp;number(46)?>

2. Calculate total number of lines : Total number of lines coming in data can be calculated by using count() function on particular node. Assign that value to variable TOTLINES

Consider that records are coming inside A node(refer to sample data above), like for 4 records there will be 4 A nodes present in data.

<?xdoxslt:set_variable ($_XDOCTX,'TOTLINES',count(//A))?>

this will count number of A tags coming in data, and assign that value to TOTLINES variable.

3. Page Break after every nth row : After every 46th record we want to give a page break. We decided number of records that we want per page and based on that we have to insert page breaks.

Using for-each:tag, loop through all the records

<?for-each:A?> <?B1?> <end for-each>

To keep track of count of records, we will increment variable i value using following loop

<?for-each:A?>
<?xdoxslt:set_variable($_XDOCTX,'i',xdoxslt:get_variable($_XDOCTX,'i')+1)?>
<?B1?>
<?end for-each?>

It gets the current value of i using get variable, adds 1 to that and sets new value of i.

After each nlppv(same as variable nlpp with value 46) number of records we want page break.

Check value of counter variable. When 47the record is reached give page break using xsl break-before and reset the counter variable value to 1.

<?if@inlines : xdoxslt:get_variable($_XDOCTX,'i') = xdoxslt:get_variable($_XDOCTX,'nlppv') + 1 ?>
<?xsl:attribute name="break-before"> page </xsl:attribute>
<?xdoxslt:set_variable($_XDOCTX,'i',1)?>
<?end if ?>

Now we have code in place to display each record column value and code to make sure that after every nth page a page break is inserted.

Just add Footer after the loop condition and when all records are processed, a footer will be displayed after it. But we need to display footer section at bottom of the page, so we need to add spaces based on some logic.

Decide Number of Blank Lines to add to display footer at bottom of the page.
The same way we decided value of variable nlpp in step 1, we need to run some test and check how many records a page can accommodate along with footer section().

<!--?xdoxslt:set_variable($_XDOCTX,'ilast',70)?>
The last IF EF condition is outside of main loop to display records.

If number of records on the page(value of variable i after loop is complete) is less that the total number of records that the last page can accommodate along with footer section(ilast value), we need to insert blank lines.

Following code will display blank lines to make sure footer appears at bottom of the page.
<?if:xdoxslt:get_variable($_XDOCTX,’i’)
<  (xdoxslt:get_variable($_XDOCTX,’ilast’) - 1)?>
<?for-each:xdoxslt:foreach_number($_XDOCTX,1,xdoxslt:get_variable($_XDOCTX,'ilast') - xdoxslt:get_variable($_XDOCTX,'i'),1)?>


4. Special Scenario
that need to be handled
Footer coming across pages:  This scenario is for last page alone. We need to decide the number of records that can come in single page along with footer, without footer breaking across page.

Complete Code Explanation

Variable Declarations
<?variable:nlpp;number(46)?> -- number of lines that should be displayed per page before page break

<!--?xdoxslt:set_variable($_XDOCTX, 'page', 1)?> -- keeps track of page numbers

<!--?xdoxslt:set_variable($_XDOCTX, 'i', 0)?>        --- variable used for looping through all records

<!--?xdoxslt:set_variable ($_XDOCTX,'nlast', (xdoxslt:get_variable($_XDOCTX,'TOTLINES') mod $nlpp)) ?>         --calculate number of lines for last page

<!--?xdoxslt:set_variable($_XDOCTX, 'nlppv', $nlpp)?>     --xsl variable nlpp declared above just for convenience of using short variable name

<!--?xdoxslt:set_variable ($_XDOCTX,'tpage', ceiling (xdoxslt:get_variable($_XDOCTX,'TOTLINES') div $nlpp)) ?>    --calculate total number of pages based on number of records per page calculation

<!--?xdoxslt:set_variable ($_XDOCTX,'skip',0)?>
skip is used to handle the special scenario mentioned above. To prevent footer from coming across pages, move footer on next page along with one last record.

<!--?xdoxslt:set_variable($_XDOCTX,'ilast',70)?>
-- ilast is used to decide on number of spaces that should be displayed to display footer at bottom of page. ilast is number of records that can be displayed on a page with footer coming on that page.

<!--?if@inlines:xdoxslt:get_variable($_XDOCTX,'nlast') = 0?>
<!--?xdoxslt:set_variable($_XDOCTX,'nlast',$nlpp)?>
<?end if?>

If number of records for last page is more than what last page can accommodate along with footer then we need to page break on last record.
<!--?if@inlines:xdoxslt:get_variable($_XDOCTX,'nlast') >= xdoxslt:get_variable($_XDOCTX,'ilast')?>
<!--?xdoxslt:set_variable($_XDOCTX,'skip',1)?
<?end if?>

Looping through records
<!--?xdoxslt:set_variable($_XDOCTX,'i',xdoxslt:get_variable($_XDOCTX,'i')+1)?>

for special condition give page break just after second last record
<!--?if@inlines:xdoxslt:get_variable($_XDOCTX,'tpage') = xdoxslt:get_variable($_XDOCTX,'page')
and  xdoxslt:get_variable($_XDOCTX,'skip') =1
and  xdoxslt:get_variable($_XDOCTX,'i') = xdoxslt:get_variable($_XDOCTX,'skip_at')?>

<xsl:attribute name="break-after">page        

<?end if?>

--give page break after fixed number of lines per page
<!--?if@inlines:xdoxslt:get_variable($_XDOCTX,'i') = xdoxslt:get_variable($_XDOCTX,'nlppv') +1 ?>
<xsl:attribute name="break-before">page
<!--?xdoxslt:set_variable($_XDOCTX,'i',1)?>riable($_XDOCTX,'page')+1)?>
<?end if?>

--spaces to display footer at bottom of page
<!--?if:xdoxslt:get_variable($_XDOCTX,’i’)< (xdoxslt:get_variable($_XDOCTX,’ilast’) - 1)?>
<!--?for-each:xdoxslt:foreach_number($_XDOCTX,1,xdoxslt:get_variable($_XDOCTX,'ilast') -xdoxslt:get_variable($_XDOCTX,'CR'),1)?>
<?end for-each?>
<?end if?>

4 comments:

  1. This comment has been removed by a blog administrator.

    ReplyDelete
  2. Can you please provide rtf file for the same.It will be very much helpful.

    ReplyDelete
  3. Please attach rtf file again
    tks

    ReplyDelete