Solved

# How to Find a subtotal of a future field value

Posted on 2011-03-14
Medium Priority
382 Views
I am using Crystal Reports 8.5 and need to place a divider between records based on several criteria. It is first sorted by job_id and cust_part_no among other fields, and then I place a line in the details b that is only displayed if the pack_div is true.  My pack_total cannot exceed 12, so I am looking at the next record to see if it is > 12, but now I need to keep like part numbers together.  I know the total of the quantity for the part number of the record I am on, but I really need to know the total of the quantity for the part number of the next records.  Any help is appreciated.  Thanks.

shared booleanVar pack_div := False;
shared numberVar pack_total;
shared numberVar part_total;
shared numberVar job_line_total;

EvaluateAfter ({@Pack_Total});
EvaluateAfter ({@Job_Line_Total});

If ({KITTING_JOBS_CSV.ORDER TYPE} = 'AWB') or   //Broken Pack
({KITTING_JOBS_CSV.JOB ID} <> Next ({KITTING_JOBS_CSV.JOB ID})) or  //End of Job
(pack_total = 12) then
pack_div := True
Else If pack_total < 12 and
(pack_total + Next ({KITTING_JOBS_CSV.QUANTITY})) > 12 then
pack_div := True
0
Question by:juliemckenzie
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 4
• 3
• +1

LVL 17

Expert Comment

ID: 35134959
Have you thought about writing SQL scripts to create either VIEWS or a Stored Procedure that will do this by the creation of TEMP Tables that will calculate and/or aggregate you data totals as needed.

If you create SQL Scripts, you can add these at that datasource ADD COMMAND and use the as tables and link them together accordingly.

I've found that when you are trying to this kind of advanced aggregation or projection type of logic, its best to hard code it in SQL.

Just shooting from the hip here,....hope it helps...
0

LVL 35

Expert Comment

ID: 35136474
Are you trying to apply the pack_total limit of 12 at the part number level instead of the JOB_ID level, so that you put in the divider when the quantity for a part hits 12?  Or, when you say "total of the quantity for the part number of the next records", are you talking about the total for a different part number?

If it's the former, it seems like you could add a Next test on the part number, like the one that you have on JOB_ID.

If it's the latter, can you explain why you're trying to check the quantity for a different part number?  Understanding that might help find a solution.

James
0

Author Comment

ID: 35138164
The pack cannot contain more than 12, and it has to stay within the Job Id.  Currently, I am checking if the total plus the quantity from the next record is more than 12, then back up and put the divider there.  Now they want to keep part numbers together.  So if there are several successive rows with the same part number, then keep those together in a pack, so I thought I could check whether the current pack_total + the part_total of that part number was greater than 12, then I would put the pack divider before that grouping, else include the next part number in the current pack.

Thanks.
0

LVL 101

Expert Comment

ID: 35139047
Only problem with that is if the part has multiple lines you can't sum those up at that point.

mlmcc
0

Author Comment

ID: 35139066
That's my problem.  Either that or I need to read ahead several records, not just the Next.
0

LVL 101

Expert Comment

ID: 35139190

What database are you using?
You might be able to use a SQL expression

mlmcc
0

LVL 35

Expert Comment

ID: 35146003
What mlmcc said.  I don't see any easy way to get the total for an unknown number of future records.  A SQL Expression (as mlmcc suggested) might be one option.  Or, off the top of my head, maybe a subreport.  It's a pretty big "if", but I think it might be possible.

James
0

Author Comment

ID: 35151357
I've been trying a sub-report, but without any luck.  Unfortunatly, this data is coming from a flat file instead of a true database, so I'm not sure that I can create a SQL procedure.  I am having trouble passing a different value to the sub-report, other than a value from the record it is currently on.  I'm not sure what else is meant by creating a "SQL expression" other than a sub-report or the initial connection to the datasource.

0

LVL 101

Assisted Solution

mlmcc earned 300 total points
ID: 35151468
SInce you are using a flat-file you can't use a SQL expression.

Link it on the group field.
IN the subreport simply count the number of items required.
You can return that value to the main report in a shared variable
You can then use that value to determine if they will fit in the current case or if you start a new one.

mlmcc
0

LVL 35

Accepted Solution

James0628 earned 1200 total points
ID: 35154166
A SQL Expression is a separate query on a db that returns a single value, like a query that would return the number of records in a table for the current record in the report.  There is a "SQL Expression Fields" heading in the "Field Explorer", where you have "Database Fields", "Formula Fields", "Parameter Fields", etc.  If you can't use SQL Expressions with your datasource, that heading won't be shown.

I assume that the basic idea is to get a count of the records for the next part number in that job, to see if that count plus the current count is over 12.

I can think of 2 ways that you might be able to do that with a subreport.  The subreport would read the same file (assuming that CR will allow the main report and a subreport to read the same flat file at the same time) and be linked on the Job Id field, so that it only reads the records for the current job.  Personally, I always do the linking "manually" by creating a parameter in the subreport, setting the record selection formula in the subreport to compare that parameter with a field, and then linking the field or whatever in the main report to the parameter in the subreport.  But you can also just link the field to the subreport and use the subreport linking screen to tell CR to compare that field to the appropriate field in the subreport.

The two subreport ideas are:

1)
Create a formula that just contains Next ({part number field}) and link that to the subreport, so that it will only include the records in that job with that next part number.  This assumes that the records for each job are in part number order, meaning that the subreport can just count all of the records for that job with that part number.  If not, then getting the subreport to only include the correct set of records with that part number may be difficult, if not impossible.

However, I am not at all sure that you can use the Next function when passing a value to a subreport.  CR may not allow it.  If not, then this approach won't work.

2)
Link {part number field} (the current part number) to the subreport (in addition to Job Id), and have the subreport include the records for that job where part number is greater than that.  This will only work if the records for a job are sorted by the part number, so that the first part number that's greater than the current one is the next part number in the job.  If so, then the subreport would look for records where the part number was > the current part number and save the first part number that it found in a variable, and then only include that part number in the count.

James
0

Author Closing Comment

ID: 35184623
Unfortunately, it doesn't seem like this is going to work, but thank you for your help.
0

LVL 35

Expert Comment

ID: 35187631
Sorry that it didn't work out.

James
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customerâ€™s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. â€¦
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 biâ€¦
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrasâ€¦
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/weâ€¦
###### Suggested Courses
Course of the Month10 days, 17 hours left to enroll