Wednesday Oct 26, 2011

Tricky Grouping

A week off trying to catch up with all the stuff that builds up was not enough but it was pretty fruitful. You clear the decks the week before you leave, you even answer a few mails in the first few days of vacation, until your manager slaps your wrists. But there is always a pile of work waiting when you get back. It was not too bad this time; I at least deleted the crap mail during the week via my phone.

How the heck did we survive without smart phones? I took a trip to north Denver last week to pick up a part for our bear ravaged pop up camper. I think he wanted a cold beer from the fridge just wish he'd used the front door rather than slicing the camper open. That gaping hole is meant to be covered!
I guess I could have checked a map before we left but my phone got us through the mess of roads up there. An 80 mile round trip for a $10 louvered cover! Still, we found a great Indian place for lunch, delicious curry!

Today's tidbit (titbit for my British brethren) is a bit tough to describe. It comes from Charlotte in New Zealand. On the surface it looks simple and it is when you know how :0) But there's a wrinkle in to smooth out.
Here's the data:

<DATA_DS>
 <G_1>
  <SERIALNUMBER>HSS0000156</SERIALNUMBER>
  <LOCATION>1 The Street Anytown USA</LOCATION>
   <ATTRIBDESC>Lettable Date</ATTRIBDESC>
  <ATTRIBVALUE>15/JUN/11</ATTRIBVALUE>
  </G_1>
 <G_1>
  <SERIALNUMBER>HSS0000156</SERIALNUMBER>
  <LOCATION>1 The Street Anytown USA</LOCATION>
   <ATTRIBDESC>Status Reason</ATTRIBDESC>
  <ATTRIBVALUE>Planned Major Repair/Upgrade</ATTRIBVALUE>
  </G_1>
 <G_1>
  <SERIALNUMBER>HSS0000276</SERIALNUMBER>
  <LOCATION>11 The Street Anytown USA</LOCATION>
   <ATTRIBDESC>Lettable Date</ATTRIBDESC>
  <ATTRIBVALUE>18/MAY/11</ATTRIBVALUE>
  </G_1>
 <G_1>
  <SERIALNUMBER>HSS0000276</SERIALNUMBER>
  <LOCATION>11 The Street Anytown USA</LOCATION>
   <ATTRIBDESC>Status Reason</ATTRIBDESC>
  <ATTRIBVALUE>Planned Major Repair/Upgrade</ATTRIBVALUE>
  </G_1>
 <G_1>
  <SERIALNUMBER>HSS0001046</SERIALNUMBER>
  <LOCATION>21 The Street Anytown USA</LOCATION>
   <ATTRIBDESC>Lettable Date</ATTRIBDESC>
  <ATTRIBVALUE>21/JUN/11</ATTRIBVALUE>
  </G_1>
 <G_1>
Heres the desired output:

Serialnumber Status Reason Date
HSS0000156 Planned Major Repair/Upgrade 15/JUN/11
HSS0000276 Planned Major Repair/Upgrade 18/MAY/11
HSS0001046 Planned Major Repair/Upgrade 21/JUN/11
HSS0001303 Planned Major Repair/Upgrade 19/MAY/11
HSS0001403 Planned Major Repair/Upgrade 14/JUN/11

Hok-Min, Zen Master of the RTF template, jumped on this for me and nailed it first time. On first look you think, ah this is just a simple re-group left, using some xpath to repeat the ATTRIBVALUE element twice in the table based on the ATTRIBDESC. Along the lines of:

<?ATTRIBVALUE[../ATTRIBDESC='Planned Major Repair/Upgrade']?> and <?ATTRIBVALUE[../ATTRIBDESC='Lettable Date']?>

But you oh so very easily end up with this:

Serialnumber Status Reason Date
HSS0000156
15/JUN/11
Planned Major Repair/Upgrade
HSS0000276
18/MAY/11
Planned Major Repair/Upgrade
HSS0001046
21/JUN/11
Planned Major Repair/Upgrade
HSS0001303
19/MAY/11
Planned Major Repair/Upgrade
HSS0001403
14/JUN/11
Planned Major Repair/Upgrade

and start scratching your head? With the 10.1.3.4.1 and 11g template builders there is a nice Group Left feature that removes the need for nasty nested tables but for this slightly corner case, it drops you into a world of misery. You need to take a step back from what the wizard has given you.

Yes, you need to group by serial number but you do not need to loop over the remaining data (current-group().) Rather, you just need to drop into that current-group() and bring the values you need up to the level of the serial number group. So you just need to modify your XPATHs thus:

<?current-group()/ATTRIBVALUE[../ATTRIBDESC='Status Reason']?>

i.e. provide a complete path to the element you want within the serial number re-group level. Neat!

Template and sample data available here.


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
« April 2014
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
26
27
28
29
30
   
       
Today