» Publishers, Monetize your RSS feeds with FeedShow: More infos (Show/Hide Ads)
Being British, I of course love Indian food, its almost our national dish back in the UK. Roast beef and yorkshire pud is no longer the go to faire. Its a spicy hot chicken Pathia or Dal for me all the way. Thats sometimes hard to find here in Colorado, we have taken to making our own having found only a handful of Indian restaurants in Denver and Springs (suggestions welcome locals :0) Nan bread is now available in local supermarket so we have not built our own tandoori oven.
My tenuous bog post title is not referring to that delicious bread but to an XSL and therefore Publisher issue, well maybe be not an issue; more of something you may need to handle.
there is no native support to handle divide by zero errors or maybe mistakenly dividing a string into a number. Right now the only way around it is to check every value prior to using it in a calculation e.g.
<?if: XXXX >0?> <?XXXX div YYYY?><?end if?>
Not so bad, but if you have multiple calculations to handle in a template it can become a pain and of course is a bunch of code to write or at best copy and paste.
There was a questions on the forum this week asking if BIP had a safeDivide function like Actuate has? After a little digging to find out what the heck a safedivide function was, my initial thought was to create a template(function) for the template to call prior to any divide functions. I am from the school of 'why re-invent the wheel' so Google it took another hit. Lots of cool solutions out there; one of my favorite sites is from Dave Pawson of XSLT book fame - http://www.dpawson.co.uk/xsl/sect2/sect21.html. There is a mass of answers on Dave's site from a host of experts, well worth bookmarking.
Dave's site popped up this solution
Define a format name, like so:
<xsl:decimal-format name="MyFormat" NaN=" - " zero-digit="0"/>
And use it with your format statement:
<?format-number(@totalAmt,'#,###','MyFormat')?>
this is much simpler than building a function. The first statement needs to be placed at the top of your template. In this case if a divide by zero occurs i.e. a NaN then a '-' will be returned.
First thought, corrected and a solution found. My second thought, we need to provide something natively to handle this for folks. Some folks have differing requirements in separate parts of an output, maybe 'N/A' in sme cases and '0' for others. So, I logged an enhancement request to handle this. I logged 9355897, Template Meister Hok Min picked it up and implemented the enhancement within the day. Now waiting for a release vehicle but there's service for you, logged, coded and tested within the day. If you are desperate for it, you can get support to log a one off request on your behalf.
Been a while I know, crazy busy, which is not a bad thing but the blog suffers. Last week was spent embedded in all things 11g BI including the new version of Publisher. There are some very cool and more importantly useful, features coming. My favorite is the new data builder tool; no more 'notepad' to build more complex data structures, oh no!
Its a great drag and drop interface that lets you pull in multiple sources, join them and then group the data to generate a hierarchical data set. You can then abstract the element names to friendly functional names for your layout template builders. There are also some new sources, such as LDAP and Excel - cant wait to share more on the new stuff. Before you ask, I can not tell you when its coming ... you know the drill.
Back to my numbers to words update. String of mails flying around over the toWordsAmt function; typically used in check printing.
<?xdoxslt:toWordsAmt(12345.98)?>
generates 'Twelve Thousand Three Hundred Fourty Five and paise Ninety Eight' It always appears to return the subunit in 'Paise'. Upon investigation the 'paise' is hardcoded; this function was written for some Crystal conversion routines that made it into core code.
A few mails later and it emerges that we would like you to use another function that it a bit more robust and has some more options.
<?xdoxslt:to_check_number(12345.98,2)?>
Typical use of this function is :
xdofx:to_check_number(12345.67, 2)
--> Twelve thousand three hundred forty-five and 67/100
The user can also pass a currency for its precision.
xdofx:to_check_number(12345.67, 'USD')--> Twelve thousand three hundred forty-five and 67/100 Note that the currency is only for precision. No currency word, e.g. dollar or cent, is included in output. All capital output is available with CASE_UPPER key word.
xdofx:to_check_number(12345, 'JPY', 'CASE_UPPER')--> TWELVE THOUSAND THREE HUNDRED FORTY-FIVE
CASE_INIT_CAP, CASE_UPPER, and CASE_LOWER are available.
The user also change the style of decimal area, if needed.
xdofx:to_check_number(12345.67, 'EUR', 'CASE_LOWER', 'DECIMAL_STYLE_WORDS')--> twelve thousand three hundred forty-five and sixty-seven
DECIMAL_STYLE_FRACTION1(default), DECIMAL_STYLE_FRACTION2, and DECIMAL_STYLE_WORDS are available.
Please note for EBS customers this is only available for R12. Its not currently presnt on 11i. You could log a backport request via support thou.
Have a play and you'll be printing words in no time.
Finally, the end of the most manic week I think I have had for a while and that included a public holiday on Monday. Public holidays are a double edged sword for me. On the plus side, you can enjoy your Sunday, you get to lie in bed on Monday, go out for lunch with your partner, swim with the kids. On the downside, there is always a nagging feeling that its a day of work that you are going to have to somehow make up in the remaining four days of the work week. Two late nights this week that I'll be paying for later ...
During my presentation prep I came across a reasonably useful presentation I squirreled away on my hard drive on label printing. A little high level but gives you some information about how to get BIP hooked up to you Zebra printers. Get it here.
Officially a public holiday for us at Oracle today but for the rest of the world and some folks here in the good ol' US of A, a great write up from Andy and his team at Beyond Systems on handling dynamic sql requirements for BIP when connecting to BI Server.
Just the intro to 1. whet your appetite and 2. for those of you wondering what the heck Im talking about:
Recently while working on a high profile BI Publisher project we encountered a rather interesting challenge. The organisation that we were working for are very forward thinking, having adopted OBIEE as their corporate BI solution they had fully bought into the idea of having a single point of truth. This architecture meant that OBIEE server would be the single data source. BI publisher is a great product however it quickly became apparent to us that we had lost the ability to write dynamic SQL through the usual technique of creating a data template and executing a package procedure in the before report data trigger to change the SQL. Now this in itself was not a complete show stopper but the effort required to write a single all inclusive query, then filter the data and conditionally display it in the RTF template was going to be considerable. With this in mind we decided to do some research and came up with this alternative solution which we hope will help others adopt the OBIEE server architecture with BI Publisher. The beauty of this solution is that several reports can condensed into a single report which significantly reduces the development effort and gives the user a much simpler solution.
Document available here.
Thanks Andy and team, really appreciate the time you took to write it up and allow me to share it.
I have been trying to help out a customer recently with a nasty SSL setup for their BIP instance. Wont get into detail but although BIP is running on an SSL server its not seeing itself as running on it. Links and images in the outputs are not 'https' but rather 'http'. Having got the dev team to dig into their code, it appears to be the web server set up that is at fault.
I came up with an idea to work around the issue for now so that they could move forward with their project while the web server issue is addressed - a custom parameter.
Some of you that have turned debug on might have seen several parameter values going by in the log prefixed with 'xslt.' This is what gave me the idea.
You will need to set up the xdo.cfg file to hold the parameter. It needs to sit in the $JDK_HOME/jre/lib directory. Or under the config directory in the reports repository e.g. XMLP\Admin\Configuration for the standalone release.
Heres the beginning of mine:
<config version="1.0.0" xmlns="http://xmlns.oracle.com/oxp/config/">
<!-- Properties -->
<properties>
<!-- System level properties -->
<!-- PLEASE SELECT A VALID TEMPFILE DIRECTORY!!! -->
<property name="system-temp-dir">c:/Temp</property>
<!-- PDF compression -->
<property name="pdf-compression">true</property>
<!-- PDF security -->
<property name="pdf-security">false</property>
<property name="pdf-open-password">user</property>
<property name="pdf-permissions-password">owner</property>
<property name="pdf-no-printing">true</property>
<property name="pdf-no-changing-the-document">true</property>
<!-- Custom Properties -->
<property name="xslt.SERVER_PROTOCOL">"https"</property>
</properties>
I have a custom property(parameter) SERVER_PROTOCOL. Notice the double quotes surrounding the value, they are a must!
Now in my template I just need to declare the parameter
<?param:SERVER_PROTOCOL?>Notice you dont need the 'xslt.'prefix. Then I can reference the value
<?$SERVER_PROTOCOL?>
Im using this as a workaround in this case but Im sure you will come up with other uses.
I got involved in an interesting issue yesterday with Julie. She had two columns of data coming into her data set but she wanted to generate a stacked vertical column report, normally you would have three data points. One of the data points would be along the X-Axis. The count of the instances of the other data point would make up the column. Its an interesting problem ... well I thought it was. Read on if you are interested. The change in font will be explained at the end.
Using the following data:
<ROWSET>
<ROW>
<STE>CMP</STE>
<ACT_LEV>SHORT</ACT_LEV>
</ROW>
<ROW>
<STE>CMP</STE>
<ACT_LEV>SHORT</ACT_LEV>
</ROW>
<ROW>
<STE>CMP</STE>
<ACT_LEV>LNG</ACT_LEV>
</ROW>
<ROW>
<STE>CMP</STE>
<ACT_LEV>OPEN</ACT_LEV>
</ROW>
<ROW>
<STE>CMP</STE>
<ACT_LEV>OPEN</ACT_LEV>
</ROW>
<ROW>
<STE>COM</STE>
<ACT_LEV>SHORT</ACT_LEV>
</ROW>
<ROW>
<STE>COM</STE>
<ACT_LEV>SHORT</ACT_LEV>
</ROW>
<ROW>
<STE>COM</STE>
<ACT_LEV>SHORT</ACT_LEV>
</ROW>
...
</ROWSET>
Notice that
1, Data is de-normalized
2. The ACT_LEV for a given STE can contain any of 4 values
|
ACT_LEV |
|
LNG OFFLINE OPEN SHORT |
This is the required chart:
|
STE |
ACT_LEV | ||||||
|
CMP |
| ||||||
|
OM |
| ||||||
|
CVD |
| ||||||
|
DIF |
| ||||||
|
DRY |
| ||||||
|
IMP |
| ||||||
|
INS |
| ||||||
|
LIT |
| ||||||
|
LOT |
| ||||||
|
MTL |
| ||||||
|
PHT |
| ||||||
|
PTS |
| ||||||
|
WET |
|
This is going to require some customization of the chart commands. Once modified by hand the chart dialog will not be able to re-read the chart definition without losing the customization.
Start using the dialog to get the
following:
-
Drop the STE field into the Labels field. Set the Group Data checkbox
-
Drop the 'measure' ACT_LEV for as many times as you have possible values for ACT_LEV. In the attached data set its 4.
-
Set the chart to Bar-Vertical Stacked
-
Add your legend values
-
Be sure to set the aggregation to count. The graphic is showing Sum. I think there is a bug in the chart dialog, if I create the base chart, Close it and re-open then the aggregation is reset to Sum ... grrrr!
This will give us the following chart:
This is still editable by the chart dialog. It will stack the same ACT_LEV count values on top of each other for every STE. At this point we need to get counts for each of the possible values of ACT_LEV. We do this using an XPATH expression, its like an inline if or where condition e.g.
count(ACT_LEV where ACT_LEV = 'SHORT')
count(ACT_LEV where ACT_LEV = 'LNG')
count(ACT_LEV where ACT_LEV = 'OPEN')
count(ACT_LEV where ACT_LEV = 'OFFLINE')
In our XPATH we use a specific format:
count(ACT_LEV[.= 'SHORT'])
count(ACT_LEV[.= 'LNG'])
count(ACT_LEV[.= 'OPEN'])
count(ACT_LEV[.= 'OFFLINE'])
-
The [ ] enclose the command
-
'.' refers to the current value ie ACT_LEV
-
Then we test against a hard coded value of ACT_LEV
Now we need to get at the chart commands.
In Word 2000/2/3 - double click the chart image and go to the Web tab
In Word 2007 - right click the chart image -> Size -> Alt Text tab.
Rather than work in the window, copy and paste the text into the main document, its much easier. You'll get the following:
chart:
<Graph depthAngle="50" depthRadius="8" pieDepth="30" pieTilt="20" seriesEffect="SE_AUTO_GRADIENT" graphType="BAR_VERT_STACK"><LegendArea visible="true" /><LocalGridData colCount="{count(xdoxslt:group(.//ROW, 'STE'))}" rowCount="4"><RowLabels><Label>Short</Label><Label>Long</Label><Label>Open</Label><Label>Offline</Label></RowLabels><ColLabels><xsl:for-each-group select=".//ROW" group-by="STE" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><Label><xsl:value-of select="current-group()/STE" /></Label></xsl:for-each-group></ColLabels><DataValues><RowData><xsl:for-each-group select=".//ROW" group-by="STE" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><Cell><xsl:value-of select="count(current-group()/ACT_LEV)" /></Cell></xsl:for-each-group></RowData><RowData><xsl:for-each-group select=".//ROW" group-by="STE" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><Cell><xsl:value-of select="count(current-group()/ACT_LEV)" /></Cell></xsl:for-each-group></RowData><RowData><xsl:for-each-group select=".//ROW" group-by="STE" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><Cell><xsl:value-of select="count(current-group()/ACT_LEV)" /></Cell></xsl:for-each-group></RowData><RowData><xsl:for-each-group select=".//ROW" group-by="STE" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><Cell><xsl:value-of select="count(current-group()/ACT_LEV)" /></Cell></xsl:for-each-group></RowData></DataValues></LocalGridData></Graph>
I have highlighted the pieces we need to change in bold. Right now they are all showing the same value, ACT_LEV. Just make the changes to add the XPATH expressions e.g. count(current-group()/ACT_LEV[.='SHORT'])
chart:
<Graph depthAngle="50" depthRadius="8" pieDepth="30" pieTilt="20" seriesEffect="SE_AUTO_GRADIENT" graphType="BAR_VERT_STACK"><LegendArea visible="true" /><Y1Axis majorTickStepAutomatic="false" majorTickStep="1.0"/><LocalGridData colCount="{count(xdoxslt:group(.//ROW, 'STE'))}" rowCount="4"><RowLabels><Label>Short</Label><Label>Long</Label><Label>Open</Label><Label>Offline</Label></RowLabels><ColLabels><xsl:for-each-group select=".//ROW" group-by="STE" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><Label><xsl:value-of select="current-group()/STE" /></Label></xsl:for-each-group></ColLabels><DataValues><RowData><xsl:for-each-group select=".//ROW" group-by="STE" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><Cell><xsl:value-of select="count(current-group()/ACT_LEV[.='SHORT'])" /></Cell></xsl:for-each-group></RowData><RowData><xsl:for-each-group select=".//ROW" group-by="STE" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><Cell><xsl:value-of select="count(current-group()/ACT_LEV[.='LNG'])" /></Cell></xsl:for-each-group></RowData><RowData><xsl:for-each-group select=".//ROW" group-by="STE" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><Cell><xsl:value-of select="count(current-group()/ACT_LEV[.='OPEN'])" /></Cell></xsl:for-each-group></RowData><RowData><xsl:for-each-group select=".//ROW" group-by="STE" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><Cell><xsl:value-of select="count(current-group()/ACT_LEV[.='OFFLINE'])" /></Cell></xsl:for-each-group></RowData></DataValues></LocalGridData></Graph>
To get the lines on the Y-Axis for each unit we added the following line
<Y1Axis majorTickStepAutomatic="false" majorTickStep="1.0"/>
Its in bold above for the position it needs to be set at.
Once you have made the changes to all four to handle the four different values. Copy and paste the XML back into the Web/Alt Text tab
Now you will have a chart that looks correct:
If the template builder throws and error similar to Error in expression: 'count(current-group()/ACT_LEV[.=Â’SHORTÂ’])'. It means that you have used smart quotes '' rather than plain ol quotes in the XPATH. Just open the Web/Alt Text tab, then delete and replace the quotes. In the dialog it will only use plain quotes.
If you need to make the chart larger or smaller, just resize the chart image. It will distort the chart in the template but should come through crisp in the output.
Don't forget, if you open the chart with the chart dialog it will blow away your customizations. It might be a good idea to store the base chart format without your changes so you do not have to re-build it each time. You could add it to the end of your template and wrap and if statement around it so that its hidden at runtime.
I actually built a self explaining template for all of this, rather than then copy and paste from Word over the to the blog client I have. I just ran the template to HTML, then copied and pasted the whole thing from firefox into the blog article, have you seen Word HTML ... bleeeeuch!. It came across pretty well, just a different font. If you are interested in the template and data you can get them here. Happy Charting!.
I have shiny new laptop and on that laptop is Office 2007 ... oh joy! Its been a bit of struggle but I thought I had found my way around Word. That was until I wanted to tweak a chart in a way the BIP dialog could not handle, hey, it happens.
Where the heck was was the Alt Text for the sample chart image we drop into the template? In 200 and 2003, just double click -> Web tab and there it is. I gave up and read the manual :0)
Right click your image, select Size and then the Alt Text tab and there it is in all its XML glory ready to be deciphered and modified ... phew!
Microsoft recently released an update to Microsoft Office Word 2007 and Microsoft Office Word 2003. See
http://support.microsoft.com/kb/978951:
This is an update to Microsoft Office Word 2007 (including Office 2007 suites) and Microsoft Office Word 2003 (including the Office 2003 suites). This update was issued as a result of a United States court ruling on December 22, 2009. Generally, customers who purchase or license Word 2007 or Word 2003 from Microsoft after January 10, 2010 for use in the United States and its territories will need to use updated software that does not include a particular custom XML tagging implementation.
Oracle BI Publisher (XML Publisher) has no dependency on the functionality that is being removed. Customers will still be able to create and use BI Publisher RTF layouts using the BI Publisher Template Builder Add-in when installed and used with these updated versions of Microsoft Office Word 2007 and Microsoft Office Word 2003.
Saw a patch release today slipping past my ever growing inbox. It had an intriguing title: OPTIONAL PRINTING OF PAGES IN PDF TEMPLATES, WTF?
Digging into the bug text; some customers would like the ability to not have to print the instruction sheets in their PDF documents. In an RTF template its pretty simple but if the document is already in a PDF format why re-invent the wheel. Its a nice enhancement and available all the way back to 11i (5.6.3)
There are two parts to the solution one piece in the data and another in the template itself. For the PDF template you need to add a field to the instruction page:
Name: SHOW-PAGE
Tooltip:<?show-page:PRINT-INSTRUCTION='Y'?>
this is the default command to print the instructions ie 'Y' or 'N' if you don't want them.
Now, in your data for the form you need to include a specific XML element:
<data>
<form1>Your Text Data</form1>
<form2>1234567890</form2>
<PRINT-INSTRUCTION>N</PRINT-INSTRUCTION>
</data>
Now as BIP merges the data in it will check the XML tag see whether it should be printing the instruction page or not.
The patch is not quite out of the gate but if you are interested look out for 8452335. If you need a template to check out the functionality, get it here.
Happy New Year everyone! Hopefully you are well rested and getting back into the swing of BIP. I took 2 weeks with a huge list of to do's ... sadly I managed only 2/10! A friend once told me to put a couple of things on a list that I had already done so I could tick them off immediately and feel good about my progress :0) Im pleased to say the 2 I did get done were real tasks so all was not lost.
Got a mail today from a customer having some issues conditionally showing bookmarks for a PDF output or a table of contents for HTML output. I blogged some of the solution a while back but thought I would share a template today to avoid any confusion.
Here's the template
- Param - <?param@begin:_xf;"html"?> - this declares the output format parameter that the server will pass to the template at runtime.
- if PDF - <?if:$_xf='pdf'?><?convert-to-bookmark:?> - if the output is going to be PDF then create the book marks
- Then we have the first instance of the TOC in the template
- EI - <?end convert-to-bookmark:?><?end if?> - closing out the if and the bookmarks
- if HTML - <?if:$_xf ='html'?><?copy-to-bookmark:?> - if the output is going to be HTML then leave things as a TOC
- Then we have the second instance of the TOC in the template
- EI - <?end copy-to-bookmark:?><?end if?> - closing the if and copy
Not too tough and once the template is deployed to the server you will get the desired effect in the output of choice. Template available here.
I have seen various postings on being able to use BIP with Avery label templates. Im not limited to Avery here, there are other templates out there too. I have recently been working with John and colleagues over in Oklahoma City who were desperate; desperate might be too strong a word but they needed, at least, to get BIP generating their address labels.
Problem was, existing solutions, my efforts so far have fallen into this category, would work for a page or even a couple but after that things started to slip down the page and you started to get addresses spanning labels and it degenerated into a big mess very quickly.
If you take a look at Avery's templates
You'll notice that they put in the nice Word shapes to show you where the label is on the paper. Looks great and your mind, as a template developer falls immediately to using a shape file and manipulating it for a really groovy BIP template. Sadly, the shape manipulation support is not aware of margins and headers and footers. So it will keep repeating the label shape off the bottom of the page and on to the next. Its not an easy thing to address. I once spoke to Edward (RTF parser builder extraordinaire) about the shape support. They are not bound by the margins, etc so can be placed any where on the page leading to issues when you want repeat them down the page.
So the shape approach is out; I took a closer look at the Avery template.
There is actually a table behind the scenes keeping the shapes in their place. Worth a look. I went down the path of a previous post, using a single row and a for-each loop. I ran into another problem with keeping rows on the page and getting addresses to run across and then down. That was out then.
During this time I was writing the old school cross-tabbing post. That got me thinking about another solution.
Rather than trying to work with a row of a table; how about a single cell and some cross tabbing tricks. Looking closer at the table:
We have that nasty spacer column to deal with. All we need is the top left cell with the spacer column. A little bit of table manipulating and I had a single cell made up of the top left cell extended by the spacer column. Graphic shows the modified top left cell and the next below with the spacer column still present.
If I can repeat that cell across the page and then down I will get the spacing I need to get the document to print on the label paper.
Here's the template:
Not much to it really, just a couple of neat tricks with some help from template meister Hok-Min. Heres the data structure we were working with:
<ADDRESSES>
<ADDRESS>
<STREET>1 Oracle Street</STREET>
<CITY>Redwood Shores</CITY>
<STATE>CA</STATE>
<ZIP>94065</ZIP>
</ADDRESS>
<ADDRESS>
<STREET>2 Oracle Street</STREET>
<CITY>Redwood Shores</CITY>
<STATE>CA</STATE>
<ZIP>94065</ZIP>
</ADDRESS>
<ADDRESS>
...
...
</ADDRESSES>
Here are the fields and their contents:
| FE | <?for-each:ADDRESS[position() mod 3=1]?> |
| FEC | <?for-each@column:. | following-sibling::ADDRESS[position()<3]?> |
| STREET | <?.?> |
| EFE | <?end for-each?> |
| EFE | <?end for-each?> |
. | following-sibling::ADDRESS[position()<3]
command, has the loop only looping triplets of columns across the page. We check that the following record number in the loop is less than 3.
I use <?.?> to just drop in the complete ADDRESS block ie all of its children in one go. you could equally drop in the individual fields too. One word of advice; you may need to adjust the font size to get the correct number of rows to show on each page. The template I was working with was 3x10 and needed 9 point font for Calibri. Arial needed to be 8 point.
If you're interested you can get the RTF template and some sample data for the 3x10 Avery label layout here.
Three folks have asked me about this just today. They wanted to create file names for FTP, Email or WebDAV that have some dynamic component to them e.g. TIM1212009.pdf where the numeric values relate to the date.
Well I learned something today, its supported on the standalone server. Its not that Leslie had not documented it; its just, its tucked away in the API documentation.
You can use the date expressions for a couple of properties. Those expressions will be translated with the actual values just before the delivery. Available expression are following.
- %y : 4 digit year (ex, 1972, 2005)
- %m : 2 digit month (00 - 12)
- %d : 2 digit date (00 - 31)
- %H : 24h based 2 digit hour (00 - 24)
- %M : 2 digit minute (00 - 59)
- %S : 2 digit sec (00 - 59)
- %l : 3 digit millisec (000 - 999)
For example, if you specify my_file_%y%m%d.txt for the filename, the actual filename will be 'my_file_20051108.txt'.
Update
During some testing we have found that the following statement is not quite true. If you use unsupported expressions such as %a%b%b.pdf you file will arrive on the destination server as defined ie %a%b%b.pdf.
All undefined expressions will be translated into 0 length string, for example, if you specify my_file_%a%b%c.txt, it will be my_file_.txt. You can escape the '%' letter by passing '%%'.
The properties that support the date expressions are as follows.
- E Mail file attachment
- FTP Filename
- WebDAV filename
This can be set either in the standalone scheduling page or when using the delivery APIs.
Don't panic folks, I have not become a turn coat. It stems from a question asking how one can remove the default page title from the Publisher server.
maybe this would be better ...
I wrote about changing some of the UI objects a while back the text on the page is another matter entirely.
Just as with the logo images, etc, we are completely off the reservation here, you are on your own. No crying to support that Tim told you to do it; Kevin and the gang will not look favorably on you. They are probably cursing me as they read this. You're big boys and gals now, just remember two words, 'back' and 'up'.
The pages you see in the application are for the most part servlet generated so there are no jsp/html files to update. Almost every string you see is translatable and it therefore exists in an xlf file. These can be updated, at your own risk of course :0)
To make the change you see above, go to the root reports repository directory then to XMLP\Admin\Translation. In there you will find the translation files that the rendering engine uses to look up strings.
In my US-en environment the file I have updated is XMLP_en_US.xlf, the particular string I was changing was this
Just find your appropriate translation file and get experimenting. As for finding which string is which. I had the benefit of the source code but most of the strings in the app are pretty obvious and easy to find in the xlf file.
So, make a back up, change the file, save it and bounce the server to see the change et voila, 'My Cool Publisher Server'!
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!
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.
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.
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.
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.
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.
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!