StayHappy

Friday 17 May 2024

Calling of subtemplate from Main template in BI Publisher in Oracle Fusion

Sometimes there are requirement where header and footer are changing and rest content/body of template is same.

For example there are multiple addresses for any parent company.

In that case we can create subtemplate and call that subtemplate in parent template.

Creating Subtemplate:

In RTF template , do below

<?template:MyAddressUS?>
My Company
500 Main Street
Any City, CA 98765
<?end template?>

?template:MyAddressIndia?>
My Company
500 Main Street
India
<?end template?>

Now we can save that template in fusion, Go to oracle fusion



its extension will be .xsb



Now we need to call that template in our main template, 
wherever we want to call in the main template

Calling of Subtemplate from Main template:
In the Main template enter the following import statements in the form field or directly
anywhere in the template.

<?import:xdoxsl:///Customer Reports/Templates/Sub_Template_Address_XXX.xsb?>
In Main template where you want to show that address, use below Call command
<?call-template:MyAddressUS?>
At runtime, parent template will take care of those path.
To handle parameterized layout , use below command
In the RTF template define a parameter using syntax - 
<?param@begin:ParameterName?>
For example:
<?param@begin:DeptName?>

Happy Learning .

Monday 13 May 2024

How to invoke parametric BIP Report from ESS job

Hi Team,

First create Data mode and report with parameters.

Let suppose 3 parameters

business Unit Id

From date

To Date

Now we need to create parametric ESS job with 3 parameters itself.

Please make sure that order of ESS Job parameters and BIP Data model should be same.

In ESS Job , it should be

Business Unit Id

From Date

To Date.

Please make sure to provide correct path for BIP report also.

In ESS job :

Path should be : /oracle/apps/ess/custom/Financials/XXXX

if report comes under financials and then XXXX

Give exact xdo path for report id

Happy Learning.

Passing date type parameter from an ESS job to BIP SQL data model in oracle fusion application

Hi Team ,

When you are passing date parameter from ESS Job to BIP report then it is not fetching data due to wrong parameters.

When opening XML it is showing

Although we have given DATE parameter in ESS job as

Still it is showing in XML document as wrong format date.

Solution: Make the date format in SQL BIP data model as yyyy-MM-dd

and in ESS Job also , select Date parameter as yyyy-MM-dd

and then submit ESS job, it will display output also and see XML also.

It will display correct output.

Thanks and Happy Reading.

How to use Date function in excel BI Publisher

Formatting Dates

Excel cannot recognize canonical date format. If the date format in the XML data is in canonical format, that is, YYYY-MM-DDThh:mm:ss+HH:MM, you must apply a function to display it properly.

One option to display a date is to use the Excel REPLACE and SUBSTITUTE functions. This option retains the full date and timestamp. If you only require the date portion in the data (YYY-MM-DD), then another option is to use the DATEVALUE function. The following example shows how to use both options.

Example: Formatting a Canonical Date in Excel

Using the Employee by Department template and data from the first example, this procedure adds the HIRE_DATE element to the layout and displays the date as shown in Column E of Figure 6-18.

Figure 6-18 The Employee by Department Template Showing the Hire Date

Description of "Figure 6-18 The Employee by Department Template Showing the Hire Date"

To format the date:

Add a column to the table in your layout for HIRE_DATE.
In the table row where the data is to display, use the Template Builder to insert the HIRE_DATE field.
Note:
If you are not using the Template Builder, copy and paste a sample value for HIRE_DATE from the XML data into the cell that is to display the HIRE_DATE field. For example:
Copy and paste
1996-02-03T00:00:00.000-07:00
into the E8 cell.
Assign the cell the defined name XDO_?HIRE_DATE? to map it to the HIRE_DATE element in the data.
The inserted field is shown in Figure 6-19.
Figure 6-19 Inserting the HIRE_DATE Field

Description of "Figure 6-19 Inserting the HIRE_DATE Field"

If you do nothing else, the HIRE_DATE value is displayed as shown. To format the date as "3-Feb-96", you must apply a function to that field and display the results in a new field.
Insert a new Hire Date column. This is now column F, as shown in Figure 6-20.
Figure 6-20 The New Hire Date Column in Column F

Description of "Figure 6-20 The New Hire Date Column in Column F"
In the new Hire Date cell (F8), enter one of the following Excel functions:
- To retain the full date and timestamp, enter:
```
=--REPLACE(SUBSTITUTE(E8,"T"," "),LEN(E8)-6,6,"")
```
- To retain only the date portion (YYY-MM-DD), enter:
```
=DATEVALUE(LEFT(E8,10))
```
Notice that in both functions, "E8" refers to the cell that contains the value to convert.
After you enter the function, it populates the F8 cell as shown in Figure 6-21.
Figure 6-21 Hire Date Cell (F8) Populated

Description of "Figure 6-21 Hire Date Cell (F8) Populated"
Apply formatting to the cell.
Right-click the F8 cell. From the menu, select Format Cells. In the Format Cells dialog, select Date and the desired format, as shown in Figure 6-22.
Figure 6-22 Applying the Format for the Date in the Format Cells Dialog

Description of "Figure 6-22 Applying the Format for the Date in the Format Cells Dialog"

The sample data in the F8 cell now displays as 3-Feb-96.
Hide the E column, so that report consumers do not see the canonical date that is converted.
Figure 6-23 shows the template with column E hidden.
Figure 6-23 Completed Example

Description of "Figure 6-23 Completed Example"