Oracle BI Publisher Blog

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

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.

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.

You can get the RTF template here if you need it.

Sometimes you can teach a new dog old tricks!

Filed under:

Shout out here for Hussein over on the ADF Dev to Dev blog. Nice article covering some of the BIP APIs and how to use them within an ADF project.

http://husaindalal.blogspot.com/2009/11/integrating-bi-publisher-standalone.html

Thanks to Jurgen and Klaus for finding Hussein and his prose.

Filed under:

Completely off topic today, but something Im sure nearly all of you use. Social networks, whether it be Facebook, Twitter, MySpace, the list goes on, I'm sure you are using at least one. Jake over at the lab has been a great flag bearer for the genre. I on the other hand have been somewhat skeptical, there are probably posts over on the lab proving so. Yep, Im a dinosaur, even my kids tell me so and I listen to more modern new fangled music than they do.

I have to admit, I have a twitter account, not much used. A facebook account, more used but mainly down to the ease of posting something via my trusty but horribly cracked iPhone (that's another story) Its been great to keep up with current friends and colleagues and find old school buddies and catch up, its fun but then what? That's kinda where my interest wains...

That is until this past week. Back in March my wife opened an ice cream shop in our town, its more of a village, blink and you miss it. The shop has an accompanying website cos Im geeky like that - I think we are the only shop in the vill ... town to have one. We get hits and comments and I write a blog for it posting pictures, news, etc. Our advertising budget is not huge so print media for the most part is out and 'sadly' except for Leslie and her WSJ subscription, who reads a paper these daze?

What about online advertising? I'm skeptical of that too, I make a point of blocking the common hosting servers that slow web pages while down loading their ads. I ignore them if they do get through. A quick straw poll and I found that most of my friends ignore them. So what alternatives are there?

My wife is a huge Facebook fan, so many friends and to my mind spends far too long on it :0) Rather like the pot calling the kettle black I suppose. Her computer skills have improved immensely since using it thou, so I should not complain. She still hits the button or icon 5 or 6 times if the reaction is not instant; makes for some fun trying to close 20 windows on a laptop that has been brought to its knees by 20 copies of Adobe Illustrator being opened at the same time :0)

I spotted 'fan' pages on FB a few weeks back and thought what better way to get some 'viral' exposure for the business in the surrounding area. My kids 'Facebook' too and they spend even more time on it than my wife, that's when they are not texting of course.

Its not going to cost me a bean so I duly started putting together the fan page (still working on some custom FBML content.) But we are now posting updates, pictures, getting to the videos and some freebies for the more motivated.

I then invited all of my wife's friends to become fans; within the week we were up to 50, admittedly some were from the UK, Canada and California which was not so good but its still useful. Why useful? Because of the viral nature of Facebook, even if the first fans are remote, they have friends that see them 'fanning' the shop, so they fan the shop. And so it goes on, eventually (I hope) getting back to folks in our area who may not know about the shop. How many degrees of separation are facebookers apart? We are just starting to break out of the 'friends' stage with fans that we do not know. Some from afar but others closer, so its starting.

Facebook, seems to have finally found it revenue stream in offering to target likely fans on their page with an ad on the right hand side of the page. Having already poo poo'ed online advertising earlier I dont think we'll be going there. Facebook does offer some great profiling thou, they know so much about users that I can run an add to 'male/female teenagers that live in our nearest town and like ice cream' ... awesome stuff!
They do have another cool feature that I do want to use. Im going to need my sons' help and their nasty texting habit. They do not know this yet but they will be texting their 100's of friends to text FB to become fans of the shop, if they so wish.
This is my master plan for world ice cream shop domination, getting more fans and the fans we want; ice cream eating fans that will nag their parents to bring them in.

This is all great but how about getting updates out there, we can move over to the fan page as administrators but what about other content? I created a 'twitter' account for the shop too, hey its free and 'real' people will find you, I think we boast more followers on twitter (excluding the scantily clad bots that seem to plague every twitter account) than fans on FB.
Twitter is not on my wife's radar at the moment so we needed some way to get her shop FB updates out as tweets. Facebook does that for you, even from a fan page now, easy. To take it a stage further I wanted to be able to write a blog posting from our Wordpress install, get that to FB and then out as a tweet. That took some finding but there is a plugin out there that almost does it. With a little tweaking I can now write a blog posting, hit a button to publish to FB and then FB pushes it out to twitter. Perfect!

I have finally seen the light, at least for my wife's business. For other bigger companies, maybe yours? The likes of Coca Cola and others boast huge numbers of fans, that kind of product awareness is invaluable ... every little helps I say! We'll be pushing as hard as possible into the social networking frontier for our little shop.

Filed under:

More from my journey of Siebel- BIP integration discovery last week. It was a steep learning curve but none the less very useful.
Firstly, there are 3 levels of integration available to Siebel 8.1.1 users

