Record Selection - Data Extraction

[kbrown73]

I have a database that contains production data from a reporting system. The way this database is built some records are "linked" or "related" to a previous record but does not contain all data needed. For example:

JobName CycleTime GrossPieces NetPieces
Job 1 62 145 122
Job 2 70 0 0
Job 2a 0 198 185
Job 2b 0 198 185

Job 1 above is only a single piece job. No problem here. Job 2 is a record that contains information about a family job (Job 2a and Job 2b) maybe right and left hand parts. But as you see Job 2 contains my cycle time for jobs 2a and 2b. I am throwing out all records in Crystal Reports Record Selector that have Gross and Net = 0. The only records that I need are ones that pieces have been produced. I need a way in Crystal to grab the "70" from cycle time from Job 2 and use it for 2a and 2b for performance calculations. Can this be done through the Record Select, or within a formula using "WhileReadingRecords"?

 

[kbrown73]

Sorry, Crystal Reports 8.5 is being used...

 

[bdreed35]

Your best bet would to create a formula that returns the "Job " and the number.

left({table.JobName},length({table.JobName})- 1)

You can insert a group based upon this formula. This will isloate each Job together.
Next, add the JobName database field to the Record Sort Expert. Make sure it is sorted Ascending.

Create a formula that gets the maximum of cycle time for the group, and populates a variable.

global numbervar CycleTime := maximum({table.cycletime},{@JobName});

Place the formula in the @jobName formual group header.
You can now reference the variable in your calculations.

~Brian

 

[synapsevampire]

Since you're throwing out the rows with 0 grosspieces, etc. using the record selection formula, you won't have that data in the report.

You might do this more simply on the database, but you'd have to share the type and version for that to be broached (you should include that with any post).

The difficulty as I see it as that you have child data mixed with parent data, so I would attempt to correct the shortcomings of the database first.

Taking a flaming loufah to the database architects spleen is a good start.

You might cheat this by creating a formula to group rows on the job<X> to obtain the cycletime.

First, nuke the record selection formula which eliminates any required data in the report, this is NOT the appropriate way to not display data that you need.

Assuming that your examples are sound, create a formula such as:

@MyGroup
if not(isnumeric(right({trim({table.jobname}),1))) then
left({table.jobname},len(trim({table.jobname})-1)
else
trim({table.jobname})

Now you have a generic grouping that eliminates the last character if it's alphanumeric.

Group on this formula, and display the results in the details, except that you will use a suppress formula in the Details X 2 supression formula such as:

next(@formula) = @formula
and
isnumeric(right({trim({table.jobname}),1))

This will suppress the Job2 scenario row.

The last thing you'll need is to have a formula on hand for the last valid Job2 scenario Cycletime, to accomplish this, sort by the real jobname and use a formula in the group header to obtain the first cycletime:

GH1 formula:
@cycletime
whileprintingrecords;
numbervar Latest:={table.cycletime};

Now you can use this formula in any formulas requiring cycletime:
whileprintingrecords;
numbervar Latest;
Latest+<some value>

Hope this helps to describe one approach.

-k

 

[synapsevampire]

Brian, I like the maximum idea for obtaining the cycletime, but your grouping formula won't work as you will truncate the parent row (such as JOB2 to JOB) as well, see my example.

-k

 

[bdreed35]

Yeah, I did overlook that didn't I?

~Brian

 

[kbrown73]

First off, thanks for your assistance. It has gotten me thinking about a couple different things (the flaming loufah for one).

Point one: The naming examples that I gave are not sound, they were for simplicity. The names will vary with "618" for the parent job to "XLR PNL Assembly Upper RH" as one child job and "XLR PNL Assembly Upper LH" as the other child job. The naming convention of these jobs is another problem that I have with the DB.

Point two: The next problem that I have is this report is extremely complex in the groupings already. It is grouped by Press (A1, A2, A3, B1, B2, etc). These are actual names BTW. Then by Shift number (1, 2, 3). Then by the job (618, 608, 639Shale, 639Brown, etc.) For each job per shift I am calculating an OEE or Overall Equipment Efficiency based on Availability, Efficiency, and Quality. The standard cycle is calculated in Efficiency.

Then: Avail * Eff * Qual = OEE

I am getting running totals based on Gross Pieces, Net Pieces, Downtime, Rejects, etc based on each job to calculate Avail, Eff, and Qual. Then I am taking the Weighted average of all the OEE's to calculate an "Overall" OEE for the entire plant in the Report Footer.

Hence If I leave in the Parent Job (Job2) for example and suppress it on the report would it still use those records (even though they are suppressed) in my calculations? I tried this and it seemed to not change the Overall OEE result.

This may be getting off track, but it still does not get me cycletime on the child records...HELP.

 

[synapsevampire]

I figured you were going to say "only kidding" about the example data, as you can see, it helps to demonstrate some theory, but was a waste of our collective time as a result.

To address each concern, I fear that you might be better suited to create a subreport since the office abviously doesn't have reasonable database programming available.

Did I make it clear that your database design sux? It looks like it was designed by an application programmer, scourge of the world, they be

The tables should have a hierarchial relationship at least, wherein the rows have a parent ID on them.

I guess that I'd juist bite the bullet and use some logic to test for everything, and also within each forumal/Running Total, as in:

Detail formula:
whileprintingrecords;
numbervar Cycletime;
numbervar othercycle;
If GrossPieces = 0 and NetPieces = 0
and
jobname in next(jobname) then
Cycletime:={table.cycle}
else
othercycle:=othercycle+{table.cycle}

Soemthing like that, I'm kind of burned out on this thread.

You'll have to apply this sort of logic to every formula though, inclusive of summaries.

Might add some whirling razor wire to that flaming Loufah.

There are plenty of approaches here, this is but one.

You might also link the data into Access and massage it first in Queries, or you might create Stored Procedures on the database itself, and other options such as subreporting.

You still didn't even bother to state the database type.

-k