# can you build us a sales BI report in Fishbowl - monthly revenue trend plus our top products and top customers? let me preview it before it goes in.

> A real Claude session on a Fishbowl database (ILC's demo company, sample data), connected through ILC MCP for Fishbowl. The answer below is what the AI said; the queries are what it ran. Page: https://israellopezconsulting.com/fishbowl-mcp/questions/report-bi-report

## Question

can you build us a sales BI report in Fishbowl - monthly revenue trend plus our top products and top customers? let me preview it before it goes in.

### Answer

Here's the live preview of **Sales Overview**, running against your Fishbowl data in read-only mode. Nothing is installed yet:

**What's on it**
- **Period selector:** last 3, 6, 12, 24 or 36 months. It opens on 12.
- **KPI tiles:** net revenue, order count, average order value, and last full month compared with the month before.
- **Monthly revenue bar chart:** the current month is shown lighter because it's still in progress. Hover over a bar to see its revenue and order count.
- **Top 10 products and top 10 customers:** each shows revenue and its share of the total, with a share bar.

**How revenue is defined.** Please check this matches how you count sales:
- Orders are bucketed by **issue date** (order basis), not ship date.
- Revenue counts the order statuses Issued, In Progress, Fulfilled, Closed Short and Historical. It leaves out Estimates, Voided, Cancelled and Expired.
- Revenue is net of credit returns and discounts. Tax, shipping and subtotal lines are excluded.
- Product share is calculated against product sales only, because order-level discounts can't be tied to a product.

**Sanity check, last 12 months:** September was the strongest month at about $86.8K. Chocolate Chips (CR403) is the top product at about 14% of product sales. Allen's Groceries is the top customer at about $22.7K.

Once you've looked, tell me what to change, or say "install" and I'll put it into the BI Report module as "Sales Overview." After it's installed, a Fishbowl admin will need to grant groups access before anyone else can see it.

## Follow-up

that's exactly what i wanted - the revenue definition works for us. go ahead and install it.

### Answer

**Sales Overview** is installed in Fishbowl's BI Report module and shows up there now, with no restart needed. The preview ran with no errors.

At the moment only Fishbowl admins can open it. For anyone else to see it, an admin needs to give their group **View** (or **Edit Settings**) access on the Sales Overview BI report in Fishbowl.

This install is saved as version 1. If you want changes later, just ask and I'll update this same report, so the access settings stay in place.

## Behind the scenes

Every step the AI took, as recorded in the query log. All queries are read-only and row-capped.

1. Read the Fishbowl notes index — 64 topic notes
2. Read Fishbowl notes (BISCRIPT.md)
3. Read Fishbowl notes (CLOUDPAGES.md)
4. Read Fishbowl notes (SALES.md)
5. Ran a read-only query: Understand sales order volume, statuses and date range to design a sales BI report — 5 rows, 269 ms

   ```sql
   SELECT so.statusId, COUNT(*) AS orders, MIN(DATE_FORMAT(so.dateIssued, '%Y-%m-%d')) AS first_issued, MAX(DATE_FORMAT(so.dateIssued, '%Y-%m-%d')) AS last_issued, SUM(so.subTotal) AS subtotal
   FROM so
   GROUP BY so.statusId
   ORDER BY so.statusId
   LIMIT 101
   ```

6. Ran a read-only query: See which sales order line types exist and their totals, to define revenue — ? rows, ? ms

   ```sql
   
   ```

7. Ran a read-only query: See which sales order line types exist and their totals, to define revenue — 5 rows, 179 ms

   ```sql
   SELECT si.typeId, COUNT(*) AS line_count, SUM(si.totalPrice) AS total, COUNT(si.productId) AS with_product
   FROM soitem AS si
   GROUP BY si.typeId
   ORDER BY si.typeId
   LIMIT 101
   ```

8. Ran a read-only query: Monthly net sales revenue trend for last N months, order basis — 12 rows, 244 ms

   ```sql
   SELECT DATE_FORMAT(so.dateIssued, '%Y-%m') AS month_key, COUNT(DISTINCT so.id) AS order_count, SUM(si.totalPrice) AS revenue
   FROM so
   JOIN soitem AS si ON si.soId = so.id
   WHERE so.statusId IN (20, 25, 60, 70, 95) AND si.typeId IN (10, 11, 12, 20, 21, 30, 31) AND so.dateIssued >= DATE_SUB(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL @months - 1 MONTH) AND so.dateIssued < DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 1 MONTH)
   GROUP BY month_key
   ORDER BY month_key
   LIMIT 101
   ```

9. Ran a read-only query: Top 10 products by net sales revenue over last N months — 10 rows, 196 ms

   ```sql
   SELECT p.num AS product_num, p.description AS product_desc, SUM(si.qtyOrdered) AS qty, SUM(si.totalPrice) AS revenue, SUM(si.totalPrice) / SUM(SUM(si.totalPrice)) OVER () AS share
   FROM so
   JOIN soitem AS si ON si.soId = so.id
   JOIN product AS p ON p.id = si.productId
   WHERE so.statusId IN (20, 25, 60, 70, 95) AND si.typeId IN (10, 11, 12, 20, 21) AND so.dateIssued >= DATE_SUB(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL @months - 1 MONTH) AND so.dateIssued < DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 1 MONTH)
   GROUP BY p.id, p.num, p.description
   ORDER BY revenue DESC
   LIMIT 10
   ```

10. Ran a read-only query: Top 10 customers by net sales revenue over last N months — 10 rows, 318 ms

   ```sql
   SELECT c.name AS customer_name, COUNT(DISTINCT so.id) AS order_count, SUM(si.totalPrice) AS revenue, SUM(si.totalPrice) / SUM(SUM(si.totalPrice)) OVER () AS share
   FROM so
   JOIN soitem AS si ON si.soId = so.id
   JOIN customer AS c ON c.id = so.customerId
   WHERE so.statusId IN (20, 25, 60, 70, 95) AND si.typeId IN (10, 11, 12, 20, 21, 30, 31) AND so.dateIssued >= DATE_SUB(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL @months - 1 MONTH) AND so.dateIssued < DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 1 MONTH)
   GROUP BY c.id, c.name
   ORDER BY revenue DESC
   LIMIT 10
   ```

11. Drafted the page — html draft (v1)
12. Edited the page
13. Rendered a live preview (https://your-fishbowl.cloudpages.app/preview/sales-overview) — live preview rendered
14. Checked the preview for errors — no errors
15. Installed the BI report — Installed BI report 'Sales Overview' in this Fishbowl (BI report (biscript note 

Learn more: https://israellopezconsulting.com/fishbowl-mcp · More examples: https://israellopezconsulting.com/fishbowl-mcp/questions