[Reports] Allow displaying products in columns

[audez]

What is the need / problem?

Several of our wholesalers need a specific report with products displayed in columns. They use it to manage the deliveries to customers. For now they create these reports manually, based on the OFN exported reports, but it takes them several hours to half a day every week. They would like to have an estimate of the feature cost.

Which type of users does this problem affect (and how many, if known)?

- Hub Managers (MCFEs) - Producers

Potential solutions that will solve the problem ?[[brainstorming to list feature candidates]

In "Order Cycle Customer Totals", add an option "Display products in: Lines/Columns". "Lines" is selected by default.

When choosing "Columns", the following happens on the dashboard:

• "Generate report" is restricted to "spreadsheet" or "csv" (to keep it simple on our side, because with products in columns the table will extend horizontally so displaying it on screen or in a PDF will be tricky. And the need is only to export a table)
• The option "Display: header row/summary row" is disabled
• The "Columns" choice is disabled (we will see in a second step if needed)

File format:

• There's a header row with the hub name, in bold. If there's several shops, it will be used to distinguish between them.
• If there's several shipping methods, there's a second header row, with the shipping method name. It will be used to distinguish between the shipping methods.
• Next line is "Products", displaying each product name (text in bold). If a product has several variants, cells are merged.
• Next line is "Variants", displaying each product variant (text in bold). If there's only one variant of a product, it displays the packaging - weight/ volume/item.
• Next line is "Price", it will display each variant price
• Next lines are "Customer info" (in bold), displaying in the first cell the customers' name, first name and phone number, then displaying the ordered quantities in each cell At the end of each of the customer lines, after the ordered quantities, a "Total price" column displays the total price for each customer order. After that column, a "Paid?" column displays if the order was paid or not.
  After this column, a "Payment method" column is displayed.
• Next line is "Total", displaying the total quantities ordered for each product, and the total price for all products.

This is repeated for each shipping method, and each shop.

Example of expected file:

export-report.xlsx

Connected wishlist and discovery discussions* [list precedent discussions]

Additional context

[lin-d-hop]

This report would only work for a subset of users with limited product sets. Imagine this report for a hub with 1000 products available. We'll need to understand how to create a report like this, while ensuring that it won't create problems for other users.

I feel like more work is needed to capture the requirements of this report. Do you have more information about the types of users that require this? Can we limit the number of products to be displayed so that the report can't render with 1000 columns? How do they use the report? How does this fit into their workflow?

Thanks :)

[audez]

Thanks for your reply and comments ^^ Users are 3 different wholesalers of biological products (mainly citrus) that they mainly purchase to small local farmers in Spain, and sell in France. They created hubs or shops on CoopCircuits. Their clients order the products quantities they want (for instance: 3kg oranges, 4kg avocados, 2kg mangos). Based on the reports data, they create 2 reports on their own:

• A report with the total of products to prepare (one of their need is also to generate this from the dashboard but I didn't create a wishlist for this yet, it's similar to this wishlist but with the "Order Cycle Total" report). They use this report to pass their orders to their suppliers.

• A report with the detail of what to deliver to each customer (the one described here). The persons in charge of the delivery use this report: they are in the truck with the food and go in each delivery location. They have this report printed, generally in landscape mode and on several A4 sheets. It is much more efficient and quick for them to read the table with the products in columns.

But the issue is that it's very long to prepare this report. The biggest wholesaler has hired someone just for this task. She can spend up to half a day doing this, and she does it every week for 9 distribution point.

For the question of limiting the number of columns, I'm not sure I understand: for now, if a hub has 1000 products available, and each product has been ordered, in the reports it will be displayed on 1000 lines at least (it will actually be more than that because if 2 customers ordered the same product, there will be 2 lines). The request is to display it in columns instead of displaying it in lines. So if a hub has 1000 products available, and all of them have been ordered, the products will be displayed in 1000 columns (and 1000 is max because if 2 customers ordered the same product, it will correspond to the same product column). In terms of computing time, I think it's the same to create 1000 columns or 1000 lines. So I thought that in most cases it would actually save calculation time to create these reports.

The problem I think it could cause is displaying the table on screen, as it would mean to handle a horizontal scrolling - that's why I think we should restrict the report generation to spreadsheet.

Please let me know what you think or if there is any other question/requirements :)

[lin-d-hop]

Ok so the issue of display would be solved by a new user experience of a report that doesn't have an onscreen version?

Would you see it as a viable solution to create this report in N8N first and have it emailed to the user? Or does it need to be in the OFN reports page to be an acceptable solution to you?

[lin-d-hop]

Note computationally no other report behaves in such a way that we don't have a set number of columns of such magnitude. The tax reports do this but only for tax rates which are much more limited. There will be computational implications to having reports generate an n(line items)*n(customers) matrix where neither value for n is known in advance.

Thus this report will cost more than a usual report development as we need to take into consideration a few aspects of design and performance.

[RachL]

Aloha! I don't want to add myself in this conversation as it looks like you two are already handling this brillantly and another cook might create just more headaches.

Just throwing an idea: what about keeping the product in lines and just shifting their pivot tables? I could be wrong but, I don't think it would mess up too much their process.

The reason I'm saying this is that we have a third hub that might be interested in funding this and they work with this reverse process. The example is documented over there, there is a lot on assigning numbers to customer that we could leave behind in a first generic iteration (the pivot table result in in the "DISPATCH" tab): https://community.openfoodnetwork.org/t/hub-can-easily-generate-accurate-packing-slips/1392/62?u=rachel

But if there is a strong reason for product in columns that I've missed, disregard this comment :)

[audez]

Hi! Oh yes that's great, and if they fund it, perfect :D How do you think it would be available in the dashboard, maybe as an additional report?

what about keeping the product in lines and just shifting their pivot tables? I could be wrong but, I don't think it would mess up too much their process.

I agree that a simple transposition could do the job! If we are really cautious when placing columns titles and additional info needed by the wholesalers (variant price, total price for each order, paid and payment method), everything should be fine after the shift. We could also provide them with an estimate to have this option "Products in columns" directly in the dashboard (which actually means building the table with products in column :P but it will be easyyy if we did the reverse)?

if there is a strong reason for product in columns

The main one is that it's very convenient to read customers names in lines, which are usually longer than products names. If they put customer names in columns and don't want them to be trimmed, they can end up with very large cells that stretch the file horizontally. Also, when putting products names in columns, cells can be merged if there are variants, giving more space to read the text properly.

Ok so the issue of display would be solved by a new user experience of a report that doesn't have an onscreen version?

It was just a suggestion / potential solution to simplify the dev :) IMO it's not really a new user experience, they would see the same button "Generate report" but be restricted to tables export. We could also add an info like "This report is only available in xls format". But I understand your point and obviously there are other paths 👍

Thus this report will cost more than a usual report development as we need to take into consideration a few aspects of design and performance.

Yes for sure it's a different logic than the other reports. And it will be the same if we go with Rachel solution, as it's the same algorithm to populate cells except that the table will be built with products in lines instead of columns. It will also come with the same questions in terms of display with the table extending horizontally too.

Would you see it as a viable solution to create this report in N8N first and have it emailed to the user? Or does it need to be in the OFN reports page to be an acceptable solution to you?

Yes if we need to do a first step with N8N, I can dive into this - first I'll have to see with the FR team if this can fit into my schedule. I'll see if it's acceptable for the users but I think it should be. Do you think we could automatically trigger an email at the end of each OC ? Or would they have to go to a GSheets, select the OC in a dropdown list and download the report?