Creating a BOM Report using Jet Reports - Part 2

I don't know why Christmas cookies are on my mind.  Probably because I need to go to Tesco's (grocery store) and I'm asking myself, how much flour, milk and eggs I should buy.  However, before I can know what to buy, I need to know what I'm cooking, how many batches I am going to make, how much inventory I currently have, and finally forecasted growth just in case more guests RSVP for Christmas dinner!  If I wanted to get really fancy, maybe I could group the ingredients by Vendor or section of the grocery store!

Although we're no where near Christmas season, maybe some of you who do purchase planning for manufacturing companies are already thinking about raw material demand, consumption and ordering.  Ideally, if volume is high enough, you'll lock in price contracts months ahead of when you'll actually need the inventory just to save a few pennies per pound which could translate into thousands of dollars per year!  But how do you project demand?  Especially with a shifting landscape involving new customers, fluctuating trends, shifting market prices, and expiring inventory!

"I just want to know how much raw material I need to cover production."  You need a Bill of Materials (BOM).  Some of this functionality is built into NAV, but (as far as I know) there is no way to say: "I want to know how much flower I need if I'm making 100 batches of Ginger Snaps, 300 Thin Mints and 50 Widgets."

In Part I of this series, we looked at building a BOM in Excel using Jet Essentials.  We used the idea of a multi-level grouping report to list out each item in a recipe, then all the items of assembled intermediate items, and all the subitems of the intermediate to the intermediate and so on.  Going back to Christmas dinner, I'll need butter in the gravy, on the turkey, in the stuffing as well as in the bread pudding too, but at the grocery store i just want to see: "Jae, you need 3.5 pounds of butter."  (Who am I kidding?  I build reports, I don't actually cook dinner! ;)

The screenshot bottom left is the Jet Essentials Grouping Report.  If you recall, we were building 2x Bicycles and an extra back wheel.  Unfortunately, the output is a bit confusing because we're seeing the intermediate parts Front Wheel and Back Wheel in addition to requiring 5 Rims! 

Jet Enterprise Shopping List Overview
Before I get carried away discussing HOW you're going to build this, I want to pause and think about how we can use this.  Obviously this can help my Purchasing team stay on top of raw material demand when a big sales order comes in or a national wholesaler client is added, but that's just the tip of the iceberg!  

If a key vendor announces bankruptcy or there is a spike in demand for coffee, wood pulp, oil or copper, this report will show all the Finished Goods we produce which may be affected.  Similarly if we recycled this structure for actual Production Orders we could see every lot of finished good affected if we needed to do a recall due to a faulty component or ingredient.  Lastly, if on the heels of a good trade show you predicted a spike in demand for cerulean blue sweaters or chipotle flavored foods, you could do a What-If analysis to help plan purchasing.  The truly best part is, you can take this report, marry it up to your ERP and show not only what you would need, but also what you have, what's expiring, and what's currently on order!

If I've piqued your interest, please contact me at Jae@OnyxReporting.com, I'd happily send you a sample of the report used in this blog or discuss implementing a solution like this in your Jet Enterprise environment!

But now, let's get nerdy and discuss building this bad boy.

Surely the SQL developers are thinking, "Well I could build a recursive SQL query to get this data, but how do I pass it into a Jet Report?"  Jet Essentials allows you to write SQL into a report using "SQL=Select..." for the table argument.  Honestly, I don't use this technique very often because it can be difficult to maintain or modify, but this project is the perfect opportunity to blend Jet Essentials, SQL and Jet Enterprise!

1) Build a Shopping List Table in the Jet Data Warehouse. 

Theoretically we could build the entire shopping list report from start to finish within a SQL query in a Jet Essentials report; however, ideally we'd build the ShoppingList table in our Data Warehouse because we can recycle this functionality in many reports (also as the number of recipes increases, reporting against a static table will improve performance)!  

Ultimately I want a table where I can ask: "How many and which raw material items will I need to produce one finished good."  To answer this question, we'll use the Production BOM Line table to construct a recursive query that shows all raw materials used in a Production BOM and loop through until you run out of intermediates.  (Remember the BOM table is just the recipe guide, if you need Lot tracking, the Production Order Line table in conjunction with the Item Ledger Entry records what actually happened.)

If we query the ShoppingList table in the DWH, we can now see something similar to the Jet Essentials report we built in Part I except it doesn't include intermediates AND it isn't limited to N number of recursions.  It can loop through an infinite level of intermediates!  Take a look at this MSDN article for a thorough-ish introduction to recursive queries with CTE's.

2) Reporting Total Quantity needed in Jet Essentials

At this point an NL("Table") of ShoppingList will return the ingredients for a Bicycle and a Front wheel, but how will we get the ingredients for 3x Bicycle and 5x Front Wheels?  

• Total Rims (Item 1110) required = 3 Bikes x 2 Rims per bike + 5 Front Wheels x 1 Rims per Wheel = 11 Rims total.  
  

In Jet Essentials, we'll use the Options page in conjunction with some Excel concatenation magic to produce a SQL statement that multiplies the [Quantity] for each recipe ingredient by the [Options Value].  

But before running THAT query, we'll wrap it all in another SQL statement to find the SUM[Total Quantity Per Recipie] grouped by Raw Material item No to get a unique list of Raw Material Items and the TotalQuantity needed.

/wipes brow.

---

I hope that made sense and whetted your appetite for what is possible with Jet Essentials and Jet Enterprise (and a skilled Jet Report writer ;)  Please feel free to email me on our Contact page for a copy of the functional Jet Essentials report or to start a discussion on how we can implement this for you and your company!