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;