Thursday, July 28

XSLT - Remove Special Characters Function

Remove Special Characters Function


Which ever characters are allowed, provide it in the select. Anything other than provided characters will be considered as special characters.

<xsl:function name="stripSpecialChars">
    <xsl:param name="string" />
    <xsl:variable name="AllowedSymbols" select ="'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789()*%$#@!~&lt;&gt;,.?[]=- +   /\ '"/>
    <xsl:value-of select="translate($string,translate($string, $AllowedSymbols, ''),' ')"/>
</xsl:function>


<xsl:value-of select="stripSpecialChars($string)"/>


For your information -

&lt;  ---> Less Than Symbol
&gt; ---> Greater Than Symbol

Wednesday, July 27

Calculated Fields and Types

Calculated Fields and Types


Calculated fields are new field definitions that can be configured which in deed allows you to manipulate and derived values based on the existing data. Calculate fields must be associated with business object. Calculated fields can be created using Workday Delivered fields and custom fields as source. Calculated fields determined the value at run time.

Calculated fields can be categorized into 2 ways:

1. System-wide calculated fields
         They are created in a generic way by using the task - Create Calculated Field. They can be used in multiple reports.

2. Report specific calculated fields 
              They are created as part of one report and usage is specific to that report only. They are not reusable by other reports.

Below task allows you to convert System-wide Calc Field to a Report Specific Calc Field.
   Convert Calculated Field

Below task allows you to convert a Report Specific Calc Field to System-wide Calc Field.
   Convert Calculated Field for Report


Also check - Reports / Tasks Associated:

All Calculated Fields
Create Calculated Field
View Calculated Field
Delete Calculated Field
Edit Calculated Field
Maintain Calculated Fields for Report
Calculated Field Exception Audit
Calculated Field Usage Hierarchy
Maintain Calculated Fields
Calculated Fields Defined
Maintain Calculated Fields for Report
Recently Updated Calculated Fields
Report Specific Calculated Field Exception Audit

Tuesday, July 26

XSLT - Calculate Week Number based on date

 Calculate Week Number based on date


Template with Parameters:

Defining Template 1- [This template calls another template below]

   <xsl:template name="calculate-week-number">
        <xsl:param name="year"/>
        <xsl:param name="month"/>
        <xsl:param name="day"/>
        
        <xsl:variable name="j-day">
            <xsl:call-template name="calculate-julian-day">
                <xsl:with-param name="year" select="$year"/>
                <xsl:with-param name="month" select="$month"/>
                <xsl:with-param name="day" select="$day"/>
            </xsl:call-template>
        </xsl:variable>
        
        <xsl:variable name="d4" 
            select="($j-day + 31741 - ($j-day mod 7)) 
            mod 146097 mod 36524 mod 1461"/>
        
        <xsl:variable name="L" select="floor($d4 div 1460)"/>
        
        <xsl:variable name="d1" select="(($d4 - $L) mod 365) + $L"/>
        
        <xsl:value-of select="floor($d1 div 7) + 1"/>
        
    </xsl:template>

Defining Template 2 - [Used by above template]

    <xsl:template name="calculate-julian-day">
        <xsl:param name="year"/>
        <xsl:param name="month"/>
        <xsl:param name="day"/>
        
        <xsl:variable name="a" select="floor((14 - $month) div 12)"/>
        <xsl:variable name="y" select="$year + 4800 - $a"/>
        <xsl:variable name="m" select="$month + 12 * $a - 3"/>
        
        <xsl:value-of select="$day + floor((153 * $m + 2) div 5) + $y * 365 + 
            floor($y div 4) - floor($y div 100) + floor($y div 400) - 
            32045"/>
        
    </xsl:template>


Calling Template - [The returned value is stored in the variable WeekNumber]

<xsl:variable name="WeekNumber">            
    <xsl:call-template name="calculate-week-number">
        <xsl:with-param name="day" select="day-from-date($Start_Date)"/>
        <xsl:with-param name="month" select="month-from-date($Start_Date)"/>
        <xsl:with-param name="year" select="year-from-date($Start_Date)"/>
    </xsl:call-template>
</xsl:variable>

Monday, July 25

XSLT Functions with Examples

 XSLT Functions with Examples


Always remember while you are coding XSL Transformation, your input for your xsl is XML. 
i.e. your XSL code is based on the XML.

Below are most commonly used XSLT coding functions and methods. This is not an exhaustive list though.

How to Declare a variable:

<xsl:variable name="Newline" select="'&#xa;'"/>
<xsl:variable name="Tab" select="'&#9;'"/>
                <xsl:variable name="NumberFormat" select="'0.00'"/>
                <xsl:variable name="DateFormat" select="'[M01]/[D01]/[Y0001]'"/>
                <xsl:variable name="Pipe" select="'|'"/>
                <xsl:variable name="SplChar" select="'$@!%^&*()- '"/>
                <xsl:variable name="FillerZeros" select="'0000000000000000000000'"/>


How to use the variable which was declared: [Use $]

          <xsl:value-of select="$Newline"/>


Simple Text:
    <xsl:text>Test</xsl:text>


Concat Function:

   Example1:    <xsl:value-of select="concat(wd:Email,$Pipe)"/>
   Example2: <xsl:value-of select="concat('Y',$Pipe)"/>


Translate Function: [Use when you want to replace something]

       <xsl:value-of select="translate(wd:phone, '+().- ', '')" />


Sort Usage:

        <xsl:sort select="wd:Location"/>  


String-length Function:

         <xsl:when test="string-length(wd: Zip) = 8">  
