0

# Capturing next in sequence

Jan 30, 2017 at 03:34 PM

108

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

operation

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?

Abhilash Kumar
Jan 30, 2017 at 03:40 PM
0

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.

-Abhilash

Show 1 Share
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.

Steve Daniel Jan 30, 2017 at 05:25 PM
0

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?

Share
Abhilash Kumar
Jan 31, 2017 at 11:16 AM
0

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

-Abhilash

Show 1 Share

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?????

Ian Waterman Feb 06, 2017 at 10:47 AM
0

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.

Ian

Show 2 Share

ok, so how does a shared variable work in this instance? If I am looking through a database where every operation has a different set of data and I want to see which is the first line that has not been completed, how would I set that variable up? I know what I'm looking for, but how do I tell Crystal that if there are 7 sequences and everything is complete down to sequence 5 that the correct answer is sequence 5?

example.jpg

in this example operation sequence 2 is open. Although this is not where it is really (See sequence 5) I would accept location being sequence 2. Does this help?

example.jpg (45.2 kB)
Ian Waterman Feb 07, 2017 at 04:46 PM
0

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

Ian

Share
Steve Daniel Apr 28, 2017 at 12:50 PM
0

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)
Show 2 Share

Still not totally sure what you are trying to achieve.

I assume the image shows data as it is now.

Please show how you want it to show and what value you are trying to count/measure

Ian

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.

Brian Dong Apr 28, 2017 at 09:06 PM
0

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.

Brian

Show 1 Share

Don't know how to do either of these.