Creating a BOM report using Jet Reports - Part 1

If you're involved in manufacturing, at some point you'll need a bill of materials to identify all the ingredients in a finished good.  

A SQL developer will describe this as a recursive query.  Most Jet Analysts will call this a PITA.  And the business owners say, "Yes, this is absolutely critical to my company."

I used recursive queries to support a reporting need for a ready-to-bake-turkey manufacturing company who needed a lot tracing report.  If there was a recall on XYZ spice or a supplier informed the manufacturer of a bad batch of turkeys, we needed to be able to identify and recall all finished turkeys where that lot was used.  Similarly, when my cookie manufacturing client lands a new distributor, we need a recipe report we can turn into a shopping list to ensure the availability of chocolate chips and organic flour to match future raw materials demand.  Alternatively, I might use this idea of a recursive query to find out all the employees who fall under a specific manager for HR reporting.

Let's take a look at building a BOM report exclusively using the tools available in Jet Essentials.

At the end of the day, a BOM report in Jet Essentials is a fixed n-level grouping report.  If all your recipes have the same n stages to get from Raw Materials to Finished Good then you're golden, but if recipes are ragged and have different number of stages, you will end up with empty rows where the recipe n-1 stages.  

This is your classic Production BOM Line (the recipe table with all the ingredients and quantities) report using 4 levels of grouping.  With each NL("rows=x") function we list out the ingredients of the preceding Item (Production BOM No.)  We'll need to get fancy with Conditional Hides to hide rows that don't contain data (it might take 4 stages to build a road bike and only 3 stages to build a bicycle pump), but this is the foundation for recursive reporting in Jet Essentials.

For a reminder on how to build a basic grouping report check out this video.

While showing each line item and calculating Quantity per recipe is a good start, if you think back to my cookie company example, we might list out multiple recipes (Chocolate Chip, Sugar Cookies, and Ginger Snaps) that use the same raw ingredients - each cookie recipe uses flour and eggs.  In our current multi-level grouping setup, we'd have three lines for flour!  Ideally, we want just one line reading flour and total flower required for all three recipes.  To really put the spitshine on this report, we have to hide rows 9-14 and summarize the report using some more fancy Jet functions.

In H16, we'll use an NP("Join") function that takes all the items in Column H and joins them into a "|" separated string"ItemA|ItemB|ItemC." Next, Excel's Substitute() function removes any extra "|" from the string before the NL("Filter") (H17) 'uniquifies' our Item No.'s so we only see each item once.  Lastly in column J we'll do a SumIF to add up all the Total Quantities for the item in column K.

Here are the results of our efforts when I run the report to show the shopping list for 2x Bicycles and 1x Front Wheel.

Although the numbers add up properly, the shopping list is misleading because it shows intermediate products which we manufacture (2x Front Wheel and 2x Back Wheel) interspersed between raw materials (5x Rims and 250x Spokes).

Ideally, the final step would be to somehow identify and hide the intermediate products.  Maybe an NL("First") to the Production BOM Header table to identify if the item has a recipe. "Front Wheel" (yes) or "Rim" (no).

In conclusion, I'd label rate this a 'good' BOM report.  It gets the job done, but it has a strict limitation.  Because Jet Essentials doesn't have a recursive function, you MUST know the maximum number of intermediate steps.  If you need n+1 recursions with an n level grouping report, you'd miss out on some ingredients!

My hardcore Jet Essentials readers are thinking, "Recursive queries are a SQL thing, can't I use 'SQL=' to write my own SQL queries in Jet Essentials?"  Why yes you can!  And that's the topic of the next post!  Join us for Part II where we'll build and maintain a Custom View in Jet Enterprise then report against it using "SQL=" in Jet Essentials!