Working Crystal Report Stops Working?

crystal-report-probl

(Joe Lalonde) #1

I swear I’ve had some gremlins or something in my system. This latest issue has me perplexed.

We have a Crystal Report that would group our Resource Groups by Start Date. This has been working well as long as I have been here. Yesterday, we go to run the report and it just continues to load.

It then caused Vantage to hang and Vantage would only run once we killed the report. Running the Crystal Report directly from Crystal Report also hangs Vantage.

Any ideas on what the problem could be? I’m thinking there might be corrupt data in the database? Any other thoughts?


(Vic Dŕėċċhíò) #2

Are you able to write a BAQ duplicating your query in CR? Perhaps see any offending data. What’s your CR query look like? In Developer, go to Database --> Show SQL Query.
image

It caused Vantage to hang? Meaning you had to restart the server / appservers?


(Calvin Krusen) #3

CAn you run it with parameters (start and end dates?) that worked in the past?


(Joe Lalonde) #4

Vic,

I struggle with writing reports. I doubt I would be able to duplicate it easily.

By Vantage hanging, I mean it was unresponsive until I closed out of Crystal. The same thing happens if we run the Crystal in Vantage. The program will go unresponsive and I have to close it.

I was able to get the CR Query. It is:

SELECT “joboper”.“jobcomplete”, “joboper”.“opcomplete”, “orderhed”.“openorder”, “orderrel”.“partnum”, “jobopdtl”.“resourcegrpid”, “joboper”.“estprodhours”, “joboper”.“startdate”, “jobhead”.“jobnum”, “orderdtl”.“linedesc”, “jobhead”.“partdescription”
FROM (((((“mfgsys803”.“dbo”.“jobhead” “jobhead” INNER JOIN “mfgsys803”.“dbo”.“jobprod” “jobprod” ON “jobhead”.“jobnum”=“jobprod”.“jobnum”) INNER JOIN “mfgsys803”.“dbo”.“joboper” “joboper” ON “jobprod”.“jobnum”=“joboper”.“jobnum”) INNER JOIN “mfgsys803”.“dbo”.“orderrel” “orderrel” ON ((“jobprod”.“ordernum”=“orderrel”.“ordernum”) AND (“jobprod”.“orderline”=“orderrel”.“orderline”)) AND (“jobprod”.“orderrelnum”=“orderrel”.“orderrelnum”)) INNER JOIN “mfgsys803”.“dbo”.“orderdtl” “orderdtl” ON (“orderrel”.“ordernum”=“orderdtl”.“ordernum”) AND (“orderrel”.“orderline”=“orderdtl”.“orderline”)) INNER JOIN “mfgsys803”.“dbo”.“orderhed” “orderhed” ON “orderdtl”.“ordernum”=“orderhed”.“ordernum”) INNER JOIN “mfgsys803”.“dbo”.“jobopdtl” “jobopdtl” ON ((“joboper”.“jobnum”=“jobopdtl”.“jobnum”) AND (“joboper”.“assemblyseq”=“jobopdtl”.“assemblyseq”)) AND (“joboper”.“oprseq”=“jobopdtl”.“oprseq”)
WHERE “jobopdtl”.“resourcegrpid”<>’’ AND “orderhed”.“openorder”=1 AND “joboper”.“opcomplete”=0 AND “joboper”.“jobcomplete”=0 AND (“joboper”.“startdate”>={ts ‘2018-12-28 00:00:00’} AND “joboper”.“startdate”<{ts ‘2019-04-19 00:00:00’})


(Joe Lalonde) #5

Calvin,

There are no parameters prompts for this report.


(Calvin Krusen) #6

what sets the dates in

“joboper”.“startdate”>={ts ‘2018-12-28 00:00:00’} AND “joboper”.“startdate”<{ts ‘2019-04-19 00:00:00’}

Based on the current date?


(Joe Lalonde) #7

Yes, based on the current date. Here’s the record selection formula:

