We are facing an issue with one of the reports. The report h...

[VisuaLizeFOCUS .]

We are facing an issue with one of the reports.

The report has DBMS time approx. 1-2 hours for 1 year of data.

Seems like the request from WebFOCUS server gets complete and the Server AGENT becomes IDLE but still, the Report Caster job remains in a Running state and never gets complete.

We have already raised a case with IBI but no such help any suggestions

WF Version: 8207.

Thanks.

[John Gelona]

Need more info. What is the output format Is this summary or detail Does the job run normally if you run it from App Studio Was the schedule migrated from a release prior to 82

[VisuaLizeFOCUS .]

Output format : xlsx

We ran job from app studio it did run sometimes and takes more than 2 hrs.

Its a complicated report with more than 23 joins.

We are using sql stored procedure for this report.

Schedule was not migrated users can create a new schedule for this report.

report has 7 By fields and 4 measures.

[John Gelona]

How many rows are returned by the stored procedure. Is it going into one sheet in the workbook or multiple sheets Is it going into a macro workbook (.xltm or .xlsm) How fast does it run if you just put the data into a HOLD file instead of XLSX Have you tried to see if it works better/faster as an EXL2K file Obviously I have no knowledge of your database or application but our child welfare application has some processes that collect 25 years of data and nothing takes 2 hours to collect. Are you sure your stored procedure is efficient

[john cullen]

Hello Rajesh,

You have a couple of choices, but basically you are going to have to find a way to step thru the procedure to make sure it actually runs. I would suggest running it deferred as a first step. If it doesnt run from the browser, it cant run from Caster. dont use appstudio, take that out of the equasion. You could also run it with reportcaster traces on. But, first be sure it actually runs. How are you sure it doesnt finish in reportcaster. What does the reportcaster log say. You could put -SET &ECHO=ALL; for debugging, or add some -TYPE statement like -TYPE got to here so the botlog (reportcaster logs) have some breadcrumbs you can follow to find where it is quitting. Debugging this takes some methodical thinking. Like run the procedure with -EXIT at some point, and youll know it runs to the -EXIT. You just have to step thru and find where it is quitting. When it takes 2 hours to run, you only get about 5 trys a day,

[Toby Mills]

Hey Raj

Just wanted to add an agreement with the basic first step of changing the report to just do a HOLD (not PCHOLD). Just see if the report finishes that way before you actually try to funnel all the records back to caster for display.

Theres an internal deal called the NG connector that sends all the rows collected by the WFRS back to the webapp. It sounds like youre getting hung up there. Traces are available for this, but as John (jnc) said, you might get a ton of information which may just muddy the water.

So start with the ON TABLE HOLD and see how many records youre trying to send over the pipe back to the client (and whether that number looks reasonable to you).

Good luck!