Limiting lines per page and fixed length table in XML/BI Publisher reports

In most master detail report there is a need to display the master/header lines followed by the child/detail lines. For example, Invoice report, Dunning Letter, Customer Statement, etc. In all these reports the number of detail lines that can be displayed on a single page needs to be restricted so that the report look is consistent across all pages.

There are solutions available at various forums but I wish to share the solution with my own logic.

The requirement:

Say if your report has 36 lines of data and you requirement is to display only 30 lines per page, then the report should display only 30 lines in the first page and the remaining 6 lines must appear in the next page. Also, the second page must have the table size fixed, meaning the table must end at the end of the page and not just after displaying the remaining lines.

Look at the screenshots shown below to get a glimpse. The first screenshot shows that only 30 lines have been printed. The second screenshot shows that the second page has printed the rest of the lines but the table does not end immediately. The table extends till the end of the page or in other words till it can fill 30 lines.

Let us divide this into stages.

In stage 1, we shall discuss about the declaration of variables that are required to put this logic together.

In stage 2, we shall discuss about limiting lines per page which means page will be broken once the line limit is reached in that particular page.

In stage 3, we shall discuss about extending the table based on the lines printed in the last page so that table goes till the end of the last page.


Stage 1: Variable declaration

In this stage, we shall look at the variables required. Let us simplify this logic. All we need is a few variables for calculation of number of lines and lines per page.

lines_page” variable is needed to store the number of lines that we are supposed to display per page. This will be the line limit.

Counter” variable is needed to store the position of the current line that is being printed.

tot_lines” is the count of all the lines in the xml data for that particular header. The count function on “.//G_LINE” gives the count of the lines in that particular header.

Remainder” variable is needed to store the count of lines for the last page to extend the table.

The below commands are used to create a few variable.

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

<?xdoxslt:set_variable($_XDOCTX, ‘lines_page’, 31)?>

