Home » Fusion Middleware & Colab Suite » Business Intelligence » Report not generating any pdf files
Report not generating any pdf files [message #433668] Thu, 03 December 2009 10:18 Go to next message
kkeerthi
Messages: 6
Registered: December 2009
Junior Member
Hi All,

I'm trying to view a simple report which generates pdf files.
For one of the parameter values - '11F EA Accept nonhonor ASnSOMnSON_20091201090150846' the report is not generating any pdf files.
Initially I assumed the reason is because of the keyword 'AS' in it and created another parameter value - '11F EA Accept nonhonor AnS-SOM-SON_20091202074331997', but it is also not generating any pdf files.
I get this message from Adobe Reader " There was an error opening this document. This file cannot be opened because it has no pages. "
The report is working normally when i hardcode the same parameter values into the logic.
Any suggestions on this?

Thanks.
Re: Report not generating any pdf files [message #433711 is a reply to message #433668] Thu, 03 December 2009 15:07 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If report works correctly when you hardcode that parameter, then it must be something in the way you are passing it to the report. Are there, for example, some functions in between (such as UPPER or LOWER or ...)?
Re: Report not generating any pdf files [message #433733 is a reply to message #433711] Thu, 03 December 2009 21:14 Go to previous messageGo to next message
kkeerthi
Messages: 6
Registered: December 2009
Junior Member
Thanks for your reply.
Parameter is of menu type with LOV attached to it and the query involved for LOV has UPPER function in it, but all the parameter values are getting displayed properly and is able to select the values when viewing the report. After the parameter value is being passed to the report, it is not generating any pdf files.

Is it something to do with the UPPER function for the LOV?
Re: Report not generating any pdf files [message #433747 is a reply to message #433733] Fri, 04 December 2009 00:40 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is a parameter value: "11F EA Accept nonhonor ASnSOMnSON_20091201090150846". Mixed case, obviously. If you have applied UPPER to it and passed such a value to the report, it is unable to match those values and returns nothing:

SQL> create table test (val varchar2(100));

Table created.

SQL> insert into test values ('11F EA Accept nonhonor ASnSOMnSON_20091201090150846');

1 row created.

SQL> select * from test where val = upper('11F EA Accept nonhonor ASnSOMnSON_20091201090150846');

no rows selected

SQL>

Therefore, either don't UPPER it, or modify the report so that it contains the same function in the WHERE clause:
SQL> select * from test where upper(val) = upper('11F EA Accept nonhonor ASnSOMnSON_20091201090150846');

VAL
----------------------------------------------------------------------------------------------------
11F EA Accept nonhonor ASnSOMnSON_20091201090150846

SQL>


Re: Report not generating any pdf files [message #433810 is a reply to message #433747] Fri, 04 December 2009 06:09 Go to previous messageGo to next message
kkeerthi
Messages: 6
Registered: December 2009
Junior Member
The following sql query written for LOV - BatchID is displaying all the parameter values correctly. The only issue is when the parameter value selected from the displayed LOV list is been passed to the report, then it is not generating any pdf files.

select ' Select Batch Id....' batchid from dual
union all
Select distinct batchid from ugradadm.ugrad_correspondence
where batchid is not null
and delmethod IN ('LTRLBL', 'LTR')
and item ='DECLTR'
and NVL(activity_status,'NP') = DECODE(:Show_ALL,'N','NP',NVL(activity_status,'NP'))
and UPPER(batchid) like DECODE(:Show_ALL,'T','%EA%ACCEPT%'||TO_CHAR(sysdate,'YYYYMMDD')||'%','%EA%ACCEPT%')
order by batchid
Re: Report not generating any pdf files [message #433824 is a reply to message #433810] Fri, 04 December 2009 07:42 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Query seems to be OK.

Quote:
parameter value selected from the displayed LOV list is been passed to the report
How do you do that?
Re: Report not generating any pdf files [message #433834 is a reply to message #433824] Fri, 04 December 2009 08:03 Go to previous message
kkeerthi
Messages: 6
Registered: December 2009
Junior Member
Following is the main data template of my report which has 2 sql queries and takes the value of the parameter :BatchID

<dataTemplate name="DT" dataSourceRef="DW">
<parameters>
<parameter name="Batch_ID" dataType="string"/>
<parameter name="APPLID" dataType="string"/>
<parameter name="Num_Rec" dataType="number"/>
<parameter name="Run_With_Update" dataType="string"/>
<parameter name="ActivityDate" dataType="date"/>
<parameter name="Show_ALL" dataType="string"/>
</parameters>
<dataQuery>
<sqlStatement name="Q1">
<![CDATA[SELECT DISTINCT
a.appl_id APPL_ID,
a.APPLICANT_ALIAS USER_NAME,
NVL2(:ActivityDate,TO_DATE(:ActivityDate,'MM-dd-yyyy'),
TO_DATE(f.ACTDATE,'YYYY/MM/DD')) ACTDATE,
a.EAGLE_NUMBER EAGLE_ID,
UGRADADM.get_fin_aid_key(a.EAGLE_NUMBER) FINANCIAL_AID_KEY,
DECODE(a.sex,'M','Mr. ','F','Ms. ') PREFIX,
a.first_name FIRST_NAME,
DECODE(LENGTH(a.middle_name),1,' '||a.middle_name||'.',NVL2(a.middle_name,' '||a.middle_name,'')) MIDDLE_NAME,
a.last_name LAST_NAME, a.suffix SUFFIX,
a.citizenship CITIZENSHIP,
a.citizenship_class CITIZENSHIP_CLASS,
UPPER(NVL(DECODE(a.permanent_resident,'Y','YES',a.permanent_resident),'NO'))
PERMANENT_RESIDENT,
a.alumni_child ALUMNI_CHILD,
NVL(a.early_notify,'N') EARLY_NOTIFY,
TRIM(ml.FIELD_DATA) ENTER_COLLEGE_NAME,
a.ENTER_COLLEGE ENTER_COLLEGE,
a.housing_application HOUSING_APPLICATION,
NVL(a.FINAL_EARLY_ACTION_RATING,0) RATING,
NVL(a.resident_or_commuter,'N') RESIDENT_OR_COMMUTER,
NVL(DECODE(a.YEARS_OF_HOUSING,'00','one semester',
'01','one year',
'02','two years',
'03','three years',
'04','four years'),'0') YEARS_OF_HOUSING,
e.address1 ADDRESS1,
e.address2 ADDRESS2,
e.address3 ADDRESS3,
e.city CITY, NVL2(e.state_abbrev,' '||e.state_abbrev,'') STATE_ABBREV,
e.zipcode_5digit ZIPCODE_5DIGIT,
DECODE(e.country_code,'US','US',UPPER(fl.code_meaning)) COUNTRY,
DECODE(SUBSTR(a.entrance_yr_trm,5,6),'S','SPRING','F','FALL','U','SUMMER')
SEMESTER,
SUBSTR (a.entrance_yr_trm, 1, 4) TERM,
d.first_name R_FIRST_NAME,
NVL2(d.middle_intial,' '||d.middle_intial,'') AS R_MIDDLE_INITIAL,
d.last_name R_LAST_NAME, d.title TITLE,
ugradadm.update_dw(f.UGRAD_CORRESPONDENCE_KEY,:Run_With_Update) UPDATE_DATA,
f.UGRAD_CORRESPONDENCE_KEY
FROM ugradadm.applicant a,
ugradadm.ugrad_correspondence f,
ugradadm.reader_info d,
ugradadm.address e,
ugradadm.high_school h,
dwowner.m_lookup ml,
ugradadm.fw_lookup fl
WHERE a.appl_id = f.appl_id
AND a.appl_id=NVL(TRIM(:APPLID),a.appl_id)
AND a.hs_code = h.code
AND h.readnum = d.READER_ID
--AND d.reader_id = '15'
AND a.APPL_ID = e.APPL_ID
AND fl.TYPE_CODE='C'
AND e.COUNTRY_CODE=fl.code
AND UPPER(a.INST_ADMISSION_DECISION)='AC'
AND f.item = 'DECLTR'
AND a.enter_status = '1'
AND NVL(a.EARLY_NOTIFY,'N')='Y'
AND e.table_name = 'APPLICANT'
AND ml.TABLE_NAME='SCHOOLS'
AND ml.FIELD_NAME='DESCXL'
AND ml.FIELD_VALUE=a.ENTER_COLLEGE
AND e.address_type =
NVL ((SELECT gg.address_type
FROM ugradadm.address gg
WHERE gg.address_type =
DECODE (f.mailpref,
'Home', 'HOME',
'Best', 'LOCAL',
'HOME'
)
AND gg.table_name = 'APPLICANT'
AND gg.APPL_ID = a.appl_id
AND((gg.address_type='LOCAL' AND NVL(gg.address_expir_date,sysdate)>sysdate)
OR gg.address_type='HOME')),
'HOME'
)
AND f.delmethod IN ('LTRLBL', 'LTR')
AND a.eagle_number IS NOT NULL
AND NVL(f.activity_status,'NP')=DECODE(:Show_ALL,'Y',NVL(f.activity_status,'NP'),'NP')
--AND rownum<=:Num_Rec
AND f.batchid = :Batch_ID
ORDER BY a.last_name, a.first_name]]>
</sqlStatement>
<sqlStatement name="Q2">
<![CDATA[SELECT batchid BATCHID, COUNT(appl_id) CNT
FROM ugradadm.ugrad_correspondence
WHERE batchid=:Batch_ID
GROUP BY batchid]]>
</sqlStatement>
</dataQuery>
<dataStructure>
<group name="MASTER1" source="Q2">
<element name="BATCHID" value="BATCHID"/>
<element name="COUNT" value="CNT"/>
<element name="ACOUNT" value="MASTER.APPL_ID" function="COUNT()"/>
<group name="MASTER" source="Q1">
<element name="APPL_ID" value="APPL_ID"/>
<element name="USER_NAME" value="USER_NAME"/>
<element name="EAGLE_ID" value="EAGLE_ID"/>
<element name="FINANCIAL_AID_KEY" value="FINANCIAL_AID_KEY"/>
<element name="ALUMNI_CHILD" value="ALUMNI_CHILD"/>
<element name="PREFIX" value="PREFIX"/>
<element name="FIRST_NAME" value="FIRST_NAME"/>
<element name="MIDDLE_NAME" value="MIDDLE_NAME"/>
<element name="LAST_NAME" value="LAST_NAME"/>
<element name="SUFFIX" value="SUFFIX"/>
<element name="ACTDATE" value="ACTDATE"/>
<element name="CITIZENSHIP" value="CITIZENSHIP"/>
<element name="CITIZENSHIP_CLASS" value="CITIZENSHIP_CLASS"/>
<element name="PERMANENT_RESIDENT" value="PERMANENT_RESIDENT"/>
<element name="EARLY_NOTIFY" value="EARLY_NOTIFY"/>
<element name="ENTER_COLLEGE_NAME" value="ENTER_COLLEGE_NAME"/>
<element name="ENTER_COLLEGE" value="ENTER_COLLEGE"/>
<element name="HOUSING_APPLICATION" value="HOUSING_APPLICATION"/>
<element name="RATING" value="RATING"/>
<element name="RESIDENT_OR_COMMUTER" value="RESIDENT_OR_COMMUTER"/>
<element name="YEARS_OF_HOUSING" value="YEARS_OF_HOUSING"/>
<element name="ADDRESS1" value="ADDRESS1"/>
<element name="ADDRESS2" value="ADDRESS2"/>
<element name="ADDRESS3" value="ADDRESS3"/>
<element name="CITY" value="CITY"/>
<element name="STATE_ABBREV" value="STATE_ABBREV"/>
<element name="ZIPCODE_5DIGIT" value="ZIPCODE_5DIGIT"/>
<element name="COUNTRY" value="COUNTRY"/>
<element name="SEMESTER" value="SEMESTER"/>
<element name="TERM" value="TERM"/>
<element name="R_FIRST_NAME" value="R_FIRST_NAME"/>
<element name="R_MIDDLE_INITIAL" value="R_MIDDLE_INITIAL"/>
<element name="R_LAST_NAME" value="R_LAST_NAME"/>
<element name="TITLE" value="TITLE"/>
<element name="BATCHID" value="BATCHID"/>
</group>
</group>
</dataStructure>
</dataTemplate>
Previous Topic: business intelligence
Next Topic: how users/client for OBIEE
Goto Forum:
  


Current Time: Thu Mar 28 15:42:37 CDT 2024