xdo.XDOException When Using subtemplate from Desktop

Apparently Oracle put a new hook in when viewing a report from the BI Publisher desktop preventing the import and throwing an error.

You can read about Subtemplates Failing? in Tim Dexter's blog.

The fix worked for me by reading the blog on Learn Oracle Applications Tech-Stack.

If you want to cut to the chase then it is a matter of making a modification to the xdo.cfg file. On my installation that file did not exist but there was a file called xdo example.cfg file.

Here are the steps I took to make the modifications:

1. I right-clicked on the Notepad icon and chose "Run as administrator".  I had to do this because of the security in the folder hierarchy, I couldn't save it otherwise.
2. I opened C:\Program Files (x86)\Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\config example.cfg
3. I added this line: <property name="xdk-secure-io-mode">false</property>
4. I saved the file in the same location calling it xdo.cfg

You should be good to go!!

Graph Information

Graph Informatoion

Migrating Reports in 11g

Someone asked me the question about how you migrate BIP 11g reports from one environment to another.  Here is the document on how to do it:

XPATH/XSLT Functions that Can Be Used

Search a string.
<?contains(string-to-search-in, string-to-search)?>

If you want to get a distinct count of a data element.
<?count(xdoxslt:distinct_values(JOBID))?> 

An if-else statement.
<?xdoxslt:ifelse(SEX='M', 'Man', 'Woman')?>
 If you want to nest it. You can nest it many times.
<?xdoxslt:ifelse(SEX='M', 'Man', xdoxslt:ifelse(SEX='T','Transgender','Woman'))?>

Safe divide function returns a specified value if the result of the function is NaN. In the syntax shown, num1 is the dividend; num2 is the divisor and string is the value to be returned if NaN is returned.
Examples: <?xdoxslt:sdiv(10,0, '0')?> would yield '0'
<?xdoxslt:sdiv(10,0, 'None')?> would yield 'None'  

<?xdoxslt:sdiv(num1,num2, string)?> 

If you want to use sdiv in calculation like <?xdoxslt:sdiv(4,5,0)  + 5?> then this will not work.
You have to use the function number to convert the result.  So it would be like this:

<?number(xdoxslt:sdiv(4,5, 0)) + 5?>

String Replacement
<?xdoxslt:replace(xdoxslt:replace(PV_FY,'[',''),']','')?>

The example shown is the replace function being nested.

Rounding 
<?xdoxslt:round(some_num, 2)?>
Rounding to 2 places so 100.125 becomes 100.13

Naming an Excel sheet in an RTF template:
<?spreadsheet-sheet-name: {xpath-expression}?>

Assuming an XML element has the name SHEET_NAME
<?param@begin:sheet;SHEET_NAME?>
<?spreadsheet-sheet-name: {$sheet}?>

Get Environment Information within the Template
<?xdoxslt:getXDOProperties($_XDOCTX)?>

Handling Dates in BI Publisher Excel Templates

You may have struggled with merging date XML data into an Excel worksheet cell. Changing the Format Category does not make it an Excel stored date.

Excel dates are stored as a serialized number. Manually input a date into an Excel cell and you'll see a date (example: 1/6/2009). When you go to Format then the Category will be a Date.



Now change it to General. The result:

Where does that number come from? Excel counts the number of days since January 1, 1900 and comes up with a number. There are a couple of funky aspects that you should be aware of. The count starts at one and the other consideration is that Excel considers the year 1900 a leap year (a bug) and it is not. There is no such date as February 29, 1900 but it is a date in Excel. So you have to add 2 after you subtract the dates.

Cut to the chase
In Oracle SQL it is simply: SELECT DATE1 - TO_DATE('01/01/1900','MM/DD/YYYY') + 2 DATE_SER FROM......
The next step is to define the target cell in Excel with the Category of date.

You can easily test this out with this SQL:
SELECT trunc(sysdate) - TO_DATE('01/01/1900','MM/DD/YYYY') + 2 DATE_SER FROM dual

I usually want to keep the serialized date and the original date in my SELECT. So if I have a DUE_DATE then I may name the serialized one as DUE_DATE_SER.

Function 
I've created a function to serialized the date:

create or replace FUNCTION TO_EXCEL_SER_DATE
-- Created by: Jim O'Connor
-- Create date: 07/28/2017
-- Converts a date data type to an Excel serialized date
(P_DATE DATE)
   RETURN NUMBER
is
BEGIN
     RETURN (P_DATE - TO_DATE('01/01/1900','MM/DD/YYYY') + 2) ;
END;

Prevent Page Break on Group of Data

You have a situation that you want to keep a group of data together on the same and you don't want it to break on separate pages like this:





A method to keep the group together is not to put the data on separate rows on the table but to use one row like this:



Putting multiple lines on one row still won't prevent a break on separate pages, so there is one more step to tell MS Word not to break across pages. Go to table properties and uncheck "Allow row to break across pages".

Valdiate Template

You develop an RTF template and it works fine from your desktop.  You upload your template to the BI publisher server then run it and nothing happens.  What could go wrong?

Unfortunately, previewing the template from the desktop is more forgiving on errors.  You can try validating the template by going to the BI Publisher menu item and validate the template:

BI Publisher Menu

If there are errors or not then this window will appear:

If there are errors then the errors will be listed in the window.  An example of an error is not closing a for-each loop.