The Problem:
Recently I was tasked to build the report in PeopleSoft to generate the list of external examiners (yes, it is obviously an education institute) which will eventually be printed on to commercially available Labels. The labels would be pasted on to the question paper stack assigned to the designated exam hall for distribution.
As the de-facto choice of technology to accomplish something of this nature I went with XML Publisher a.k.a BI Publisher. What I thought was a simple report eventually turned out to be anything but simple to print the data across and then down....
The difficulty:
My Data source was a PS Query which had already done the necessary sorting with the query criteria.
In BI Publisher, the data within XML tags are printed one below the other using a For-loop in the form of a table. This in itself presented a challenge as the data would get printed vertically down in a single column. But I wanted multiple column output. Without thinking too much my mind immediately jumped into the idea of breaking the page into 4 columns which is an option in MS Word. When I printed the sample data I ended up with an output like this -
Recently I was tasked to build the report in PeopleSoft to generate the list of external examiners (yes, it is obviously an education institute) which will eventually be printed on to commercially available Labels. The labels would be pasted on to the question paper stack assigned to the designated exam hall for distribution.
As the de-facto choice of technology to accomplish something of this nature I went with XML Publisher a.k.a BI Publisher. What I thought was a simple report eventually turned out to be anything but simple to print the data across and then down....
The difficulty:
My Data source was a PS Query which had already done the necessary sorting with the query criteria.
In BI Publisher, the data within XML tags are printed one below the other using a For-loop in the form of a table. This in itself presented a challenge as the data would get printed vertically down in a single column. But I wanted multiple column output. Without thinking too much my mind immediately jumped into the idea of breaking the page into 4 columns which is an option in MS Word. When I printed the sample data I ended up with an output like this -
1 4 7 10
2 5 8 11
3 6 9 12
What I was actually looking to accomplish was to print the data in an "across down" manner. The same way we write things on a piece of paper (left to right, then down),
1 2 3 4
5 6 7 8
9 10 11 12
The next best option:
As I had developed cross tab/pivot reports previously, which is primarily used when the number of columns and rows are dynamic. In my situation, I had a fixed number of rows which had to be printed in a columnar fashion. After messing with the PS Query a bit and the RTF template, I then played around with the H tag (horizontal-break-table) of BI Publisher cross tab reports and got the output I wanted albeit with a catch. Each box (or row) of data elements got printed on separate pages. Unfortunately the horizontal-break-table causes a page-break and not a line-break and this works only with PDF output.
The solution:
At this point I had tried so many things that I had given up on BI Publisher and a tad disappointed at how much difficult this was proving to be as opposed to a simple MS Word mail merge. And just when I was about to settle for the closest solution I got using the 4 column output I discussed above, I found this wonderful blog from Vetriselvan where he had shown how to accomplish the output I was trying to get.
My sample template appears this way -
and the sample output is just the way I wanted -
There is also a caveat with this solution as well. The 4 embedded tables grow vertically down printing 1 data element and skipping the other 3 in a round robin fashion, thus making it appear to print across and then down. As long as the data is even, this works fine. However if any of the data element is larger than the rest, the rows will be of uneven size and the output would look like this (Element-28 occupies more height than the rest causing the alignment to go haywire) -
This is not a big issue as most of the commercial labels have pre-defined height and width and the tables embedded in the RTF template have to adhere to those size restrictions. This coupled with choosing the right font and font-size addresses any such issues.
Feel free to drop a comment if you have a question or accomplished using a different approach.
No comments:
Post a Comment