Data template in Oracle BI Publisher 10g

Objective:

To generate reports from OBIP using data template SQL queries.

Scenario:

Customer has given a requirement to develop the report in OBIP. While developing it, developer came to know two dataset of SQL queries required to complete the report. Developer has to develop the report with single dataset of SQL query, Instead of using two dataset of SQL queries.

Solution:

To develop OBIP report using Data template SQL Queries.

Step 1: Create OBIP report

Step 2:  Create data template SQL Queries and place it into data model in OBIP.

Step 3: Data Template SQL Query.

Step 4: XML data generation.

Step 5: Excel Template.

Note: By this method, we can call package or procedure inside the dataset.  It will reduce no. of dataset use in it.

By
Karkuvelraja T

Scroll to the top of the page/report in Oracle APEX

Objective:

To Scroll the top of the page/report in Oracle APEX application.

Scenario:

We have encountered an issue that you always stay on the bottom of the page even after the refresh OF the report. One of my customers marked this as a bug and meant that after the refresh the display position should be at the beginning OF the report.

Solution:

Add a dynamic action: After refresh of your report execute this JavaScript statement.

Step 1: Create dynamic action

By

Karkuvelraja T

APEX Reports with dynamic help text in Oracle APEX 4.2 using CSS

Objective:

To show dynamic help text in APEX reports (Interactive/classical) in Oracle APEX 4.2 using CSS.

Solution:

Step 1: Create Interactive report

Step 2: Use below query in IR Region source.

<<SQL Begins>>

SELECT

  *

FROM

  (

    SELECT

      ROWNUM LEVEL_VALUE ,

      UPPER(TARGET) TARGET,

      STARTED_BY

    FROM

      (

        SELECT

          ('<span style="cursor:help" title="'

          || UPPER(REPORTNAME)

          || '">'

          || (

            CASE

              WHEN AA.SUB_PAGEID !=:APP_PAGE_ID

              THEN '<a href="'

                ||'f?p=&APP_ID.:'

                ||AA.SUB_PAGEID

                ||':'

                || :APP_SESSION

                ||':::'

                ||AA.SUB_PAGEID

                ||':">'

                ||INITCAP(AA.SUBTABNAME)

                ||' </a>'

              ELSE '<font color=purple>'

                ||INITCAP(AA.SUBTABNAME)

                ||'</font>'

            END)

          || '</span>') AS TARGET,

          TRIM(UPPER(AA.SUBTABNAME)) REP_ORD ,

          AB.MAKER_ID AS "STARTED_BY"

        FROM

          REP_DEPT_REPORT_MAP_DETAILS AA,

          DS_REP_REPORT_MASTER AB

        WHERE

          AA.REPID IN

          (

            SELECT DISTINCT

              RSD.REPORTID

            FROM

              REP_ROLE_DEPT_REPORT_MAP RSD,

              REP_ROLE_DEPT_MAP RD,

              REP_USER_MASTER UM,

              REP_USER_ROLE_MAP UD,

              REP_ROLE_MASTER RM,

              REP_DEPARTMENT_SUB_MASTER DSM,

              REP_DEPT_REPORT_MAP_DETAILS REPTD,

              REP_ROLE_DEPT_SUB_MAP RRDSM,

              DS_REP_REPORT_MASTER DRP

            WHERE

              RSD.DEPTID               =RD.DEPTID

            AND TRIM(UPPER(UM.USER_ID))=TRIM(UPPER(:P0_USERID))

            AND UM.USER_ID             =UD.USER_ID

            AND RD.ROLEID              =UD.ROLEID

            AND RSD.ROLE_ID            =UD.ROLEID

            AND RM.ROLE_ID             =UD.ROLEID

            AND DSM.DEPT_SUBID         =REPTD.STABID

            AND RRDSM.DEPT_SUBID=DSM.DEPT_SUBID

            AND RM.ROLE_ID      =RRDSM.ROLEID

            AND RSD.REPORTID=REPTD.REPID

            AND DRP.REPID   =REPTD.REPID

            AND DRP.RSTATUS LIKE 'C'

            AND TRUNC(SYSDATE) = TRUNC(DRP.ENDTIME)

          )

        AND AA.REPID=AB.REPID

        ORDER BY

          AB.ENDTIME DESC

      )

  );

<<SQL Begins>>

By

Karkuvelraja T

Generate DDL source code using Oracle SQL

Objective:

To generate DDL source code using Oracle SQL.

Scenario:

Customer asks us to generate all the DDL source code for some database objects. So I just found the little piece of code to create DDL source.

Database Objects are, 

Ø  Sequence

