Solved

How to Find a subtotal of a future field value

Posted on 2011-03-14
12
372 Views
Last Modified: 2012-08-13
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
Comment
Question by:juliemckenzie
  • 4
  • 4
  • 3
  • +1
12 Comments
 
LVL 17

Expert Comment

by:MIKE
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 34

Expert Comment

by:James0628
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

by:juliemckenzie
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 100

Expert Comment

by:mlmcc
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

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

Expert Comment

by:mlmcc
ID: 35139190
You can only read ahead 1 record.

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

mlmcc
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 34

Expert Comment

by:James0628
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

by:juliemckenzie
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 100

Assisted Solution

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

What you want is in the group header add the subreport
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 34

Accepted Solution

by:
James0628 earned 400 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

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

Expert Comment

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

 James
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now