Creating a Custom Report

We wish to create a report of all product departments so that the output is like the following

Depid	Department Name	Product Count	Sales YTD	Sales Last Year	Strikerate
17	Fresh	283	35687.88	740893.84	27.4%
34	Cleaning	67	4619.80	82463.30	4.3%

Strikerate is the percentage of sales that include at least one item from that department

Method 1 - Classic API calls

The obvious solution is that we write some javascript to call various APIs and assemble the results.

Pro	Con
Easy method, just write some javascript Can possibly vibe code with AI	Need to understand Javascript Output is only HTML page

This would be the logic

1. Request all departments using ...
2. For each department, request full department details. This will retrieve product count.
3. For each department, request total sales for that department so far this year.
4. For each department, request total sales for that department last year.
5. Request total sales "this year". We need this total to derive strikerate.
6. Wait for all data to calculate, draw to web page. Alternatively, draw the web page as soon as we have the initial list of departments, and then populate the information as it arrives.

Using FD1, once we have the initial list, we can freely issue requests for all the additonal data required. This could be hundreds of requests, but that wont worry the server or the protocol.

Method 2 - using fd1.data to create a table

The endpoint fd1.data has the ability to derive a "table" from existing tables of data. This is done server side, and the results sent to the client. There is minimal difference in performance, from method 1, as the bottleneck in this case is the actual time to scan all sales.

Pro	Con
Can download results in different formats, html, curl to csv, Excel Can be used as input "table" to another report	Need to understand fd1.data

fd1.data works by taking input data sources (aka table) applying filters and other processing to transform the table and outputing the final result. They are easiest to understand visually

				Source Data departments
⇓
Source Data products	»	Filter / Group Get count by department	»	Add / Merge Product Count
⇓
Source Data sales	»	Filter Sold this year
		Filter Department N	»	Add / Merge Sales YTD
⇓
Source Data sales	»	Filter Sold last year
		Filter Department N	»	Add / Merge Sales Last Year
⇓
Source Data sales	»	Filter Sold this year	»	Add / Calculate Sales YTD / Total Sales
				Output Rows

The first step in this request, is to ask fd1.data for depid and department name. The following query does that

{
    a: "fd1.data",
    rq: "step1",
    v: {
        table: departments
    },
    qo: {
        depid:true,
        description: true
    }
}

The output so far looks like this

rows: [
    {depid:17, description: "Fresh"},
    {depid:34, description: "Cleaning"}
]

Step 2, we extend the query to get count of items in each department. This is done with a simple tweak to the "qo" field

{
    a: "fd1.data",
    rq: "step2",
    v: {
        table: departments
    },
    qo: {
        depid:true,
        description: true,
        cnt: "products[r_depid=@depid]._count"      « Added
    }
}

Breaking that down into English -

• for each row of data
• open the products table
• filter for the depid in the current row
• and count how many rows are in that filtered-products-table

The output is now

rows: [
    {depid:17, description: "Fresh", cnt: 283},
    {depid:34, description: "Cleaning", cnt: 67}
]

Step 3, Sales data can also be done by extending the qo field

{
    a: "fd1.data",
    rq: "step3",
    v: {
        table: departments
    },
    qo: {
        depid:true,
        description: true,
        cnt: "products[r_depid=@depid]._count",
        salesYTD: "salelines[r_depid=@depid][sold this year]._sum(line.totalprice)",          « Added
        salesLastYear: "salelines[r_depid=@depid][sold last year]._sum(line.totalprice)",      « Added
    }
}

These two new lines are identical to how products worked in step 2, select all salelines, for the time period, only for current department, and sum the totalprice.

The output is now

rows: [
    {depid:17, description: "Fresh", cnt: 283, salesYTD: 35687.88, salesLastYear: 740893.84 },
    {depid:34, description: "Cleaning", cnt: 67, salesYTD: 4619.80, salesLastYear: 82463.30 }
]