Oracle BI Publisher Blog

Question on the forum this week ...

Hi,

I need to build a template like below, in my sql query i have employee details.
i need to display every five employee details in table then 5 in subsequent tables.

so how to split columns..
help me soon.

see below template:
--------------------------------------------------------------------------------------
Description emp1 emp2 emp3 emp4 emp5
--------------------------------------------------------------------------------------
empno 1 2 3 4 5
empname a b c d e
salary 100 200 500
---------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------
Description emp6 emp7 emp8 emp9 emp10
--------------------------------------------------------------------------------------
empno 6 7 8 9 10
empname a b c d e
salary 100 200 500
---------------------------------------------------------------------------------------

A quick point on questions on the forum, please please please let us know the following:

1. BIP/XMLP version being used
2. What flavor? EBS, PS, JDE, Standalone or using APIs
3. A snippet of XML data if appropriate to the question
4. Template layout - its tough I know but it helps
5. Logs, config files, etc - post the contents of them

Im closing in on 1000 replies and I wonder how of those are 'Hi XXX, Please supply info A, B and C then I can help you. Regards, Tim' format. Lastly, please provide a forum name, its so much better replying 'Hi John' or 'Hi 60 Sausages' - yes we have a Mr. 60 Sausages on the forum ... than replying to User00000 ... who are you?

To the question, were I feeling mean I'd reply, check the user guide, there is enough in there to work out how to do it but it makes for a nice demo so Naresh ... you're lucky :o)

We need to use the @cell command to get the rows of data to display as columns, I assumed we had a data set like this:

<ROWSET>
   <ROW num="1">
      <EMPNO>7810</EMPNO>
      <ENAME>Jo Bloggs</ENAME>
      <JOB>CLERK</JOB>
      <SAL>100</SAL>
      <DNAME>ACCOUNTING</DNAME>
   </ROW>
   <ROW num="2">
      <EMPNO>7800</EMPNO>
      <ENAME>Jane Doe</ENAME>
      <JOB>CLERK</JOB>
      <SAL>100</SAL>
   </ROW>
 ...
</ROWSET>

All we need to do is loop over each member of the ROW group using the @cell command

<?for-each@cell:ROW?>

this will get us the row values rendering as column values, However the requirement was to show 5 at a time and then break to a new table.
Introducing an XPATH command can achieve this for us:

<?for-each@cell:ROW[position()<6]?>

this means that we will loop over the first 5 ROWs and then stop. You can then introduce a second instance of the template where the XPATH expression presents a range of ROWs to loop over.

<?for-each@cell:ROW[position()>5 and position() <11]?>

this will loop over 6 through 10.

So our template will look something like

and generate output like

Now, we have hardcoded things here and here are the sample files. But you could quite easily wrap a loop around the table that repeats if for as many rows as is needed based on the incoming data. Im not going to do it just yet ... have a go.