Dynamic Sorting

I am an advocate of sorting, aggregating, performing computations and arranging a hierarchy when  forming the xml data so that it does not have to be done in the RTF template.  This simplifies the xpath/xslt scripting that needs to be done in the RTF template.

On the other hand, there can be times you don't have control on how the xml data feed is built especially when the source you have to work with is the xml data.  The problem I faced is that the xml data was sent by Siebel integration via web services.  A requirement of a report was to dynamically change as to which data element to sort on depending on the setting of a parameter.

Snipet of  xml data

For this example I am using the parameter SortBy.  The option is to either sort on ENAME or MGR.  First I will declare the parameter on the RTF template like:

<?param@begin:SortBy;'"MGR"'?>

The sort goes right after the for loop:

<?for-each:ROW?>

<?sort:xdoxslt:ifelse($SortBy=’EMP’,ENAME,MGR);'ascending';data-type='text'?>

Yes it is that simple.  You might be asking where xdoxslt:ifelse comes from.  This is one of those many undocumented features.  As you can see, the way ifelse works is that if the comparison is true then ENAME else MGR.  You are are allowed to layer multiple ifelse's.

Another undocumented feature that I found when using the sort statement is lang='no'. So then it becomes: 
<?sort:xdoxslt:ifelse($SortBy=’EMP’,ENAME,MGR);'ascending';data-type='text',lang='no?>.  What does this do for you?  If the data element, that you're sorting on, contains alphanumeric characters, then sorting will not sort correctly.

Combined Running Page and Reset Page Numbers

I had a problem that a client wanted a report such that the page number in the header would reset every time there was a new order number.  At the same time, the client wanted a running page number on the footer.

This was tricky because it is easy to have one or the other but not both.  Hok Min (sp?) from Oracle Product Support provided me with a solution.  Here his the code he gave me:

 <fo:page-number xdofo:report-page-number="true"/>

The problem with using this code is that for it to work, it has to go in a form field.  This presents a problem because headers and footers cannot have form fields in an RTF template.  The work around is to define a subtemplate and to call the subtemplate from the footer.  Understand that subtemplates do not have to be on a separate RTF template but they can exist on the same RTF template.  When I do it this way, I usually put the definitions at the end of the RTF template that I am working on.

Here is the definition of the subtemplate:

<?template:pageno?>TotPageNo<?end template?>

The form field contains the code above in the blue text.

Now to use it in a footer:


--------------------------------------------------------------------------------------
footer section

                                            <?call:pageno?>

For Loops

For Loops

There are some instances that you may want to have a for loop and not have it driven by the XML data coming in.

It is in the user guide in the section where shapes are discussed but it is not elaborated at all.  I used it in my "Dynamic Formatting" blog.

Here is the construct of the loop:

<?for-each@inlines:xdoxslt:foreach_number($_XDOCTX,1,3,1)?><-some text-><?end for-each?>

$_XDOCTX,1,3,1 - This number will be the start of the count.

$_XDOCTX,1,3,1 - The loop ends when this number is reached.

$_XDOCTX,1,3,1 - This number is used as the increment of the count.

The output of the above example will be:  <-some text-><-some text-><-some text->

If you remove the @inlines then the output text will be:

<-some text->

<-some text->

<-some text->

Instead of hard coding the three numbers, a variable can be used in one or more places.

BI Publisher MS Word Add-in: The macro cannot be found or has been disabled because of your macro security settings

All of a sudden your MS Word Add-in will quit working with this error message:  The macro cannot be found or has been disabled because of your macro security settings.

Windows Common Control-based embedded ActiveX controls may fail to load within pre-existing Office documents, within third-party add-ins, and when you insert new controls in developer mode. 

This was caused by the MSCOMCTL.OCX file not being registered properly after after an MS Office security update.

You will look on the web and find all kinds of gyrations that they make you do but it is not necessary.  

This is all you have to do:

Note You must run the commands from commands at an elevated command prompt with administrator permissions. To do this, follow these steps:

1. Click Start, type cmd.
2. Right-click the cmd icon, and then click Run as Administrator.
3. Depending on your operating system, type the either of the following commands, and then press Enter:
   • For 64-bit operating systems, type the following:
     Regsvr32 "C:\Windows\SysWOW64\MSCOMCTL.OCX"
   • For 32-bit operating systems, type the following:
     Regsvr32 "C:\Windows\System32\MSCOMCTL.OCX"

Dynamic Formatting- BI Publisher

+Kan Nishida
A client wanted to give their users the ability to bold and italicize text for various verbiage in a contract.  The interface is Siebel Hospitality.

I chose an uncommon character, caret (^) to delimit the text.  The caret at the beginning of the text segment turns on the bold/italics and the caret at the end of the text segment turns off the formatting.  You can use this many times in the text.

The pseudo code is as follows:

Count the number of characters in the text.

Starting at the beginning of the text, look at each character.  

If the character is other than the tilde then just output it.

If the character is a tilde then just set a flag.

There are two form fields. One is for plain text and the other is for formatted text.  It the flag is set then print the character in the formatted form field otherwise, print the character in the unformulated form field.

For some reason, spaces are ignored so code has been added to put the spaces in.

Here is the code (note: code can be put in form fields):

The test text:
<?variable:s;string(‘Now is the time for all ^good men^ to come to the aid of their ^commarades^.’)?>
Initialize and set the flag variable "hit":
<?xdoxslt:set_variable($_XDOCTX,'hit',1)?>

 Display the test text: <?$s?>

This is the loop and all the code should go on one line:

 <?for-each@inlines:xdoxslt:foreach_number($_XDOCTX,1,string-length($s),1)?>

 This code sets the flag on and off when it hits the delimiter character.  In my case it is the caret. 

<?if@inlines:substring($s,position(),1) = '^'?><?xdoxslt:set_variable($_XDOCTX,'hit',xdoxslt:get_variable($_XDOCTX,'hit') * -1)?> <?end if?>

The code below should go in a form field with plain text.

<?xdoxslt:ifelse(xdoxslt:get_variable($_XDOCTX,'hit') = -1 ,'',xdoxslt:ifelse(substring($s,position(),1) = '^','',substring($s,position(),1)))?>

The code below should go in a form field with formatted text.  In my case it will be bold and italicized:

<?xdoxslt:ifelse(xdoxslt:get_variable($_XDOCTX,'hit') = -1 ,'',xdoxslt:ifelse(substring($s,position(),1) = '^','',substring($s,position(),1)))?> 

Insert this code in a form field before a space:

<?if@inlines:substring($s,position(),1) = ' '?> 

Insert this code in a form field after a space:

<?end if?> 

  Close the loop.

<?end for-each?>

Generating Consecutive Dates

Below is technique that I used to generate rows of consecutive dates. I had a report that required labor costs for each day of between two dates. This same technique can be used to generate a particular number of rows. This is one place where you can intentionally use a cartesian product.

Here is an example that can be executed in SQL*Plus

variable p_start_mm varchar2(4)
variable p_start_yyyy varchar2(4)
variable p_end_mm varchar2(4)
variable p_end_yyyy varchar2(4)
exec :p_start_mm := '01'
exec :p_start_yyyy := '2012'
exec :p_end_mm := '04'
exec :p_end_yyyy := '2012'
select to_char(ROWNUM + to_date(:p_start_mm :p_start_yyyy, 'MM-YYYY') - 1, 'DD-MON-YYYY') gen_date from dual connect by level <= last_day(to_date(:p_end_mm :p_end_yyyy, 'MMYYYY')) - to_date(:p_start_mm :p_start_yyyy, 'MMYYYY') + 1;