Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 15 Oracle Analytics Lounge
- 214 Oracle Analytics News
- 42 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Adding two new columns in output

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
-
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.
0 -
Thanks for the reply, Other than this, is there any other way to achieve the solution? Regards, Ravi
0 -
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.
0 -
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
0