Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Sql Expression is making report very slow.

Sql Expression is making report very slow.

Sql Expression is making report very slow.

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 top 1 JO.jmoJobOperationID FROM JO WHERE jmoJobID = J.jmpJobID and JO.jmoProductionComplete = -1 order by JO. jmoJobOperationID desc)

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?

RE: Sql Expression is making report very slow.

Try a formula instead:

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})


RE: Sql Expression is making report very slow.

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).

RE: Sql Expression is making report very slow.

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


RE: Sql Expression is making report very slow.

Hi LB,

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

RE: Sql Expression is making report very slow.

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?


RE: Sql Expression is making report very slow.

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.

RE: Sql Expression is making report very slow.

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


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close