Problem: Fishbowl Inventory is a very popular integrated application for QuickBooks users due to the enhanced functionality it offers. The invoices use one of approximately 10 items which each begins with "FB_" then a description such as Shipping, non-inventory, etc. This creates an issue when using QuickBooks for detailed sales reports, commission reports, etc. While it is possible to create the detail reports in Fishbowl Inventory, this is usually difficult without extensive iReport experience or costly to have a custom report created.
Solution: The item level detail is included on the invoice as the beginning of the item description. So by extracting it into a separate column, traditional sales reports with all the grouping, subtotals, etc are possible.

QuickStart Option: Download the memorized report template for the Fishbowl Sales by Item Detail report to serve as a starting point for your modifications using the instructions for importing report templates.

Note: Our example is a little more complicated because Fishbowl inventory was just implemented but the report needs to include both pre- and post-Fishbowl transactions.

Step 1: Start with the Sales by Item Detail report and add any additional information to appear on the report. In our example, we added the sales rep. Also, change the date range to be a small amount of time so the report will refresh quickly while testing. In the example here, we chose a date range that included both 2009 (using QuickBooks items) and 2010 (Fishbowl Inventory was implemented January 1, 2010)

Step 2:  Determine if the item is the QuickBooks item or a Fishbowl item using a calculated column.


Note: If the sales all originated in Fishbowl, this step is not needed.

Step 3: Based on the computed column of "True" that it is a Fishbowl item, the calculation will pull all the data prior to the " - " which Fishbowl uses as the divider between the item name and the actual description. In the example here, we were looking for the lowest level of the item. Most were parent:sub but many were parent:sub:sub. The computed column below checks to see if there is a ":" in the name of the sub, if so, it takes the item after the ":" but if not, it will just return the sub-item.


Note: If the sales all originated in Fishbowl, the formula would be:
SPLIT_TEXT([DESC]," - ",1)

Step 4: Memorize the report!

At this point, the Combined Item column is available to be used for grouping, subtotals, etc.

Note: For this specific report, the combined item needed to also be split into segments for analysis purposes.