Sql Expression is making report very slow. 1

[NBVC]

I have a report, created using database wizard connected to our manufacturing ERP system, that list parts remaining to be issued to open jobs. In the report I have a subreport that identifies if there are any open jobs to make any of the parts listed in the main report.

One of the custom fields I want to populate is going to be called "Next OP ID", which means I want to show the next incomplete operation after the last completed operation.

I am not sure if there is an easy way to do using formulas, etc., but the way I came up with is by using an SQL expression.

Here is my current expression for "Next OP ID":

(Select Top 1 JO. jmoJobOperationID from JO WHERE jmoJobID = J.jmpJobID and JO. jmoJobOperationID > 
(SELECT ISNULL
((SELECT top 1 JO.jmoJobOperationID FROM JO WHERE jmoJobID = J.jmpJobID and JO.jmoProductionComplete = -1 order by JO. jmoJobOperationID desc)
,0)))

In the inner SELECT, the code looks for the last completed operation (in descending order) - jmoProductionComplete = -1 means completed operation. If there is none then 0 is returned so that I can get the first operation.
Then, in the outer SELECT it looks for the next operation id that is greater than that one. The reason I do it this way is that it could be possible that an operation was skipped, and I don't want to include that operation in my sql.

It works, but the problem is the length of time it takes to generate a report. The more parts in the main section, the longer it takes.

I hope this is enough information. Let me know if more is required.

Can anyone suggest a quicker method?

 

[lbass]

Try a formula instead:

//{@IncompleteID}:
if {JO.jmoProductionComplete} = -1 then
{JO. jmoJobOperationID}

Then insert a minimum on this at the {J.jmpJobID} group level.

Or if you want to display only the row corresponding to this value, go to report->selection formula->group and enter:

{@IncompleteID}= minimum({@IncompleteID},{J.mnpJobID})

-LB

 

[NBVC]

Thanks for reply LB, I will give it a try soon.

One thing I wanted to clarify first is... as my final result I want to get the next JO.jmoJoberationID that is greater than the JO.jmoJobOperationID that I think your formula is getting.

Basically, after I find the last operation with production complete set to -1, I want to get the next operation (which would still be set to production complete =0, meaning it hasn't been completed yet).

 

[lbass]

I thought the -1 meant incomplete. Just change the -1 to zero and try that.

-LB

 

[NBVC]

Hi LB,

Thanks. I implemented your suggestion, but it doesn't really seem to be going any faster

 

[lbass]

Well, this particular formula isn't going to affect speed particularly. Did you remove the sql expression from the report? If you want to optimize speed, there are many things to review. Is your selection formula passing to the SQL, for example?

-LB

 

[NBVC]

Hi LB,

I did check to make sure that none of the sql expressions where in the report. I guess I'll have to stick with my current method. Thanks for the help, though. I appreciate it.

 

[lbass]

Make sure you have deleted them in the SQL expression editor. I think they might compile even if not used in the report.

-LB