Writing Code
 Crystal Reports Forum : Crystal Reports .NET 2003 : Writing Code
Message Icon Topic: Crystal XI Conditional Sum Post Reply Post New Topic
Author Message
jcamp66
Newbie
Newbie


Joined: 26 Sep 2012
Online Status: Offline
Posts: 4
Quote jcamp66 Replybullet Topic: Crystal XI Conditional Sum
    Posted: 26 Sep 2012 at 5:00am

I have a table with data called InvoiceShipments. It continues a row for each product shipped on an invoice. Each product belongs to a product category, which I can query and filter by. Some of the products are finished good products with a Bill of Material, where the Bill of Materials (BOM) is a list of the parts that combine to make the finished good.

 

In the InvoiceShipments table, the finished good is listed with a price but no cost. It is then followed by the components (BOM) of that finished good, which in turn have a cost but no price. I have a separate table that lists all of the component items and which finished goods it goes to. Note that component goods can belong to more than one BOM.

 

InvoiceShipments Sample Data:

 
InvoiceShipments%20Table
 

ProductBillofMaterials Sample Date:

 
Bill%20of%20Materials%20Table

I can currently filter the InvoiceShipments by the products that I want based on the product category (from a join to a different table). What I want to do is grab that finished good number, and get a list of all the part #s that make up that BOM, then go back to the InvoiceShipments and sum the costs for all of the rows that match those component #s and invoice#. But I haven't been using Crystal long enough to know what to do at the query level, what to do with a command table, what to do with a formula, etc.

 

My desired outcome would look like:
 
Any help you can offer would be greatly appreciated.
IP IP Logged
jcamp66
Newbie
Newbie


Joined: 26 Sep 2012
Online Status: Offline
Posts: 4
Quote jcamp66 Replybullet Posted: 01 Oct 2012 at 4:00am
Okay, I've managed to change the data structure enough to change the problem and hopefully make it a "one-table" problem. I've now added a product category to the InvoiceShipments table.
So what I want to do is...every time it comes to a row that is of a category that is "Finished Good" I want to loop through the remaining rows and sum the cost of the subsequent rows until the category no longer is "Component" and return that cost as the cost for the Finished Good row.
I've tried to put in For loops, whileReadingRecords, etc. but the syntax in Crystal is eluding me. Any help would be greatly appreciated.
 
 
 
 
IP IP Logged
jcamp66
Newbie
Newbie


Joined: 26 Sep 2012
Online Status: Offline
Posts: 4
Quote jcamp66 Replybullet Posted: 01 Oct 2012 at 4:01am
Sorry, my data structure was invalid in the last post. The Invoice Number is incremented each row and it shouldn't be. The first three rows should be Invoice# 1, the second 5 rows should be Invoice #2.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 01 Oct 2012 at 10:29am
Try this (this assumes that there is only one item in the "Finished Good" category for each invoice...):
 
1.  Group by Invoice.
2.  Group by Category - set the order to "In Specified Order" with "Finished Good" first and then all others.
3.  Put the data in the Invoice group header - the Invoice Number, SKU, Category and Price will all come from the current record, which should be the Finished Good record and the Cost will be the sum of the cost values for that invoice.
4.  Suppress the details section and the group header and footer sections for the Category group.
 
-Dell


Edited by hilfy - 01 Oct 2012 at 10:30am
IP IP Logged
jcamp66
Newbie
Newbie


Joined: 26 Sep 2012
Online Status: Offline
Posts: 4
Quote jcamp66 Replybullet Posted: 02 Oct 2012 at 2:33am
Thanks for the feedback. There will frequently be more than one Finished Good on an invoice, but I appreciate the suggestion!
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 03 Oct 2012 at 3:29am
How do you relate the components to the finished goods?  There's a way to handle that.
 
-Dell
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.