Some times we need to call the Logo’s Dynamically into BI Publisher RTF Templates for Employee Photos,Vendor Logo’s,Company Logo’s etc. Generally in PeopleSoft all the Images and logos are stored BLOB Format these BLOB Format Images are not directly Displayed in BIP RTF Templates for that Oracle comes with the utility to convert BLOB Images to base64 format.
SQL to Convert the BLOB to base 64 Link:-
Example for Converting Image File to Base-24:
/* Read the Image file as a base64 string */ &Image_File = GetFile(&FilePath, "R", %FilePath_Absolute); &Str_Base64 = &Image_File.GetBase64StringFromBinary(); &Image_File.Close();
/* write back to the same location with the same data */ &Image_File = GetFile(&FilePath, "W", %FilePath_Absolute); &Image_File.WriteBase64StringToBinary(&Str_Base64); &Image_File.Close();
In RTF Template we need to write belows tag of code for Image Field.
<fo:instream-foreign-object content-type=”image/jpg”>
<xsl:value-of select=”.//A.EMPLOYEE_PHOTO”/>
</fo:instream-foreign-object>
Sample Code to Generate XMl File for Printing Employee Photo is below.
import PSXP_RPTDEFNMANAGER:*;
import PSXP_XMLGEN:RowSetDS;
Local PSXP_RPTDEFNMANAGER:ReportDefn &report;
Local PSXP_XMLGEN:RowSetDS &rosetDS;
Local string &strSQL;
Local PSXP_RPTDEFNMANAGER:ReportDefn &objReportdefn;
Local Record &record;
Local Rowset &Rowset;
Local string &strRptDefnId, &strTemplateId, &strLanguageCd, &strOutputfmt, &strOutDest, &strFileName, &strXMLFileName;
Local date &dtAsofdate;
Local Rowset &rsOutput;
Local File &SchemaFile, &XMLFile;
Local SQL &sql;
Local XmlNode &xRootNode;
Local XmlNode &xEmplid_Details, &xEmplid, &xEmployee_Photo, &xPhotoSizeName;
Local XmlDoc &inXMLDoc;
Local string &strXMLData;
rem Local date &strXMLdate;
Local string &strEmplid, &PhotoSizeName, &employeePhoto;
rem local image &employeePhoto;
&inXMLDoc = CreateXmlDoc(“<?xml version=’1.0′, encoding=’UTF-8’STANDALONE=’yes’?><ROOT/>”);
&inXMLDoc.DocumentElement.AddAttribute(“xmlns:xsi”,“http://www.w3.org/2001/XMLSchema-instance”);
&xRootNode = &inXMLDoc.DocumentElement.AddElement(“ROOT”);
rem &strSQL = “SELECT DISTINCT A.EMPLID, A.EMPLOYEE_PHOTO,A.PHOTO_IMGNAME ,A.PHOTO_SIZENAME FROM PS_EMPL_PHOTO A WHERE ( A.PHOTO_SIZENAME = ‘PAGE’)”;
&strSQL = “SELECT EMPLID,”;
&strSQL = &strSQL | “CASE WHEN DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) > 1455”;
&strSQL = &strSQL | “THEN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(DBMS_LOB.SUBSTR(EMPLOYEE_PHOTO, 1455, 1)))”;
&strSQL = &strSQL | “WHEN DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) <= 1455 THEN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(EMPLOYEE_PHOTO)) END AS C1,”;
&strSQL = &strSQL | “CASE WHEN DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) > 2910 “;
&strSQL = &strSQL | “THEN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(DBMS_LOB.SUBSTR(EMPLOYEE_PHOTO, 1455, 1456)))”;
&strSQL = &strSQL | “WHEN DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) <= 2910 AND DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) > 1455”;
&strSQL = &strSQL | “THEN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(DBMS_LOB.SUBSTR(EMPLOYEE_PHOTO, DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) – 1455, 1456))) END AS C2,”;
&strSQL = &strSQL | “CASE WHEN DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) > 4365”;
&strSQL = &strSQL | “THEN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(DBMS_LOB.SUBSTR(EMPLOYEE_PHOTO, 1455, 2911))) “;
&strSQL = &strSQL | “WHEN DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) <= 4365 AND DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) > 2910”;
&strSQL = &strSQL | “THEN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(DBMS_LOB.SUBSTR(EMPLOYEE_PHOTO, DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) – 2910, 2911))) END AS C3,”;
&strSQL = &strSQL | “CASE WHEN DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) > 5820”;
&strSQL = &strSQL | “THEN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(DBMS_LOB.SUBSTR(EMPLOYEE_PHOTO, 1455, 4366))) “;
&strSQL = &strSQL | “WHEN DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) <= 5820 AND DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) > 4365”;
&strSQL = &strSQL | “THEN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(DBMS_LOB.SUBSTR(EMPLOYEE_PHOTO, DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) – 4365, 4366))) END AS C4,”;
&strSQL = &strSQL | “CASE WHEN DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) > 7275 “;
&strSQL = &strSQL | “THEN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(DBMS_LOB.SUBSTR(EMPLOYEE_PHOTO, 1455, 5821)))”;
&strSQL = &strSQL | “WHEN DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) <= 7275 AND DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) > 5820 “;
&strSQL = &strSQL | “THEN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(DBMS_LOB.SUBSTR(EMPLOYEE_PHOTO, DBMS_LOB.GETLENGTH(EMPLOYEE_PHOTO) – 5820, 5821))) END AS C5,PHOTO_IMGNAME ,PHOTO_SIZENAME FROM PS_SY_EMPL_PHOTO”;
&sql = CreateSQL(&strSQL);
While &sql.Fetch(&strEmplid, &employeePhoto1, &employeePhoto2, &employeePhoto3, &employeePhoto4, &employeePhoto5, &Photo_ImgName, &PhotoSizeName)
rem MessageBox(0, “”, 0, 0, “Hi :” | &strEmplid);
&xEmplidDetails = &xRootNode.AddElement(“Employee_Details”);
&xEmplid = &xEmplidDetails.AddElement(“Emplid”);
&xEmplid.NodeValue = String(&strEmplid);
&xEmployee_Photo = &xEmplidDetails.AddElement(“EMPLOYEE_PHOTO”);
&xEmployee_Photo.NodeValue = String(&employeePhoto1) | String(&employeePhoto2) | String(&employeePhoto3) | String(&employeePhoto4) | String(&employeePhoto5);
&xEmployee_Photo_Img = &xEmplidDetails.AddElement(“PHOTO_IMGNAME”);
&xEmployee_Photo_Img.NodeValue = String(&Photo_ImgName);
&xPhotoSizeName = &xEmplidDetails.AddElement(“PHOTO_SIZENAME”);
&xPhotoSizeName.NodeValue = String(&PhotoSizeName);
End-While;
&strXMLFileName = “E:\Sample.XML “;
&fXMLFile = GetFile(&strXMLFileName, “w”, %FilePath_Absolute);
If &fXMLFile.IsOpen Then
&strXMLData = &inXMLDoc.GenFormattedXmlString();
&fXMLFile.WriteLine(&strXMLData);
&fXMLFile.Close();
Else
Error (“Temporary XML file creation failed. Please contact your System Administrator”);
End-If;
Example for Case Construct in BIP Report
<?choose:?><?when:Company=‘IBM‘?>
<fo:instream-foreign-object content-type=”image/jpg”>
<xsl:value-of select=”.//IBM_LOGO”/>
</fo:instream-foreign-object>
<?end when?>
<?when:Company=‘WIPRO‘?>
<fo:instream-foreign-object content-type=”image/jpg”>
<xsl:value-of select=”.//WIPRO_LOGO”/>
</fo:instream-foreign-object>
<?end when?>
<?when:Company=‘TCS‘?>
<fo:instream-foreign-object content-type=”image/jpg”>
<xsl:value-of select=”.//TCS_LOGO”/>
</fo:instream-foreign-object>
<?end when?>
<?otherwise:?>
No Image/Logo found.
<?end otherwise?>
<?end choose?>
Another Example link below:-
Sending images to BI Publisher
BI Publisher: Conditional Format
Conditional Format is similar to Conditional Region except it allows for a formatting change based on a condition. This example will set the row background to red and font color to white for records where Status is equal to ‘Quote_Received’.
Using the original Tech Tip RTF template, place the cursor just before the Status placeholder and click Conditional Format in the BI Publisher ribbon. Select the ‘Status’ Data Field with type ‘Date/Text’ and in Condition 1 select the operator ‘Equal to’ ‘Quote_Received’ in the corresponding field. Check the ‘Apply to Entire Table Row’
Click the Format button and check the Background Color and select the color red. Similarly check the Font Color and select the color white. Click OK.
In this instance there is no separate ‘end if’ tag as this is included in the code for the C placeholder.
Opening the C placeholder with the Field Browser the ‘end if’ is at the end. This is because the code is specified for in context (within the cell). It would affect whichever cell this placeholder is inserted. With the ‘Apply to Entire Table Row’ checked, the code @row extends the in context to all cells in the row.
Placeholder C:
<?if@row:STATUS=’Quote_Received’?>
<?attribute@incontext:background-color;’Red’?>
<?attribute@incontext:color;’White’?>
<?end if?>
Previewing would generate the following output.
Multiple conditions can be set (see Condition 2 section). By default, the properties window provides up to two conditions but more could be added to the code. The additional condition added is Background of Green and font color white if Status is ‘NTP_Acknowledged’.
It can be seen from the code the conditions are working independently of each other. Each one is its own in context formatting IF condition.
Placeholder C:
<?if@row:STATUS=’Quote_Received’?>
<?attribute@incontext:background-color;’Red’?>
<?attribute@incontext:color;’White’?>
<?end if?>
<?if@row:STATUS=’NTP_Acknowledged’?>
<?attribute@incontext:background-color;’Green’?>
<?attribute@incontext:color;’White’?>
<?end if?>
- Using IF THEN ELSE
This option uses the extended XSL and SQL functions (xdofx) provided by BI Publisher. Use the Field Browser on the selected STATUS placeholder to edit the code in place.
The code to insert combines IF THEN ELSE function with the AND operator.
With this example if the STATUS is equal to ‘Quote_Received’ AND VENDOR_ID equals ‘CPM’ then display ‘Received from CMP’ otherwise display the returned value from Status. Click Update after insertion.
Code to insert:
<?xdofx:if STATUS = ‘Quote_Received’ and VENDOR_ID = ‘CPM’
then ‘Received from CMP’
else STATUS
end if?>
* Note the syntax is case-sensitive.
Previewing would generate the following output.
In summary, this Tech Tip provided some different conditional options to use, whether by hiding, formatting or affecting what is displayed. The flexibility they provide assists in satisfying many client specific reporting requirements.