Level 1

• Siebel stores and manages the templates and manages data generation via integration objects


• At runtime, Siebel calls BIP via WS to process data+template to generate output and returns the document to the user.


• BIP just acts as a publishing service for Siebel

Level 2

• BIP stores the templates and a report definition. The report uses complex Siebel web services as its data source.
• At runtime, BIP is called via a button or menu item (custom), it then calls Siebel back via a WS to fetch the data (integration object)
• BIP then generates output and returns the document to the Siebel UI
• This is the recommended integration as user security can be applied to the data being returned by Siebel

Level 3

• BIP stores the templates and a report definition. The report uses SQL or stored procedure calls to retrieve data from the underlying database
• At runtime, BIP is called via a button or menu item (custom), BIP fetches data from Siebel DB
• BIP generates output and returns to Siebel UI

More details in a great white paper from John on the Siebel PM team, Siebel - BI Publisher Integration Concepts

If you are looking for info on how to migrate your existing Actuate reports in the context of Siebel - check out Kan's article over at the BIP Consulting Blog Once you have downloaded the migration assistant check out the viewlet and accompanying white paper.

Filed under:

Generating HTML from BIP? Trying to set a title for the page? Stuck? Can't find out how?

Its not in the documentation yet but its pretty simple to do. In the template in MSWord use File->Properties->Summary->Title.

Enter your page title. Its only going to work for HTML for Excel and RTF output the Title property is filled. The title will not come through into your PDF outputs. The other properties are ignored in the final output. A little inconsistent I know, but that's the way it is today.

Filed under:

Want to thank Srikant Subramaniam from the Oracle Fusion Middleware for Apps team for prompting this post.

As much as we here at Oracle would like to think that all data is in a database -- an Oracle Database at that, the fact is that sometimes ... dare I say it ... data is kept in Excel spreadsheets.

Srikant's team maintains some information in an Excel spreadsheet. To get this data into a nicely formatted PDF file was a tedious process that took 5 to 6 days. Srikant wanted to know if Publisher could help. The challenge was how to get the data out of the Excel spreadsheet into an XML file. After a little searching on the web, we worked out a way to do that.

Now his team can generate the desired output in a couple of hours. Over 20 times faster than it used to take. Saving at least 38 hours of precious time that can now be put toward other projects.

Srikant was kind enough to document this in a nice tutorial. The tutorial shows how to convert data from an Excel spreadsheet into a PDF document. The Excel data consists of a set of addresses, that are then grouped and sorted using the BI Publisher Template Builder to create the RTF template and the output PDF file. Here are the supporting files to run the tutorial.

Knowing that dates and nulls can sometimes be problematic, I took it a little further to test out an Excel spreadsheet with dates and nulls. Fortunately the NULL values are converted nicely and those elements with NULL values are absent in the resulting XML - just the way you want it so that values can be aggregated correctly.

The Dates however proved to be a little more challenging. In the XML data, they kept coming out as Excel number values and not in date canonical format. I was also using Excel 2007 so the process was a little different from what Srikant documented in the tutorial. Here are the differences for Excel 2007 and what I found I had to do to get Dates to come out in the XML correctly. Perhaps someone more clever has a better way ...

-- When converting the range to an XML List I was warned/prompted to use format or data type when mapping. When I chose data type (which I thought would work) I ended up with dates as the Excel number values in the spreadsheet and the exported XML data. So I went with "Use existing formatting" for the the mapping so the dates would still appear as dates in the spreadsheet.

However, exporting to XML still left the dates as Excel number values.

-- The "trick" is that you must create and apply a new "XML Map" (this appears to be the Excel term for XSD) that tells Excel that the date is to be mapped to an XML date format. The first step is to generate the XSD file. Set the focus in the data range and select "Create XSD files for the XML Schema at the active cell" from the XML Tools Add-in menu.

-- In the XSD you will see that the Date field is being treated as a string.

Modify it so that it's type is Date.

-- Upload the XSD as a new XML Map and map the field to this new XML map. Set focus on a cell in the region, right click and select the "XML Source" menu choice from the XML menu.

In the XML Source dialog click on the [XML Maps...] button. Add the XSD you just modified and rename it to something meaningful. Then delete the Root_Map that was there.

Click OK to return the XML Source dialog and drag and drop the Map elements onto the correct columns headers in the data range.

-- Now when you export to XML, the dates will be in canonical format and can be formatted correctly by the Template Builder or other XSL formatting tools.

Filed under:

I have been working with the Siebel CRM integration today, a good learning curve and I have learnt a lot more than I wanted to about Siebel CRM. One thing I did get stuck on was that my template was not generating the results I was expecting.
I went through and generated some sample data against what I thought was the integration object I was seeing on the page. Created my template, uploaded it, attached the report to the correct page. Went to that page and tried to generate the ouput, I was getting the header information but not the detail I was expecting. What to do?
The integration is of the embedded variety ie Siebel generates the XML and calls BIP to process it with a template to return the result. So the XML structure and content is hidden at runtime. Pick the wrong integration object, as I did, and you're a little stuck. Most of it was ignorance on my part but I wanted to see what was in the XML, element names and values.