Ø  Table

Ø  Index

Ø  View

Ø  Database Link

Ø  Materialized view

Ø  Function

Ø  Procedure

Ø  Package

Ø  Package Body

Solution:

Oracle has provided default package DBMS_METADATA. By using this we can get DDL source code.

By

Karkuvelraja T

Split by count excel report in Oracle BI Publisher 10g

Objective:

To create a template to split records in excel sheet in Oracle BI Publisher 10g.

Solution:

Template Creation - EXCEL

Step 1: Open the Excel. (See below image)

Step 2: Go to Formulas => Name Manager – See the XDO call

Step 3: Go to Sheet2 (XDO_METADATA) – See the XSLT code

Step 4: Come to Sheet1 – See how the groupings were done

Step 5: Create one XML file – with the below code, see how the sheet were split by count of records using XML

<?xml version="1.0" encoding="utf-8" ?>

<xsl:stylesheet version="2.0"

xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:template match="/">

<ROWSET>

<xsl:variable name="var_size" select="50000" />

<xsl:for-each select="/ROWSET/ROW">

<xsl:variable name="var_pos" select="position()" />

<xsl:variable name="var_mod" select="$var_pos mod($var_size)" />

<xsl:if test="$var_mod = 1">

<xsl:variable name="var_groupNum" select="($var_pos - $var_mod) div

number($var_size) + 1" />

<xsl:element name="CountGroup">

<xsl:attribute name="name">

<xsl:value-of select="concat('Group', $var_groupNum)" />

</xsl:attribute>

<xsl:for-each select="/ROWSET/ROW[position() &gt; ($var_pos -1) and position() &lt; ($var_pos + $var_size)]">

<xsl:copy-of select="." />

</xsl:for-each>

</xsl:element>

</xsl:if>

</xsl:for-each>

</ROWSET>

</xsl:template>

</xsl:stylesheet>

Note:

Ø  Workout example screenshot shared below.  

Ø  Template has been attached separately with this component.

Ø  Output will be multiple sheets, which split by count of records.

Output: Output will be multiple sheets of excel file, which are split by count of records (50000).

By

Karkuvelraja T

Stop the user to click a page submit button more than once in Oracle APEX 4.2

Objective:

To stop the user to click a APEX page submit button more than once in Oracle APEX 4.2

Scenario:

The standard way to push the page data back to the APEX server or table is to use an HTML button. One issue with HTML buttons is that they can be clicked more than once, and under certain circumstances this can cause issues.

Solution:

To ensure that the button can only be pressed once you can do the following

Step 1: Crete APEX Page

Step 2: Crete Page Region

Step 3: Crete Page Button

Alter button settings so that inside the URL redirect section the URL target value is set to

javascript:this.disabled=true;doSubmit(‘SUBMIT’)

Note: The value inside doSubmit(‘’) is the same as the button name.

The button now looks like this when it has been clicked

Output:

Conclusion: The button is now coded so that the user can only click it once to submit the page. Any further clicks are ignored.

By

Karkuvelraja T

Call Oracle package in Oracle BI Publisher 10g

Objective:

To call Oracle package in Oracle BI Publisher 10g.

Scenario:

Oracle BI Publisher is just a reporting tool. From which we can download excel, csv, xml, rtf, PDF reports. In which we can use only SQL statement not PL/SQL. But, we came across the scenario to call Oracle package in OBIP.  

Solution:

We can call the Oracle package in OBIP by Data template SQL.

Step 1: Crete OBIP report

Step 2: Create data template dataset using below SQL

<dataTemplate name="AIRReport" description="Industrial_MIS_Automation"

                  dataSourceRef="BNP_RPTLIVDB_APEXRPS" Version="1.0"

                          defaultPackage="pkg_sbfs_report_log">

                                                                <dataQuery>

                                                                                <sqlStatement name="STMT1">

                                                                                                <![CDATA[SELECT INDUS FROM DS_SBFS_MNTHLY_DATA_ANALYSIS]]>

                                                                                </sqlStatement>

                                                                </dataQuery>

        <dataTrigger name="beforeReportTrigger"

                source="pkg_sbfs_report_log.Report_log('138','START')"/>

                                                                <dataStructure>

                                                                                <group dataType="varchar2" name="G_STMT_1" source="STMT1">

                                                                                                <element dataType="varchar2" name="INDUS" value="INDUS"/>

                                                                                </group>

                                                                </dataStructure>

        <dataTrigger name="afterReportTrigger"

                source="pkg_sbfs_report_log.Report_log('138','END')"/>

                                                </dataTemplate>

By

Karkuvelraja T