X

An Oracle blog about BI Publisher

  • RTF
    November 20, 2009

Old Skool Crosstabbing

James came up with a cunning crosstab report question on the forum this week.

CTab1.jpg

Does not look that bad right? Sadly the new crosstab builder can not build what we need. Notice there is no summarization in the grid; just a listing of times for each employee, these are clock in/clock out times, no calculations.

Here's the data:

<?xml version="1.0" ?> 

<rowset>

<row rownumber="1">

<EMPL_NAME>John</EMPL_NAME>

<DAY>Wednesday</DAY>

<DATE>11/18/2009</DATE>

<TIME>08:15</TIME>

</row>

<row rownumber="2">

<EMPL_NAME>John</EMPL_NAME>

<DAY>Wednesday</DAY>

<DATE>11/18/2009</DATE>

<TIME>12:15</TIME>

</row>

<row rownumber="3">

<EMPL_NAME>John</EMPL_NAME>

<DAY>Wednesday</DAY>

<DATE>11/18/2009</DATE>

<TIME>13:15</TIME>

</row>

<row rownumber="4">

<EMPL_NAME>John</EMPL_NAME>

<DAY>Wednesday</DAY>

<DATE>11/18/2009</DATE>

<TIME>17:30</TIME>

</row>

<row rownumber="5">

<EMPL_NAME>Mary</EMPL_NAME>

<DAY>Wednesday</DAY>

<DATE>11/18/2009</DATE>

<TIME>10:00</TIME>

</row>

<row rownumber="6">

<EMPL_NAME>Mary</EMPL_NAME>

<DAY>Wednesday</DAY>

<DATE>11/18/2009</DATE>

<TIME>14:15</TIME>

</row>

<row rownumber="7">

<EMPL_NAME>Mary</EMPL_NAME>

<DAY>Wednesday</DAY>

<DATE>11/18/2009</DATE>

<TIME>15:15</TIME>

</row>

<row rownumber="8">

<EMPL_NAME>Mary</EMPL_NAME>

<DAY>Wednesday</DAY>

<DATE>11/18/2009</DATE>

<TIME>19:30</TIME>

</row>

<row rownumber="1">

<EMPL_NAME>John</EMPL_NAME>

<DAY>Thursday</DAY>

<DATE>11/19/2009</DATE>

<TIME>07:50</TIME>

</row>

<row rownumber="2">

<EMPL_NAME>John</EMPL_NAME>

<DAY>Thursday</DAY>

<DATE>11/19/2009</DATE>

<TIME>11:59</TIME>

</row>

<row rownumber="3">

<EMPL_NAME>John</EMPL_NAME>

<DAY>Thursday</DAY>

<DATE>11/19/2009</DATE>

<TIME>12:35</TIME>

</row>

<row rownumber="4">

<EMPL_NAME>John</EMPL_NAME>

<DAY>Thursday</DAY>

<DATE>11/19/2009</DATE>

<TIME>18:00</TIME>

</row>

<row rownumber="5">

<EMPL_NAME>Mary</EMPL_NAME>

<DAY>Thursday</DAY>

<DATE>11/19/2009</DATE>

<TIME>9:00</TIME>

</row>

<row rownumber="6">

<EMPL_NAME>Mary</EMPL_NAME>

<DAY>Thursday</DAY>

<DATE>11/19/2009</DATE>

<TIME>13:25</TIME>

</row>

<row rownumber="7">

<EMPL_NAME>Mary</EMPL_NAME>

<DAY>Thursday</DAY>

<DATE>11/19/2009</DATE>

<TIME>14:45</TIME>

</row>

<row rownumber="8">

<EMPL_NAME>Mary</EMPL_NAME>

<DAY>Thursday</DAY>

<DATE>11/19/2009</DATE>

<TIME>18:20</TIME>

</row>

</rowset>

So I went back to the old skool crosstab methods and built it manually.

CTab2.jpg

Just a four celled table with an @column loop for the headings, regular for-each-group for the row headings, @cell loop for the 'measure' and then an inner 'current-group()' loop for the times.

CTab3.jpg

You can get the RTF template

here
if you need it.

Sometimes you can teach a new dog old tricks!

Join the discussion

Comments ( 4 )
  • vishalaksha Wednesday, March 3, 2010
    Hi Tim,
    I have a unique requirement. For a particular part number I have quantity available date wise. Now I also want to show sum of quantities on monthly basis and half yearly too. Below is the required output. I have tried with different grouping methods but I am not able to get it. The whole data is coming from a single table.
    Heading...Jan Allotment...4-Jan...11-Jan...Feb Allotment...1-Fen...8-Feb......1H2010 Total....
    ABCD................9...................5...........4.................7.......................3.............4...................16......................
    CDEF...............11..................6...........5.................10.....................5.............5...................21......................
    EFGH................9...................5...........4.................7.......................3..............4..................16......................
    I can generate the data without monthly or half yearly grouping .
    Please guide me how to proceed.
    Thanks
    Vishalaksha
  • vishalaksh Wednesday, March 3, 2010
    I forgot ot mention that I am working on Oracle XML Publisher 5.6.3
  • guest Tuesday, February 17, 2015

    I want to display crosstab data graphically in a Line Graph. How can I go about doing that?

    the BI Publisher desktop doesnt seem to think it is valid. I was able to do it via an interactive report but it orders the data alphabetically even if I dont want it to.

    (I have salary bands, so 101000-120000 should be after 80000-90000 but the interactive report puts it before)


  • Tim Tuesday, February 17, 2015

    Need more information on your requirement to be able to comment intelligently. Can you email me detail, screen shots, sample data and required output?

    Tim


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.