Dynamically Changing Layout Sizes

If you want to dynamically change layout sizes for example you need legal size and sometimes you need letter size.

It is very simple to do.  In MS Word, you create a section break every time you have a different layout size.  In the example below there will two sections, one for legal and one for letter size.  In each section you code it like this:

<?if@section:P_SIZE='LEGAL'?>

<< ---- y o u r  l e g a l  s i z e   l a y o u t ---->>

<?end if?>

<?if@section:P_SIZE='LETTER'?>

<< ---- y o u r  l e t t e r  s i z e   l a y o u t ---->>

<?end if?>

Understand that the coding above resides in the respective section.   P_SIZE is a parameter defined in the data model but it could be anything.

I want to give credit to Vishal Pathak for posting this on his blog.

Dynamically Changing Font and Font Attributes

In the XDOUserGuide there is a section of Supported XSL-FO Elements (D1).  The section does not have any examples of how to use them.

I have created an example on how you may go about dynamically change the size of a font.

First I created a parameter called "fsize" to simulate varying the font size:

<?param@begin:fsize;string('20pt')?>

Below is the code that I will use.  It is mandatory that everything you see below is to go into a form field.

<fo:inline font-family="Arial" font-size="{$fsize}" font-weight="bold" font-style="italic"   hyphenate="yes "text-align="center">Now is the time for all good men to come to the aide of their comrades.</fo:inline>

The tricky part was to introduce the variable (fsize) into the markup. You have to encapsulate the variable with "{var}". Note that param's are referenced with a prefix of "$".

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;