{jobopdtl.resourcegrpid} <> “” and
{orderhed.openorder} = 1 and
{joboper.opcomplete} = 0 and
{joboper.jobcomplete} = 0 and
{joboper.startdate} <= today + 49 and
{joboper.startdate} in DateAdd(‘m’, -2, CurrentDate) to DateAdd(‘m’, 4, CurrentDate)


(Calvin Krusen) #8

Try temporarily changing that so that the dates end up being in a range when the report did work.

Just to see if its a selection issue.

Edit

Are today and CurrentDate the same thing? Does one reference the actual date, and the other the date the data was generated on?


(Vic Dŕėċċhíò) #9

Hey Joe,

So it definitely sounds like you have some record locking occurring via/from ODBC. Have you cycled your Appservers after this occurrence when Vantage locked up? I’ve seen ODBC locks stick in Progress until you cycle the Appservers.

Also, I’m assuming you have a TEST environment handy. If so, do a Save As on your Crystal Report to save a version for hitting your Test environment. Create a new System DSN in Windows ODBC to point to your TEST environment. Change your Crystal Report’s data source to point to the new ODBC DSN. Now you can blow up TEST instead of Production while troubleshooting. :+1:

Your query looks fine and it obviously has worked just fine for years.

Also, make sure your all of your users’ ODBC DSN properties have “Default Isolation Level” set to Read Uncommitted. With this selected, ODBC queries do not lock any records; and since you’re hitting so many tables in your query this setting is important to mitigating locks.

Read a bit more about Default Isolation Level here.

Good luck.


(Vic Dŕėċċhíò) #10

Joe, also in case you need this to set up ODBC for your Test environment, here are some helpful documents.

ODBC Setup.pdf (86.7 KB)

ODBC Epicor Vantage 803 Setup.pdf (593.5 KB)

Here is a list of the default port assignments for the Vantage environments:
LIVE 8350
TRAIN 8360
TEST 8370
PILOT 8380

This is what my Vantage ODBC setup looks like on one of my old boxes:

image


(Joe Lalonde) #11

So, I began playing with date ranges in the report and still had some trouble. I then looked at some of the lines of code and figured there’d be no harm in trying to have it ignore certain lines. I told it to ignore the last line

{joboper.startdate} in DateAdd(‘m’, -2, CurrentDate) to DateAdd(‘m’, 4, CurrentDate)

and it worked at the full date range.

Can anyone help me with what that line means? If it is important, is there another way to code it?


(Bruce Ordway) #12

Just some other questions…

  • Vantage shortcut properties - have you set compatabilty mode - Windows XP SP3?
  • Crystal Dev - page setup - “No Printer” selected.
  • Crystal Dev - which version / SP?
  • Crystal Runtime - which version / SP?
  • and does the problem follow you to all/multiple workstations?

(Vic Dŕėċċhíò) #13

Joe, that line of the query is the Where Clause that specifies a dynamic date range. All JobOper records with a Start Date from 2 Months Ago until 4 Months From Today will be included in the result set.


(Joe Lalonde) #14

Vic, we’ve restarted the servers multiple times since this has happened. Still the same results. The only thing that seems to allow the report to run is to remove that line of code. When that line of code is in the report, previewing the information in Crystal Report seems to hang with it showing “Accessing Database” at the bottom left corner.

image

I’ve tried to change the numbers and it still does it. It seems to run fine without it.

If I remove that line, will the other line of code " {joboper.startdate} <= today + 49 and" still select from today until 49 days out or does it need that bottom line of code?


(Vic Dŕėċċhíò) #15

Hi Joe, yes, those two lines are somewhat redundant.

If you have removed the red-lined selection criteria below but have kept the red-arrowed line, then you should be fine.


(Calvin Krusen) #16

Without the second line, there is no lower limit applied to start date.

Make the 2nd line
{joboper.startdate} >= today -60
That’s close to what that second line does as a lower limit.


(Joe Lalonde) #17

Thanks for all the help guys. I just removed the last line and my team is happy with the results.