<?xdoxslt:set_variable($_XDOCTX, ‘tot_lines’, count(.//G_LINE))?>

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

The above variables in PLSQL would be:

Counter := 0;

Lines_page := 31;

Tot_lines := count(G_LINE);

Remainder:= 0;

We shall see the use of these variables as we go further. The above code can be put in an XML tag as well like this var

Stage 2: Limiting lines per page

In this stage, the task is to allow the lines to be printed till 30 lines as per the “lines_page” value, then go into the next page using page break.

We iterate the variable ‘counter’ for each line, meaning the value of the variable will be incremented by one thus serving as a pointer of the current record.

So when this counter reaches the line limit per page say 30, we need to call a page break.

So the first variable will be ‘counter’ which will be initialized to zero at the beginning of the report.

Then when the FOR loop begins in the template use the below command to increment the ‘counter’ value.

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

The above command will set value of ‘counter’ as ‘counter+1′. This in PL/SQL would be “counter: = counter + 1;”

Now we need to put a check which shall break the page if 30 lines have been printed. The lines_page variable has value 31 which means it holds the value for “line per page plus 1″.

<?if@inlines:xdoxslt:get_variable($_XDOCTX,’counter’) != xdoxslt:get_variable($_XDOCTX,’tot_lines’)?>
<?if@inlines:position() mod xdoxslt:get_variable($_XDOCTX, ‘lines_page’) = 0?>

The above “IF” condition basically in PLSQL is

IF counter <> tot_lines THEN

IF current_position mod lines_page = 0 THEN

The mod operator returns the remainder divisor and dividend’s division operation.

The first “IF” condition makes sure that we are not breaking the page at the end of the report unnecessarily and the second “IF” condition checks if the report has BI processor has reached the line limit for the page.

Once the limit is reached the above IF condition will return TRUE and we then call the page break.

The usual command for PAGE BREAK is <?split-by-page-break:?> but this cannot be used inside and IF condition hence we have to call the command from another template which can be defined in the same layout but after the FOR loop of all the groups is over.

Below is an example of defining a template and calling it elsewhere.

<?template:breaking?>

<?split-by-page-break:?>

<?end template?>

The above template can be called using CALL command as shown below.

<?call:breaking?>

This command can be used in the above mentioned if condition so that the page can break when the IF condition turn TRUE, in other words when the BI processor reaches the line limit per page.

Now the page breaks and goes into second page and the first page will look as shown below.

You can see that the table does not have the bottom line as the table has not yet closed but just gone into second page. To make this table appear as closed you can use a table cell with all but bottom border as invisible as shown below.

Include this cell in the template “breaking” before the page break command as shown below.

<?template:breaking?>

<?split-by-page-break:?>

<?end template?>

The above template can be used for creating a page break; this will not only break the page but also make the table have a bottom line. The end result of this command is as shown below.

You can see that the table has the bottom line as well even though it has not yet actually closed.

So the code that has been used so far turns out to be

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

<?if@inlines:xdoxslt:get_variable($_XDOCTX,’counter’) != xdoxslt:get_variable($_XDOCTX,’tot_lines’)?>

<?if@inlines:position() mod xdoxslt:get_variable($_XDOCTX, ‘lines_page’) = 0?><?call:breaking?><?end if?><?end if?>

The above code can be put in an XML tag for ease.

The <?end if?> commands are given to close the “IF” conditions opened earlier.

Remember, the above code must be put after the FOR loop has begun for the lines.

Once the all the lines are displayed (say 36 lines which include one page break that occurs at line 30), the table is closed as shown below (after line36).


Stage 3: Extending table to subsequent pages

As you can see from above image, the table ends after the lines are displayed but we are supposed to extend the table till the end of the page as shown below.

So in this stage we shall look at extending the table till the end of the page.

This means we have to make the template extend as if we are still displaying the data inside the table and the table must keep going down.

You can see from above image that the 6 lines are displayed which means another 24 lines of data can be placed till the table reaches the end of the page.

It means all we need to do is put a line break 24 (30 minus 6) times so that the table reaches the end.

But this has to be done only when all the lines have been displayed. The below condition will help evaluate if we have reached end of all the lines.

<?if@inlines:xdoxslt:get_variable($_XDOCTX,’i’)=xdoxslt:get_variable($_XDOCTX,’tot_lines’)?>

Remember we had defined a variable tot_lines that will hold the count of all the lines. Well once the counter matches tot_lines value we can safely assume that the all the lines have been printed.

The above condition in PL/SQL is

IF counter = tot_lines THEN

Now that we are at the end of the line data, we now need to calculate how many line breaks we need to give.

The below command will give us that value. Remember we had defined a variable named “remainder“. This variable will hold the number of line breaks that we need to give to extend the table till the end.

<?xdoxslt:set_variable($_XDOCTX,’remainder’,xdoxslt:get_variable($_XDOCTX,’lines_page’) – (xdoxslt:get_variable($_XDOCTX,’tot_lines’) mod xdoxslt:get_variable($_XDOCTX,’lines_page’)))?>

The above command in PLSQL is

Remainder := lines_page – (tot_lines mod lines_page)

The above command basically returns the count of lines that could have been printed if the lines in the XML had not ended. The second page displayed 6 lines, meaning more 24 lines could have been printed. So you can call 24 line breaks to extend the table.

Now all you need to do is call a line break inside this IF condition but this is feasible only if you call it through a template similar to the PAGE BREAK that we are doing at the end of 30 lines.

Below is the code to call a template with a line break.

<?template:looping?>

<?end template?>

In the above template command, we have nothing but a line break which includes a space right after the <?template:looping?>command. The below example will simplify the “looping” template.

<?template:looping?><<space>><<line break>> <?end template?>

The above command explains the “looping” template defined. Giving the space is mandatory otherwise the line break will keep wrapping up to the previous line and the table will not get extended. When the space is given, it is treated as a character and the wrapping is avoided.

Now the template “looping” is to be called 24 times to extend the table till the page end.

The below command should do the job.

<?for-each:xdoxslt:foreach_number($_XDOCTX,1,xdoxslt:get_variable($_XDOCTX,’remainder’),1)?>

<?call:looping?><?end for-each?>

<?end if?>

The above for-each command in PLSQL is

FOR counter in 1..remainder LOOP

This means there will be a loop for 24 times and in this loop, the “looping” template is called.

The line break happens 24 times and table is extended till the end of the page.

If the second page contains only one line, then the “remainder” variable will have 29 as value and the line break would happen 29 times.

The <?end if?> command obviously closes the IF condition that was opened earlier.

So the code for extending the table would look like this:

<?if@inlines:xdoxslt:get_variable($_XDOCTX,’i’)=xdoxslt:get_variable($_XDOCTX,’tot_lines’)?>

<?xdoxslt:set_variable($_XDOCTX,’remainder’,xdoxslt:get_variable($_XDOCTX,’lines_page’) – (xdoxslt:get_variable($_XDOCTX,’tot_lines’) mod xdoxslt:get_variable($_XDOCTX,’lines_page’)))?>

<?for-each:xdoxslt:foreach_number($_XDOCTX,1,xdoxslt:get_variable($_XDOCTX,’remainder’),1)?>

<?call:looping?><?end for-each?>

<?call:pageline?><?end if?>

The above logic when incorporated in a template should give desired results as shown in the screenshots earlier.

Advertisements

7 thoughts on “Limiting lines per page and fixed length table in XML/BI Publisher reports

  1. Thanks for great article, but what Pageline template is doing, that is missing, it will be so kind of you if you can provide details about Pageline template too.

  2. Thanks for great article, but what Pageline template is doing, that is missing (). it will be so kind of you if you can provide details about Pageline template too.

    i am using the exact code detailed above and it worked but page break inserts an additional blank line on the start of each page.

  3. I was looking for something for a very critical invoice report… i searched like hell for an idea… n this article turned out to be the saviour… Kudos to the developer. Can not thank yu enough fr one or two great ideas yu shared that allowed me to complete the task… Much respect to yu. keep inspiring.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s