CCRQBOM can generate a requirements list, a report that shows what items you need to purchase if you want to build a required quantity of an assembly. I’ll show you how to add some calculated fields to the report to provide total shortage cost calculation.Here is a requirement list to build a quantity 5 of assembly Bicycle. You can see that there are shortages for two inventory parts.
This uses the standard report template that comes with CCRQBOM.
To modify this template I will select the requirements template from the forms menu.
Select the requirements list option in the edit reports window and click the edit button.
This opens (sometimes after a bit of a delay) the report designer window. Please note that this window may look a bit different with some versions of CCRQBOM, but the elements should all be the same.
Creating a Calculated Field
The first step is to add a calculated field to hold the item cost X shortage value.
In the field list find the MasterDataRequiredData section of MasterData.
Right click on this section to select Add Calculated Field. This adds a calculated field to the field list. In the property grid lower on the screen you should see the properties for this field.
Change the name of the field to ShortCost, then select expression and click the “…” symbol to open the Expression Editor.
In the expression editor select the fields section. Locate ItemCost and double click it to add it to the expression. Double click the “X” symbol to add the multiply operator. Then double click the Shortage field to add that. Click OK to save this expression.
Note that in this case I’m using the ItemCost field, which is “Cost” in QuickBooks. You might want to use AverageCost instead.
Select this ShortCost field for the data binding for the last column, as we illustrated before with the ItemCost field.
Adding Columns
I’ll add two columns, one for ItemCost and one for the calculated shortage cost. I’m not going to make the report pretty, I’m just going to add the columns. You may want to rearrange things or delete unnecessary columns.
Right click on the rightmost column in the Detail1 section, select Insert, and then Column to right. Do this again to add a second column. Repeat this with the headers in GroupHeader1. Then resized the columns to fit what you want.
Double click on each heading column and enter the column names, “Cost” and “Short Cost”.
Change the column headings by double clicking on them and typing in the text you want.
Find ItemCost in the Field List, drag it and drop it on the cell for that column in Detail1. Do the same with ShortCost for the last column.
Select each of the cells and set them to be right justified.
Select each cell, click on the “>” symbol, and set the format string to show currency.
Add the Sum to the Footer
We have a column that shows the calculated cost of the shortage. Now we want to add the sum of this to the report.
First, we want to add a GroupFooter Band to the report, to have a place to hold the sum. This will put the sum at the end of the report for each assembly. (Note: you might add a “ReportFooter” instead of a GroupFooter – that is always at the end of the report. There isn’t much difference between them in most reports, unless you have multiple groups – then you should use a ReportFooter).
Right click on the Detail band, select Insert Band and then GroupFooter.
In the field list, find the ShortCost field and drag it to the GroupFooter.
Position the field to where you want it, resize the GroupFooter as appropriate.
Next, click on the < symbol again, click the ellipsis (…) by Summary.
Select the Group option in Summary Running. Also set the format string to the currency format.
Click OK to close this window, then save your report with the File/Save As menu option and give it a name. Close the editor.
Here is our modified report, showing the unit cost for each item, the shortage cost per item, and a sum of the shortage cost at the bottom.