I came up with the following template to let me at least seethe elements and assigned values:

<?for-each://*?>

Element: <?local-name()?> Value:<?current()/text()?>

<?end for-each?>

this pushed out

Element: ListOf_ssHlsCase Value:
Element: ssHlsCase Value:
Element: ssCaseNum Value:410194-14115562
Element: ssCreatedDate Value:10/20/2009 07:26:40
Element: ssDescription Value:
Element: ssName Value:Lew Golden - - 10/20/2009
Element: ssStage Value:Application
Element: ssStatus Value:Active
Element: ssSubStatus Value:
Element: ssSubjectContactFirstName Value:Lew
Element: ssSubjectContactLastName Value:Golden
Element: ssSubjectFullName Value:
Element: ssType Value:Other
Element: ssId Value:8SIA-8EJMY
Element: ssCreated Value:10/20/2009 07:26:40

Takes a little deciphering but I worked out that I was not using the integration object I thought I was, dagnabit! Useful thou I thought, so Im sharing it, enjoy!

Filed under:

A Sunday post, I know, dedicated or what! It is however a lazy post of sorts. Im sitting in my wife's ice cream shop serving ice cream to non-existent customers on a chillsome Sunday. Thinking up new marketing strategies for my poor wife to implement and idly surfing the Publisher pages on OTN.

There's a bunch of new content out there from Reporting Plaforms for Applications to Integration with MSSharepoint (nice article). For me, the best practices paper is the best (no I didnt write it :0) its packed with info and the PM folks have put notes on each slide in the PDF. Just click the note icon in the bottom right for some information on what the slide is getting at.

Its Monday, for most of you reading this, ease into the week with a peruse of the Publisher pages - http://www.oracle.com/technology/products/xml-publisher/index.html and a cup o java or char.

Filed under:

The snow has nearly all gone, just in time for the next batch! It was a douzy of a storm, fun driving conditions, amazing how much you forget over the hot dry 'roaded' summer. My newly licensed son was itching to get out there. Mom wouldn't let him, I had to agree, our insurance couldn't take it and we have way too many trees near our house. The trees looked very pretty thou and speaking of trees (what a segue) I have been asked numerous times if BIP can generate a tree structure, the more ambitious ask for an org chart. I plan to take a look at how to do the org chart another time, for now, how about a nice tree structure.

Between the two of us, Rob (colleague and data modeler extraordinaire) came up with a solution for a customer to generate this:

Its a tree of sorts, nothing fancy, we just wanted to get the structure right, so there are no details under the headings. Its getting the indenting correct that was the trick. The customer had a pretty fixed data structure and did not want to use nested tables.

The template is pretty simple, just a funky trick to get the indenting working. I mentioned the customer had a pretty fixed data structure. It made life easier, they only had 3 levels to worry about so the code to handle the indenting could be pretty straightforward and somewhat 'hard coded' for the data set.

It also has the advantage that the tree is in the right order ie

Level1
-Level2
-Level2
-- Level3
-Level2
Level1
Level1
...

The template looks like this

the fields contain

Of course the most interesting field is the C

<?if:number(LEVELS)=1?>
<?ORG_LONG_NAME?>
<?end if?>
<?if:number(LEVELS)=2?>
<?attribute@inblock:start-indent;'20.0pt'? > <?ORG_LONG_NAME?>
<?end if?>
<?if:number(LEVELS)=3?>
<?attribute@inblock:start-indent;'30.0pt'?> <?ORG_LONG_NAME?>
<?end if?>

A chhose statement would have worked just as well. You can see the dependence on the data order and the structure or more specifically LEVELS element. We need to check that to know how big the indentation should be.
The @block is also important, we need that to get the indentation to show only on the current text and not affect anything else. If we had been using tables we could have gotten around that.
Now we could have been a little smarter and written something to handle any number of levels ... answers on the back on a new Mac Book Pro 15-inch: 2.8GHz 4Gb 500Gb HD :0)

Filed under:

Been too danged busy again, we got a nice 2-3ft of snow over the last two daze, digging out has been fun! Nearly lost the car ...

A quick Friday post on Siebel integration. Two pretty useful docs that John from the Siebel PM team recently shared with me.

8.1.1 TOI: Siebel BI Publisher Integration Functional Overview

http://oukc.oracle.com/static05/opn/login/?c=576002953&t=offering

Siebel Reporting Migration FAQ - Actuate and BI Publisher (external visible)

https://metalink3.oracle.com/od/faces/secure/km/DocumentDisplay.jspx?id=764097.1

Have a good weekend

Filed under: