Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Adding two new columns in output

Received Response
43
Views
4
Comments

Summary

Adding two new columns in output

Content

Hi All,

Here I have a challenge, I have a report which is .rdf, Customer ask me to generate output in EXCEL, For that I have created a new .rtf template and concurrent program by referring same executable as .rdf.

Now Customer want me to add two new columns which is not there in report, here the challenge is without modifying .rdf file how to achieve this?

Can some once please help on this.

Thanks,

Ravi

Answers

  • Mdtaylor69
    Mdtaylor69 Rank 2 - Community Beginner

    There is no other way.  The RDF controls the SQL for the report which controls the columns selected.  If you cannot edit the RDF, you need some other way to add the columns to the template.

    You can create a new data definition xml in a few minutes.  Just download any existing one.  Make your SQL with the new columns added by extracting the SQL from the queries section of the RDF.

    Paste your custom SQL in the CDATA section:

    Then create a temporary view using your SQL like below:

    create or replace view xxcep_project_activity_summary as

    select xps.project_num,

           xps.project_name,

           xps.task_number task,

           xps.project_num||' '||xps.task_number proj_task,

           xps.task_name,

           ROUND(NVL(xps.burdened_cost_ptd,0)) mtd_actual,

           ROUND(NVL(xps.baseline_burdened_cost_ptd,0)) mtd_budget,

           ROUND(NVL((xps.baseline_burdened_cost_ptd - xps.burdened_cost_ptd),0)) mtd_variance,

           DECODE(xps.baseline_burdened_cost_ptd,0,NULL,ROUND((xps.baseline_burdened_cost_ptd - xps.burdened_cost_ptd)/xps.baseline_burdened_cost_ptd*100,1)) mtd_variance_percent,

           ROUND(NVL(xps.burdened_cost_ytd,0)) ytd_actual,

           ROUND(NVL(xps.baseline_burdened_cost_ytd,0)) ytd_budget,

           ROUND(NVL((xps.baseline_burdened_cost_ytd - xps.burdened_cost_ytd),0)) ytd_variance,

           DECODE(xps.baseline_burdened_cost_ytd,0,NULL,ROUND((xps.baseline_burdened_cost_ytd - xps.burdened_cost_ytd)/xps.baseline_burdened_cost_ytd*100,1)) ytd_variance_percent,

           ROUND(NVL(xps.burdened_cost_itd,0)) itd_actual,

           ROUND(NVL(xps.baseline_burdened_cost_itd,0)) itd_budget,

           ROUND(NVL((xps.baseline_burdened_cost_itd - xps.burdened_cost_itd),0)) itd_variance,

           DECODE(xps.baseline_burdened_cost_itd,0,NULL,ROUND((xps.baseline_burdened_cost_itd - xps.burdened_cost_itd)/xps.baseline_burdened_cost_itd*100,1)) itd_variance_percent,

           ROUND(NVL(xps.cmt_burdened_cost_itd,0)) cmt_itd_actual,

           ROUND(NVL(xps.baseline_burdened_cost_itd,0)) cmt_itd_budget,

           ROUND(NVL((xps.baseline_burdened_cost_itd - xps.cmt_burdened_cost_itd),0)) cmt_itd_variance,

           DECODE(xps.baseline_burdened_cost_itd,0,NULL,ROUND((xps.baseline_burdened_cost_itd - xps.cmt_burdened_cost_itd)/xps.baseline_burdened_cost_itd*100,1)) cmt_itd_variance_percent,

           ROUND(NVL(xps.baseline_burdened_cost_tot,0)) total_cost_budget,

           ROUND(NVL(xps.original_burdened_cost_tot,0)) total_original_cost_budget,

           ROUND(NVL(xps.baseline_burdened_cost_tot,0)) - ROUND(NVL(xps.cmt_burdened_cost_ptd,0)) - ROUND(NVL(xps.burdened_cost_itd,0)) estimate_to_complete,

           DECODE(xps.task_number,'002','Opex','Capex') expense_type,

           xps.cost_budget_type_code,

           pbt.budget_type,

           '0'||SUBSTR(xps.project_name,4,3) dept,

           fvt.flex_value_meaning division_code,

           fvt.description division_desc,

           ftl.description dept_description

      from xxcep_pa_status_task_v xps,

           pa_budget_types pbt,

           fnd_flex_values_tl fvt,

           fnd_flex_values_tl ftl,

           fnd_flex_value_norm_hierarchy fnh

    where xps.cost_budget_type_code = pbt.budget_type_code

       and xps.cost_budget_type_code in ('AC','FC')

       and fvt.flex_value_meaning like 'DIV%'

       and fvt.flex_value_meaning = fnh.parent_flex_value

       and '0'||SUBSTR(xps.project_name,4,3) between fnh.child_flex_value_low and fnh.child_flex_value_high

       and '0'||SUBSTR(xps.project_name,4,3) = ftl.flex_value_meaning

       and fvt.flex_value_id IN (select flex_value_id from fnd_flex_values where flex_value_set_id = (select flex_value_set_id from fnd_flex_value_sets where flex_value_set_name like 'CEP_GL_COA_DEP%'))

       and ftl.flex_value_id IN (select flex_value_id from fnd_flex_values where flex_value_set_id = (select flex_value_set_id from fnd_flex_value_sets where flex_value_set_name like 'CEP_GL_COA_DEP%'))

    order by xps.project_num, xps.project_name, xps.task_number

    Generate the XML element code using the this SQL and paste into the element section of the XML file (paste into notepad and make it a two line SQL):

    select '  <element name="'||column_name||'" value="'||column_name||'"/>'

    from dba_tab_cols where owner = 'APPS' and table_name = 'XXCEP_PROJECT_ACTIVITY_SUMMARY' order by column_id;

    Upload this new data definition to XML Publisher Administrator responsibility by creating a new data definition.

    Create a new concurrent program that uses XDODTEXE as the Executable Name and Short Name is the same as the XML Data Definition name.

  • Raviteja Anne-87956
    Raviteja Anne-87956 Rank 2 - Community Beginner

    Thanks for the reply, Other than this, is there any other way to achieve the solution? Regards, Ravi

  • Mdtaylor69
    Mdtaylor69 Rank 2 - Community Beginner

    Extract the SQL from the RDF and make a new XML Publisher data definition out of the SQL plus your 2 extra columns. Then add the two columns to your rtf template.

  • YGUTTIKONDA
    YGUTTIKONDA Rank 6 - Analytics Lead

    Ravi,

    just to clarify, when you modify the RDF there is no need to touch the layout.. as long as you modify the SQL to include the 2 new columns and generate the new XML file ... using this modify your RTF to include the new columns..

    Hope this helps.

    -YG