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!

Comments:

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

Posted by vishalaksha on March 02, 2010 at 06:28 PM MST #

I forgot ot mention that I am working on Oracle XML Publisher 5.6.3

Posted by vishalaksh on March 02, 2010 at 07:09 PM MST #

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)

Posted by guest on February 17, 2015 at 11:50 AM MST #

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

Posted by Tim on February 17, 2015 at 01:36 PM MST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« August 2015
SunMonTueWedThuFriSat
      
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
27
28
29
30
31
     
Today