Skip to Content

Capturing next in sequence

I have a table that I am trying to pull data from. The table has the following fields that are relevant to the question.


status of operation

sequence of operation

Here is what I want to do. Frist of all, ops have to be in sequential order for this to work properly. Then I want to look at each operation's status. If the status is complete then I want it to go to the next operation. Once it finds a open operation, I want to report that operation. Now I want to take all of the jobs with say operation XY and total them at the foot of the report. Can this be done and if so, how?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

7 Answers

  • Jan 30, 2017 at 03:40 PM

    Hi Steve,

    1. Expand Field Explorer > Right-click 'Running Total Fields' > New.

    2. Choose 'operation' as the 'Field to Summarize'.

    3. Choose 'Count' as the Summary function.

    4. Under 'Evaluate', click 'Use a formula' > and use this code:

    {Status of Operation} = 'XY'

    5. Drag and drop the Running Total field on the Footer.


    Add comment
    10|10000 characters needed characters exceeded

    • Abhilash, if I have a list of sequences, what formula can I write to pick the top choice in the list?

      for instance:

      column a column b column c

      2 400 PB

      5 410 DM

      6 450 RF

      So I want it to look at these columns and then tell me that 400 is next and that code is PB. I want to display the "PB" column c.

  • Jan 30, 2017 at 05:25 PM

    Didn't work. I now have a massive list of jobs that should be only 4 pages. Finally the answer is 0 which is not correct since I have several at that location.

    ok, I missed giving you a detail that is pretty important. I actually want to report the dollar figures of orders in location XY which is another field within that table. So let's say I have 10 orders. I have the same sequences on all of them and all are valued at $500

    4 are at sequence XY (actually the sequence is numerical and I will need to change to 2 digit string using another table)

    2 are at sequence YZ

    and 4 are at sequence AB

    so what I would like to see on the totals is

    location XY has $2000

    location YZ has $1000

    and location AB has $2000

    does this better explain it?

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 31, 2017 at 11:16 AM

    OK, try this please:

    1. Insert a 'Crosstab' and place this on the Report Footer (Go to Insert > Crosstab Object).

    2. Use the 'Location' field as the 'Row' in the Crosstab.

    3. Use the Dollar Figures field as the 'Summarized Fields' and set its Summary function to 'Sum'.


    Add comment
    10|10000 characters needed characters exceeded

    • Sorry for the delay, I've been handling fires last week. How do I come up with a location field. I currently have two ways of looking at the location. One is a sub report that just lists everything that is open, which doesn't mean that everything that is open has actually not been worked on. it could be that someone just didn't scan it. So I can give you that formula on how I come up with that if you think I can pull that data to see the location.

      The other way of viewing the location is through pulling all ops into the report similar to the above but more data(again as a sub report - otherwise I have entries on the report for each operation and that means thousands of pages). This will have more detail and show me what has and has not been scanned along with sequence of operation. Now I need the totals on the main report, and as far as I know (still a novice with this) I cannot pull data from a sub report to get a summary field on the main report. So I guess first I need to know how to take all of the operations and come up with a "location" first. ideas?????

  • Feb 06, 2017 at 10:47 AM

    I think you need to attach some sample data including records you do not want to see.

    You can conditionally suppress or filter out the records you do not want to see using either the select expert or conditional suppression in Section expert. This should reduce page count.

    You can pass data from Subreports to main reports using Shared Variables.


    Add comment
    10|10000 characters needed characters exceeded

  • Feb 07, 2017 at 04:46 PM

    Using shared vars is a big topic.

    Search on web, here is an example of how to use

    You will need to use a Shared booleanvar which is set to true when your various conditions are met


    Add comment
    10|10000 characters needed characters exceeded

  • Apr 28, 2017 at 12:50 PM

    In the sample sets attached, these are sorted by sequence. The status column has the following options, "O" for open, "S" for started and "C" for complete. What I am looking for if the current operation, typically this is the first open or started operation. So I'm guessing that we should focus on the first operation that is not a "C" status.

    In looking at the first job, the correct operation is "SK" (based on what I know, someone skipped the Quality scan) and parts are in silkscreen. I would accept the location of "FQ" though.

    In the second data set, you see a lot more open operations. I know it is at "SK" again, but it would be ok if it said Sanding "SN". I want to use the last column for the location.
    Once this value has been determined, I want to then run a report (maybe by the value) and then give the dollar value of parts in the SK area.

    mpk1m.png (34.1 kB)
    Add comment
    10|10000 characters needed characters exceeded

    • I want to look at the data for any given order and tell me what operation it is at. So scans should be done in order of sequence. So looking at the sample data the first operation is actually at SK but based on the first non-completed line the finishing qc or FQ would be what should show as next operation. The simpler logic would be basing it on the last complete and then listing the next operation as the current location. If I know what operation the order is at, I can then get the dollars based on that department. So all I am looking for is a formula, sub-report, or something that can look at the data given and tell me where the order is at.

  • Apr 28, 2017 at 09:06 PM

    Hi Steve,

    I came into this a bit late but I would recommend taking a lot of the business logic out of Crystal and do it in a view or Stored procedure. It will make the development of the report less complicated and a lot of the logic to determine an open operation easier to deal with.

    Your database can sort the operations and determine if there is an open status and flag that operation for the report to total.

    Crystal is great for a lot of things but sometimes, the more complicated the report, the better it is to outsource some of the pre-processing back to the database and just let Crystal do the formatting.


    Add comment
    10|10000 characters needed characters exceeded

    • Hi Steve,

      Creating a view or stored procedure will depend on your database. I'd recommend googling how to create a view or stored procedure for your database. This sounds like it may be more complicated for you than expected.

      Good luck,