Crystal XI Conditional Sum
Printed From: Crystal Reports Book
Category: Crystal Reports .NET 2003
Forum Name: Writing Code
Forum Discription: .NET 2003 programming API, report integration
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=17630
Printed Date: 01 May 2024 at 10:26am
Topic: Crystal XI Conditional Sum
Posted By: jcamp66
Subject: Crystal XI Conditional Sum
Date 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:  ProductBillofMaterials Sample Date:  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.
|
Replies:
Posted By: jcamp66
Date 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.  |
Posted By: jcamp66
Date 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. |
Posted By: hilfy
Date 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
------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: jcamp66
Date 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! |
Posted By: hilfy
Date 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 ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |