Forum Stats

  • 3,852,402 Users
  • 2,264,102 Discussions
  • 7,905,060 Comments

Discussions

Download to Excel Problem

476564
476564 Member Posts: 23
edited Nov 10, 2008 11:25AM in APEX Discussions
Hi,

I am trying to export from data from a report to excel, but each time I try I just get a line in a spreadsheet saying OMITOMITOMITOMITOMIT, the query I am using is

select
initcap(a.Description),
a.HRG_CHAPTER,
a.admit,
a.HRG_CHAPTER_DESC,
nvl(CURRENT_ACT,0),
NVL(CURRENT_COST,0),
NVL(PREV_ACT,0),
NVL(PREV_COST,0),
NVL(PREV_ACT,0)-NVL(CURRENT_ACT,0) as GACT,
NVL(PREV_COST,0)-NVL(CURRENT_COST,0) as GCOST
from
(
select
DETAILED_ADMIT_METHOD AS Description,
HRG_CHAPTER,
substr(detailed_admit_method,1,3) as admit,
HRG_CHAPTER_DESC,
SUM(SPELL_COUNT) as CURRENT_ACT,
SUM(SPELL_COST) as CURRENT_COST
from ACTIVITY_REPORTING
Where ACTIVITY_MONTH between (select start_date from data_year) and (select end_date from data_year)
and 'PCT' || ADMIT_METHOD = :P3_ADMIT_METHOD
and (CLUSTER_GRP = :P2_PCT_CLUSTER OR NVL(:P2_PCT_CLUSTER,'ALL') = 'ALL')
and ADMIT_METHOD || ' ' || DETAILED_ADMIT_METHOD = :P3_DETAILED_METHOD
Group by DETAILED_ADMIT_METHOD,HRG_CHAPTER,substr(detailed_admit_method,1,3),HRG_CHAPTER_DESC
) a,
(
select DETAILED_ADMIT_METHOD AS Description,
HRG_CHAPTER,
substr(detailed_admit_method,1,3) as admit,
HRG_CHAPTER_DESC,
SUM(SPELL_COUNT) as PREV_ACT,
SUM(SPELL_COST) as PREV_COST
from ACTIVITY_REPORTING
Where add_months(ACTIVITY_MONTH,12) between (select start_date from data_year) and
(
select max (LAST_DAY(activity_month))
from activity_reporting
)
and 'PCT' || ADMIT_METHOD = :P3_ADMIT_METHOD
and ADMIT_METHOD || ' ' || DETAILED_ADMIT_METHOD = :P3_DETAILED_METHOD
and (CLUSTER_GRP = :P2_PCT_CLUSTER OR NVL(:P2_PCT_CLUSTER,'ALL') = 'ALL')
Group by DETAILED_ADMIT_METHOD,HRG_CHAPTER,substr(detailed_admit_method,1,3),HRG_CHAPTER_DESC) b
where a.Description = b.Description (+)
and a.HRG_Chapter = b.HRG_Chapter (+)
union
select
initcap(B.Description),
b.HRG_CHAPTER,
b.admit,
b.HRG_CHAPTER_DESC,
nvl(CURRENT_ACT,0),
NVL(CURRENT_COST,0),
NVL(PREV_ACT,0),
NVL(PREV_COST,0),
NVL(PREV_ACT,0)-NVL(CURRENT_ACT,0) as GACT,
NVL(PREV_COST,0)-NVL(CURRENT_COST,0) as GCOST
from
(
select
DETAILED_ADMIT_METHOD AS Description,
HRG_CHAPTER,
substr(detailed_admit_method,1,3) as admit,
HRG_CHAPTER_DESC,
SUM(SPELL_COUNT) as CURRENT_ACT,
SUM(SPELL_COST) as CURRENT_COST
from ACTIVITY_REPORTING
Where ACTIVITY_MONTH between (SELECT START_DATE FROM DATA_YEAR) and (SELECT END_DATE FROM DATA_YEAR)
and 'PCT' || ADMIT_METHOD = :P3_ADMIT_METHOD
and (CLUSTER_GRP = :P2_PCT_CLUSTER OR NVL(:P2_PCT_CLUSTER,'ALL') = 'ALL')
and ADMIT_METHOD || ' ' || DETAILED_ADMIT_METHOD = :P3_DETAILED_METHOD
Group by DETAILED_ADMIT_METHOD,HRG_CHAPTER,substr(detailed_admit_method,1,3),HRG_CHAPTER_DESC) a,
(select
DETAILED_ADMIT_METHOD AS Description,
HRG_CHAPTER,
substr(detailed_admit_method,1,3) as admit,
HRG_CHAPTER_DESC,
SUM(SPELL_COUNT) as PREV_ACT,
SUM(SPELL_COST) as PREV_COST
from ACTIVITY_REPORTING
Where add_months(ACTIVITY_MONTH,12) between (SELECT START_DATE FROM DATA_YEAR) and
(
select max (LAST_DAY(activity_month))
from activity_reporting
)
and 'PCT' || ADMIT_METHOD = :P3_ADMIT_METHOD
and (CLUSTER_GRP = :P2_PCT_CLUSTER OR NVL(:P2_PCT_CLUSTER,'ALL') = 'ALL')
and ADMIT_METHOD || ' ' || DETAILED_ADMIT_METHOD = :P3_DETAILED_METHOD
Group by DETAILED_ADMIT_METHOD,HRG_CHAPTER,substr(detailed_admit_method,1,3),HRG_CHAPTER_DESC
) b
where b.Description = a.Description (+)
and b.HRG_Chapter = a.HRG_CHAPTER (+)

I have custom names on the columns on the report attrbs tab such as 'Previous<BR>YTD Cost'

Any ideas?

Cheers
David
Tagged:

Answers

  • Simon Gadd
    Simon Gadd Member Posts: 598
    Hi David

    That's a mighty big query.

    Are you running this query in APEX? i.e have you go a report region using this query?

    Whilst I have not come across the symptoms you are describing, my first suggestion would be to put your query into a view. Then, in your APEX page, define your report region as
    select description, hrg_chapter...
    from viewname
    where ....
    the only other thing I can see is that you 'may' want to define the first select as...
    SELECT INITCAP(a.description) description
          ,a.hrg_chapter
          ,a.admit
          ,a.hrg_chapter_desc
          ,NVL(current_act, 0) current_act
          ,NVL(current_cost,0) current_cost
          ,NVL(prev_act,    0) prev_act
          ,NVL(prev_cost,   0) prev_cost
          ,NVL(prev_act,    0) - NVL(current_act ,0) AS gact
          ,NVL(prev_cost,   0) - NVL(current_cost,0) AS gcost
    FROM   ...
    Kind regards

    Simon Gadd
    Simon Gadd
This discussion has been closed.