Issue Running Report in OTM - too much data

[cspadola]

Good day...we are experiencing problems with a custom report we created in Oracle Reports to be used in OTM. The purpose of the report is to gather a list of vouchers created within specific dates (usually 2-3 weeks worth) and also to be used to run daily voucher reports - a report showing vouchers created for one specific day.

The report itself works fine and because of the volume of data we deal with, we created a materialized view for this report which improved performance (takes about 10 seconds to retrieve the results). When running the report SQL direct against the DB (using PL/SQL Developer), we get all the results we expect and can export it to a csv file. We then try to do the same using the report in OTM and although we get html results, when we try to export to excel it fails.

Upon investigating this and contacting Metalink, Oracle determined that there was too much data being passed to the report and OTM or Oracle Reports could not handle it. So we cut the date parameter to just one day but even that did not work. A typical report run for 2 weeks of vouchers can return over 35,000 rows of results. Even one day of vouchers can produce 2,500 rows of data of which there is 61 columns in the report.

The issue occurs when we attempt to export to Excel which causes a new window to open but we never get prompted to save it or open it - it eventually times out. Doing this outside of OTM works fine. Oracle says they cannot provide a fix for this as it is our custom report causing this issue.

Can anyone offer any assistance here?

Thanks

[nick.polites]

Re: Issue Running Report in OTM - too much data

Have you tried scheduling the report to run so it emails you when it is finished? You can also increase the timeout of the apache server running reports to see if that helps, but I wouldn’t recommend it. Any report that takes over 5 minutes to execute is too long and should be optimized as no doubt there is a bad query within the report. Imagine running a few of these at the same time, you will most likely not only bring down the reports instance but OTM as well.

[cspadola]

Re: Issue Running Report in OTM - too much data

Hi Nick...the problem with scheduling the report is thatit will send a PDF file and not a CSV file which our settlement team needs.

From what Oracle told us, it wasn't necessarily a time-out issue but rather the amount of rows that OTM cannot handle. We optimized this report so that it uses a materialized view and when you run the report in PL/SQL, we get all the results in 10 seconds or less. The same if we run the report in OTM against the mview - we get results in html in about 10 seconds but when we try to export to Excel, that is where the issue starts.

[sprabhakar]

Re: Issue Running Report in OTM - too much data

What is the version of OTM that you are using ?

[cspadola]

Re: Issue Running Report in OTM - too much data

We are on OTM v5.5 RU02 CU03

[sprabhakar]

Re: Issue Running Report in OTM - too much data

The way I would go ahead and resolve this would be as given below but this again depends on your environment setup.

1. Create a report that generates the output in XLS format with a specific output report file name.
2. Schedule the report.
3. The scheduled report get stored on the report server in the rptapache/htdocs directory.
4. Have a custom script to pick up the file and send it to the required email address.

We have implemented this for a few customers with success.

Regards,
Prabhakar

[cspadola]

Re: Issue Running Report in OTM - too much data

Hi Prabhakar...I thought that if we put it in the Scheduled Reports section, we can only use PDF export that gets sent by email. We must use an Excel export (xls or csv) so using a scheduled report has not been an option. The group that uses this report needs it in Excel format so that the data can be uploaded easily to a tool they use for invoicing.

The report we created for this does have an export to Excel function but only when it is run manually in OTM via the Reporting Manager page. This is where it fails because of the amount of data involved.

Thanks for your response.

CS

[sprabhakar]

Re: Issue Running Report in OTM - too much data

Hi CS,
Just to make one thing clear - OTM does not restrict you generating a report in CSV/XLS format. When you submit a scheduled report request, OTM hardcodes the DESFORMAT variable to PDF and hence the report output from OTM is always PDF. This can be modified without any issues. you have to follow the below steps to generate a scheduled report in XSL/CSV format

1. In the after parameter form trigger, modify the below line

:P_FILE := TRANSLATE(TRANSLATE(:P_DOMAIN||'_'||:P_REPORT_GID, '.','_'),' ','_')||:P_RPT_JOB||ws_date||'.'||ESFORMAT;

as

:P_FILE := 'XXX.XLS'; ( or any name as you wish)

2. Design the report to produce an XSL/CSV output and schedule the report.

3. Once the scheduled report completes, look for the report XXX.XLS in the rptapache/htdocs folder,

4. Pickup the file and send it out using the custom script.

Please get back to me if you have further questions.

Regards,
Prabhakar

[cspadola]

Re: Issue Running Report in OTM - too much data

Hi Prabhakar, thanks for these instructions.

I must be doing something wrong somewhere. I have changed the :P_FILE section as you suggested and changed this to 'report.xls'; The report was already designed to be exported to Excel as a CSV. I compile with no errors and save it on our server. If I run the report manually, it works fine. But when I run it through the scheduler, it still gets saved as a PDF. When I check the htdocs folder, I see the report I just ran in there but it is PDF. I went in and commented out all the AFTERPFORM pertaining to PDF and XML hyperlink and left the Excel hyperlink as is. Still no luck.

When you say "Design the report to produce an XLS/CSV output", am I not doing that already? Or is there a setting I must make so that it skips the HTML results and goes straight to export to Excel?

Thanks