Hello,
I have been tasked with trying to add the column ‘OnHandQty’ to the BOM Listing report. I am unsure on how/where to pull this information from.
I found the table PartQty which seems to have the info needed but it is broken out by warehouse.
I need it summed up per partnum before it gets to the report.
Any ideas on how to acomplish this?
Any help is greatly appreciated!
PartBin table also has OnHandQty, but probably the same issue that it will break it our by warehouse.
BOM Listing RDD uses PartMtl table. You may be able to join PartBin (or PartQty) on PartNum… and then “group” the results in the report .rdl file by PartNum.
OR… if you want to sum across warehouses, you could create a BAQ that sums the quantities (calculated field) and then add the BAQ as a Data Source to your RDD.
Can you tell me how to add as a datasource?
This is a biggie… a lot of steps… hopefully I don’t miss any, but here we go…
Go to Report Data Definition menu.
Search/Open: BOMListingRpt
(This is the Data Definition used by BOM Listing Report.)
You can’t alter the “out-of-the-box” RDD… so you need to go to Actions > Duplicate Report. Save it with a new name (ie. BOMListingRpt_XXX)
This is now a new custom Report Data Definition (RDD).
Under “New” icon, select “New Table”.
Click the “Schema Table” search and find PartQty… or whatever you want to add.
Give it a name in the “Report Table” field. I usually use the same default name, but I put it in ALL CAPS. That way, whenever I look at a Data Definition, I can easily see the tables I’ve added to the base RDD. If the name is in ALL CAPS, I added it.
Save.
Once the table is added and the screen refreshes, you can highlight that table in the tree view. Then find the “Exclusions” tab.
In the Actions Menu, select “Include all Columns”. This will make all the columns in that new data source available to you.
You then need to create a new “Relationship” to join the new table to your query.
Under the “New” menu, select “New Relationship”.
Relationship: PartMtlPARTQTY
Description: PartMtl to PARTQTY
Part: PartMtl
Key: (can probably leave blank)
Child: PARTQTY
Relation Type: Output
Save
Add Relationship Fields (these are the fields that create the join):
Company to Company
MtlPartNum to PartNum
RevisionNum to RevisionNum
Save
That should be it in the RDD.
You’ll now need to update your Report Style to look at this newly created RDD.
Open Report Style Maintenance… search/open BOMListingRpt.
If you haven’t already, you’ll need to create a “new” style since we’re making changes to the out-of-the-box form.
Actions > Copy Report Style.
Give it a new name. Save
Click on the new “style” in the tree view. Then update the Data Definition field by choosing your custom RDD you created above.
Save.
You can now update your SSRS report (.rdl file) in Report Builder.
You’ll need to update the query to include the Data Source (table) you added to the RDD.
Open the .rdl file in Report Builder.
In the tree view on the left, expand Datasets.
Right Click on “PartRevPartMtl” > click “Dataset Properties”
Under Query, click the little “fx” (function) icon to expand the query designer.
Expand the window so its easier to read.
Add the new table to the end of the query (as shown below):
make sure there is a space after each line (highlighted in the above) and also make sure there is a quotation marks at the end.
Then, at the top of the query, add T3.OnHandQty, (and any other columns you want to add from the new table) to the “Select” argument.
(Make sure there is a comma between each column you add).
Click “OK” to close the window.
Stay in Dataset Properties. Click on “Fields”.
Click Add > Query Field
Put “OnHandQty” in both the Field Name and Field Source boxes.
Repeat this for any other fields you added to the query.
Click “OK” when done to close Dataset Properties.
Save RDL file.
In Report Style Maintenance… with you new style selected… Actions > Upload SSRS Report. Make sure it uploads successfully with no errors.
At this point you’ve done everything to add the table and desired columns/fields to the report. If successful, you can go back to Report Builder and add a new field in the body of the report to display your OnHandQty where you want it.
Save / Upload / Test… tweak… repeat.
For just a single level, the Part Availability report (Production Management | Engineering Management) kinda does this.
Doesn’t that inflate the rows because of the multiple warehouses?
I was asking how do you add a BAQ to the existing RDD to have it sum the partnums and put the results in the report?
Well, unfortunately, I misspoke in my original response (my bad). Unfortunately you can’t add a BAQ as a datasource to a copy of a base RDD. Its on everyone’s wish list, but unfortunately, not available. You’d have to recreate the entire BOM Listing as a BAQ and the create a new RDD from scratch.
If you go the other route (adding PartQty table to the existing RDD), you should be able to group on MtlPartNum in your SSRS report and only get one row per part.
I will give that a shot!
I was hoping you had a new feature that would work for me. Who knows, maybe soon…
Thanks for the help!
Anyone have success with this? I have added the PartQty table, PartWhse table and still not getting any data
