Tuesday, January 3, 2012

My BI Quick Reference

I know there are a bunch of cheat sheets out there, but I frequently don't find everything I am looking for in one place so figured I would just start building my own.

Siebel Functions

Include statement

<?namespace:psfn=http://www.oracle.com/XSL/Transform/java/com.siebel.xmlpublisher.reports.XSLFunctions?>

Date Conversion

<?psfn:totext(OrderDate,"MM/dd/yyyy","MM/dd/yyyy hh:mm:ss")?>

Loops:

Basic Loop where QuoteItem is the Integration Component/XML Group
<?for-each:QuoteItem?>
Add a where clause to constrain rows in the loop.  Multiple constraints can be added back to back with each bracketed section representing an AND.  An OR would need to be done inside a single bracketed expression.  the .// is an xpath expression to determine the XML group of the field
<?for-each:QuoteItem[.//LineType='Sales']?>
<?for-each:QuoteItem[.//LineType='Sales'][.//LineNumber<100]?>
<?for-each:QuoteItem[.//LineType='Sales' or .//LineType='Service']?>
Groupings 

Loop that groups by the column LineType and sorts by the grouping
<?for-each-group:QuoteItem;./LineType?><?sort:current-group()/LineType;'ascending';data-type='text'?>
A nested loop showing the sub group records for the loop above:
<?for-each:current-group()?><?sort:Product;'ascending';data-type='text'?>

Loop that groups records together by a particular column and makes each grouping a column in a table
<?for-each-group@column:QuoteItem;./LineType?>
Similar to grouping by column is to group by section.  In this case each grouping creates a heading when the report breaks across multiple pages.
<?for-each@section:G_CUSTOMER?>

Group Expressions

This expression is used to sum a column from series of records outside the context of a loop.  The expression in the bracket specifies those records to include, in this case only non null values.  This is an XPath expression.
<?sum(.//ItemExtendedPriceTotal[.!=''])?>
Conditionals:

If/else
<?if:Comment!=''?>
<?Comment?>
<?end if?>

Keep in mind that Carriage returns outside the expression will still appear so consider this when judging where to put the donditional

Switch/Case/Select/Choose:

<?choose:?> <?when:MY_FIELD='value tested'?> <?call:template?> <?end when?> <?when:MY_FIELD_2='value tested 2'?> <?call:template_2?> <?end when?> <?otherwise:?> <?call:template_other?> <?end otherwise?> <?end choose?> 

Embedding a 64 Bit Image

For this to work, there must be a 64 Bit attachment embedded in the Integration Object being sent to BI. In this example,QuoteAttachment is the element name of the IC Field of type DTYPE_ATTACHMENT containing the 64 Bit inline image.
<fo:instream-foreign-object content-type="image/jpg"><?QuoteAttachment?></fo:instream-foreign-object>
Additional attributes for the fo:instream-foreign-object tag in addition to content-type can resize the image: Specify in pixels as follows:

<fo:instream-foreign-object content type="image/jpg" height="300 px" width="4 px">

... or in centimeters:
<fo:instream-foreign-object content type="image/jpg" height="3 cm" width="4 cm">
... or as a percentage of the original dimensions:
<fo:instream-foreign-object content type="image/jpg" height="300%" width="300%"> ...