Most frequently used Date, Number and String functions

  • Post published:March 8, 2019
  • Post Category:BI Publisher
  • Post Comments:3 Comments
  • Post last modified:May 13, 2020

In this article we will see some of the mostly used Date, Number and String functions that we bang our heads on to get them work in SQL Query. Want to learn Fusion Technical tools? Check out this article Get a sequence of numbers (1-12) in a table: We can generate sequence of numbers using the connect by expression.. We can…

Continue Reading Most frequently used Date, Number and String functions

Most Frequently Used SQLs for List of Values (LOVs)

  • Post published:March 4, 2019
  • Post Category:BI Publisher
  • Post Comments:0 Comments
  • Post last modified:May 12, 2020

Below are the list of SQLs that are most frequently used to get the list of values for business objects. Legislative Data Group:Select name from per_legislative_data_groups_vl Secured Persons List:SELECT DISTINCT PERSON_NUMBER FROM PER_PERSON_SECURED_LIST_V Secured Departments List:SELECT DISTINCT SUBSTR(DEPT.NAME,1,INSTR(DEPT.NAME,'-',1)-1) DEPCODE FROMPER_DEPARTMENT_SECURED_LIST_V SEC_DEPT,PER_DEPARTMENTS DEPTWHERE DEPT.ORGANIZATION_ID=SEC_DEPT.ORGANIZATION_ID Payroll Name:select distinct payroll_namefrom pay_all_payrolls_fwhere TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date Country Codes:select country_code, GEOGRAPHY_ELEMENT1 from HZ_GEOGRAPHIES where…

Continue Reading Most Frequently Used SQLs for List of Values (LOVs)

OTBI Direct Database Query feature removal and fix – Obsolete

In one of the earlier posts Direct Database Query, we saw how to use the Direct Database Query feature of OTBI analytics. This feature has been removed from 19A, so if you have upgraded or planning to upgrade to 19A, remember that the reports created using this feature will not be supported post upgrade. Oracle has suggested moving these analytics…

Continue Reading OTBI Direct Database Query feature removal and fix – Obsolete

SQL Query to get Fast Formula and related info

  • Post published:March 4, 2019
  • Post Category:BI Publisher
  • Post Comments:0 Comments
  • Post last modified:May 13, 2020

Below are the SQL's to extract Fast Formula related data from underlying tables:Fast Formula Functions and their underlying Packages: SELECT f.BASE_FUNCTION_NAME ,f.description ,f.DEFINITION || '(' || listagg(fp.name || ' ' || decode(fp.class, 'I', 'IN', 'O', 'OUT') || ' ' || decode(fp.data_type, 'N', 'NUMBER', 'T', 'VARCHAR2', 'D', 'DATE'), ',') WITHIN GROUP (ORDER BY fp.sequence_number) || ') RETURN ' || decode(f.data_type,'N','NUMBER','T','VARCHAR2','D','DATE') AS…

Continue Reading SQL Query to get Fast Formula and related info

Prevent Merging Columns in Excel Output using RTF template

  • Post published:March 4, 2019
  • Post Category:BI Publisher
  • Post Comments:1 Comment
  • Post last modified:May 12, 2020

While creating a BI Publisher Report, we develop templates using RTF template as it provides multiple output formats like Interactive Mode, PDF, XLSX, XML, CSV etc which are not possible with any other templates like XLS, E-TEXT, etc. One problem most of the people face is when they try to download the excel output using RTF template, the excel output contains merged…

Continue Reading Prevent Merging Columns in Excel Output using RTF template