Blog

In this article, we are going to discuss usage and creation of excel templates for BI – Adhoc reports in Oracle HCM Cloud. we need to have clear understanding of our report output structure (XML before generating excel templates. List of fields we need to fetch in the output and are there any calculated fields required etc..

1. Create data model in BI Publisher
2. Below is a sample SQL used to generate 10 sample employee records : 

3.              select

                               round(dbms_random.value(1,10)) emp_number,

                 round(dbms_random.value(1,10000),2) emp_salary,

                 ‘USD’ salary_currency, 12 annualization_factor,

                 dbms_random.string(‘L’, 20) emp_name

                 from dual

                 connect by level <=10

                 order by 1

4. Click on Data > View , to view data SQL is fetching
5. Click on Save as Sample Data, to let BI Publisher generate XML data structure
6. Create a new
   Excel workbook
7. Design the
   layout in Excel using BI Publisher plugin for Excel
8. Assign the BI
   Publisher defined names
9. Prepare the
   XDO_METADATA Sheet
10. Sample XDO_METADATE sheet looks like this:
11. 
    
12. Save the excel
    file as “Microsoft Excel 97-2003 worksheet” format
13. Upload the
    excel template to report