Woocommerce-admin: QUERY: How to add custom column to “Summary” at bottom of report

[EdithAllison]

Hi,

I’m building a plugin extension to add my own columns to the “products” report. Data is displaying fine in the table, but I’m stuck at how to add a summary at bottom of table.

So far I’ve got the following (based on the extension example, using the “woocommerce_admin_report_table” filter):

    const newSummary = [
        ...reportTableData.summary,
        {
            label: 'Cost',
            value: '100', // ?
        },          
    ]; 

     reportTableData.summary = newSummary; 

“Cost” is a field I’m getting from the DB, it shows correctly in the table column, but in summary I’m getting an “unknown” error. For my test, I’ve therefore entered a default “100” as value.

Query: How can I add the actual sum total of “Cost” to the table? Is there an additional SQL query I need to add; or as the data is already present in the table is there an easy way to summarise the total from the existing SQL query?

Many thanks!

[ahmer110]

I am unable to use extension plugin can anybody help? i get this error
Cannot use import statement outside a module

[rrennick]

How can I add the actual sum total of “Cost” to the table?

@EdithAllison The reports that have a summary row have two queries report_name_interval and report_name_total. The total query is used to retrieve the summary row.

@ahmer110 You need to set up your development environment as described in our readme https://github.com/woocommerce/woocommerce-admin.

[ahmer110]

Actually i never used npm before this. When i download the zip archive i get the plugin without development files.
I installed the git to clone the repository and i got permission denied error. spent many hours how to fix this but no luck. :-(

[rrennick]

@ahmer110 You need to fork a report that you don't have permissions on https://help.github.com/en/github/getting-started-with-github/fork-a-repo. Once you fork it then you can clone your fork locally.

[EdithAllison]

Got it, thank you! The SQL part was okay, I had:

add_filter( 'woocommerce_analytics_clauses_join_products_stats_total', 'add_join_subquery' );
add_filter( 'woocommerce_analytics_clauses_select_products_stats_total', 'add_select_subquery' );

The missing part was to access the totals correctly in JS with
reportTableData.totals.cost

Totals are now working :)

[ahmer110]

I was able to setup and build example extension. However i just want to show SKU in orders report table, but i see there is no way to add SKU in extended attributes info for orders as we can see SKU on products report.

[rrennick]

The missing part was to access the totals correctly in JS with
reportTableData.totals.cost

@EdithAllison It looks like we didn't implement the filters there https://github.com/woocommerce/woocommerce-admin/blob/master/client/analytics/report/orders/table.js#L247 (orders). I'll add this to feature requests.

[EdithAllison]

Hi Ron, sorry, not sure what filters you requested? Just to confirm, my code is working now, I just got the JS code wrong initially. Through the reportTableData.totals object I can now correctly access my totals and display them.

A simplified example of my code is below where "cost" is an amount I'm getting from a custom field:

    const newSummary = [
        ...reportTableData.summary,
        {
            label: 'Cost',
            value: 'reportTableData.totals.cost,
        }    
    ]; 

    reportTableData.summary = newSummary; 

and the amount is correctly called in from the SQL query which I've added in my PHP code through

add_filter( 'woocommerce_analytics_clauses_join_products_stats_total', 'add_join_subquery' );
add_filter( 'woocommerce_analytics_clauses_select_products_stats_total', 'add_select_subquery' );
add_filter( 'woocommerce_analytics_clauses_join_orders_stats_total', 'add_orders_join_subquery' )
add_filter( 'woocommerce_analytics_clauses_select_orders_stats_total', 'add_orders_select_subquery' );

The totals are now fine & showing correctly at the bottom of the table. This is tested in both the PRODUCTS and the ORDERS report. I'm not aware of anything missing.

What I still have issues with is my separate github issue regarding CSV export which I can't get to work,https://github.com/woocommerce/woocommerce-admin/issues/4235

Many thanks!

[timmyc]

@EdithAllison sounds like this has been resolved so am going to close out this issue.