soalicious

Monday, December 19, 2022

ERP BI - Case Study 2 - Create RTF template in MS WORD BI Publisher

Use case: we will perform the following in the RTF template:

Show data in tabular format and provide a report name.
Repeat table header in each page
Sort data in Ascending order on Invoice Number

<?sort:INVOICE_NUM;'Ascending'?>

Show or limit only 5 records per page

<if:position() mod 5 =0?><xsl:attribute name="break-before">page</xsl:attribute><?end if?>

Show "NO DATA FOUND" if query does not fetch any data

<?choose:?><?when:count(INVOICE_NUM) > 0?><?end when?><?otherwise:?>NO DATA FOUND<?end otherwise?><?end choose?>

Detailed steps with screenshots:

Now, my RTF template is ready. Upload it and use.

Tuesday, December 13, 2022

ERP BI REPORT - XSLT vs BIP Tags | BI publishers

XSLT vs BIP Tags:

Simple BI Publisher RTF template can be developed with BIP tags. BIP Tags are easier to use even who does not have XML or XSLT experience.
Behind the scene, BIP tags are translated to XSL code at runtime.
To develop rich functionality such as Charts, Graps XSLT is used, wizards can be used to define simple charts and graps but to customize the only option is XSLT coding.
XSLT and BIP Tags can co-exist in the RTF template. Custom functions can be created in XSL template and can be invoked from RTF template.

Example of XSL :

<OrderNum>

<xsl:value-of select="/Data_DS/G_1/Order_Number"/>

</OrderNum>

<xsl:for-each select="DATA_DS/G_1">

<SalesOrder>

<OrderNum>

<xsl:value-of select="Order_Number"/>

</OrderNum>

</SalesOrder>

</xsl:for-each>

Example of BIP tags :

<OrderNum>

<?/Data_DS/G_1/Order_Number?>

</OrderNum>

<?for-each select="DATA_DS/G_1"?>

<SalesOrder>

<OrderNum>

<?Order_Number?>

</OrderNum>

</SalesOrder>

<?end for-each?>

ERP - Create BI reports with Parameters and Dependent LOVs

Usecase: Here, we will create a BI report with parameters and also create dependent LOVs. We will fetch invoice_num,source and description from ap_invoices_all table using p_source and p_inv_num parameters and here, LOV of invoice num will be depended on the parameter p_source.

logic steps:

Create 2 parameters - p_source and p_inv_num which will be pointing to the following 2 LOVs

p_source => source_lov
p_inv_num => inv_num_lov

Create 2 LOVs where inv_num_lov depended on p_source

source_lov = select distinct source from ap_invoives_all
inv_num_lov = select invoice_num from ap_invoices_all where source =:p_source

Create data set using sql query

Select invoice_num,source, description from ap_invoices_all where source=nvl(:p_source,source) and invoice_num=nvl(:p_inv_num,invoice_num)

Create report based on this data model.

Detailed steps with screenshots:

Navigation >> Tools >> Reports and Analytics >> Browse Catalog >> create >> Data Model

Parameters >> Create 2 parameters(initially with text type)

List of Values >> create 2 LOVs,

Parameters >> update paramter type with Menu and select the respective LOVs.

Data sets >> Click + sign >> SQL Query

Provide Name, data source and SQL query.

Click data

Select the source and dependend invoice >> view

Note: if you want to create custom RTF template, export the data.

Save as sample data >> save data model

Create report

Cancel >> select Data model >> generate RTF

Save the report name

Save Report >> View Report

Test

We can also export data.

Monday, December 12, 2022

Oracle Cloud ERP links

Followig blogs are related to ERP technical activities like BIP/OTBI, ESS, FRS, SmartView etc.

Create BI report using eText template

BI Excel template

Create BI report using Excel template

BI XSL template

OTBI reports:

OIC - OTBI report creation | Invoke the otbi report from Oracle integration | Invoke otbi report webservice from soap ui tool

ESS jobs:

Friday, December 9, 2022

ERP - BI Roles and Configurations

BI Roles & Configuration:

The user should have BI abstract role, attached with "Manage reports & analytics function security policy" priviledge and BI administration role.

To configure follow below steps:

Navigator >> Tools >> Security console

Create a Role like XXDEMO_BI_ROLE where role category as BI - Abstract Roles.

Add function security policy as Manage Reports & Anlytics.

Add role membership to BI admin.

Add user to the created role.

Verify whether the user has assigned all the roles.

Users >> search user

To synchronizes users, roles and role grants with definitions in LDAP.

Navigator >> Tools >> Scheduled Processes

Schedule New process

Search Retrive Latest LDAP Changes.

Submit

We can see the job is running and will succeed and some time.

Now we are ready to create or manage BI reports.

To create report;

Navigation to BI reports:

Navigator >> Tools >> Reports & Analytics >> Browse Catalog

Here we can see 2 folders:

My Folders: Reports for a particular user, is kept and not visible to any other user.

Shared folders: reports saved in this folder, will be visible to all users having accesa to Reports and Analytics.

Following roles need to show data model under Reports & Analytics:

https://soalicious.blogspot.com/2023/03/oracle-cloud-erp-what-are-roles.html

Subscribe to: Posts (Atom)