Your Values Here
         </xsl:when>


Exists Function:

           <xsl:when test="exists(wd:Primary_Email)">   
Your Values Here
           </xsl:when>


Count Function:

   Example 1:        <xsl:value-of select="count(//wd:Report_Entry)"/>
   Example 2:        <xsl:value-of select= "count (//wd:Report_Entry[wd:Benefits/wd:Coverage='Family']/wd:Dependents)"/>


Format Date Function:

     Example 1:   <xsl:value-of select="format-date(current-date(),'[Y0001]-[M01]-[D01]')"/>
     Example 2:   <xsl:value-of select="format-date(wd:DateOfBirth,'[Y0001][M01][D01]')"/>


Format Number Function:

    <xsl:value-of select="format-number(wd:amount,$numberFormat)"/>


Substring Function:

         <xsl:value-of select="substring(wd:Work_Phone,3,10)"/>


Substring Before Function:

        <xsl:value-of select="substring-before(wd:Division/@wd:Descriptor,':')"/>


If Condition:

    <xsl:if test="wd:Gender = 'M'">
             .
             .            
            Your code here
             .
             .
    </xsl:if>


Choose and When Function:

  <xsl:choose> 
             <xsl:when test="(wd:Worker_Status = 'On Leave')"> 
                     <xsl:value-of select="'L'"/> 
             </xsl:when>
             <xsl:otherwise> 
                     <xsl:value-of select="'A'"/> 
             </xsl:otherwise> 
 </xsl:choose>


'For' Loop Function:

    Example 1:

   <xsl:for-each select="wd:Report_Data/wd:Report_Entry">
             .
             .            
            Your code here
             .
             .
       </xsl:for-each> 

    Example 2: [Using Group-by]

        <xsl:for-each-group select="wd:Report_Data/wd:Report_Entry" group-by="wd:Location">
             .
             .            
            Your code here
             .
             .
       </xsl:for-each> 


Other Text conditions in When Fuction:

Condition 1:
          <xsl:when test="(wd:Class_Item = '001' or wd:Class_Item = '002')">     
Your Values Here
  </xsl:when>

Condition 2:
         <xsl:when test="(wd:Benefits[1]/wd:Enrolment_Date) != ''">
Your Values Here
         </xsl:when> 

Condition 3:
         <xsl:when test="(wd:Class_Item = '002' and wd:Benefit_Changes[1]/wd:SubType ='T')">    
Your Values Here
         </xsl:when>


Template Declaration and Usage:

Calling Templates -

    <xsl:template match="/">
        <xsl:call-template name="HeaderRecord"/>
        <xsl:call-template name="DetailRecords"/>
    </xsl:template>

Defining Template -

    <xsl:template name="HeaderRecord">
             .
             .            
            Your code here
             .
             .
    </xsl:template>

Wednesday, July 20

MIME

 Multipurpose Internet Mail Extensions

MIME is the acronym of Multipurpose Internet Mail Extensions. MIME type is an optional field on your integrations. 

Instead of you defining your extension along with your File name, you may choose among the listed extension types for the output file. This option doesn't change the actual file format. To change the file format you need to select an alternate output format in Get Data or modify your transformation in Transformation Data tabs.



Friday, July 8

Calculated Fields - Acronyms

Calculated Fields - Acronyms

There are around 34 functions that you can use to create any calculated fields for any desired available BO. 

When trying to create a Calculated Field using one the available functions, while searching for desired function (in task Create Calculated Field ) you can simply type the acronym to find it.

Also notice the input field type (i.e. source field) and the Ouput Field type from each Calc Function below.

Calculated Field Function

Acronym

Input Field Type(s)

Output Field Type

Aggregate Related Instances

ARI

Multi Instance

Multi Instance

Arithmetic Calculation

AC

Numeric or Currency

Numeric 

Build Date

BD

Date or Numeric 

Date

Concatenate Text

CT

Single Instance or Text

Text

Convert Currency

CC

Currency

Currency

Convert Text to Number

CTN

Text

Number

Count Related Instances

CRI

Multi Instance

Number

Date Constant

DC

*Free Date Format 

Date

Date Difference

DD

Date

Numeric

Evaluate Expression

EE

Any 

Any

Evaluate Expression Band

EEB

Text

Single Instance

Extract Multi Instance

EMI

Multi Instance or Single Instance

Multi Instance

Extract Single Instance

ESI

Multi Instance

Single Instance

Format Date

FD

Date

Text

Format Number

FN

Number or Currency

Text

Format Text

FT

Text or Single Instance

Text

Increment or Decrement Date

IDD

Date

Date

Lookup Date Rollup

LDR

Date

Single Instance

Lookup Hierarchy

LH

Single Instance

Single Instance

Lookup Hierarchy Rollup

LHR

Single Instance

Single Instance

Lookup Organization

LO

N/A

Single Instance / Multi Instance

Lookup Organization Roles

LOR

N/A

Single Instance / Multi Instance

Lookup Range Band

LRB

Numeric or Currency

Single Instance

Lookup Related Value

LRV

Single Instance

Any

Lookup Translated Value

LTV

Text

Text

Lookup Value as of Date

LVD

Any

Matches Input Value Type

Numeric Constant

NC

*Free Number Format 

Numeric

Prompt for Value

PV

Any

Matches Input Value Type

Substring Text

ST

Text or Single Instance

Text

Sum Related Instances

SRI

Single Instance or Multi Instance

Numeric

Text Constant

TC

*Free Text Format 

Text

Text Length

TL

Text or Single Instance

Numeric

True/False Condition

TF

Any

Boolean