Placeholder

Customer Forum

Creating a Report to show the revenue per Order for specific Products

Workbooks Support Posted: 2013-05-14 09:13

We were recently asked how to produce a report which shows the revenue generated per Order by specific products. Please see below how to create this report:

  • Create a new blank report, based on Customer Orders. Add a column for Object Reference, plus any other columns that may be of interest.
  • Add a calculated column, called 'Products', that uses the following formula:

     

'GROUP_CONCAT( order_line_items.product_refcode SEPARATOR ', ' )'

 

This formula will show all the Products selected within a single Order separated by a comma.

 

  •  Create another calculated column, called 'Product Specific Revenue', that uses the following formula:

     

'SUM(IF(  order_line_items.product_refcode LIKE 'VOiP%' OR order_line_items.product_refcode LIKE 'SUPPORT%' ,  order_line_items.document_currency_net_value, 0))'

 

In non-reporting language, the formula says 'If a product is like 'VOIP%' OR 'SUPPORT%' then give the net value of that line, otherwise 0. Then give the sum of this'. Stating the product is like 'VOIP' or 'SUPPORT' is specific to this example and you would replace this with your own product codes.

  •  Add a column which shows the net value of the Order.
  • Finally you need to summarise by 'Object Reference'.

You should end up with a report which looks something like the one below (click to enlarge).

Here you can see all Orders, the products which are included as Line Items and their combined value.
Workbooks Support Posted: Mon, 12.08.2013 - 09:34

In next release of Workbooks, due out in late summer, you can build the same report by undertaking the following steps:

  • Create a new blank report, based on Customer Orders. Give the report a name and select the 'Details' tab.
  • Add a column for Object Reference, Line Items > Product, Line Items > 'Net (document currency)' & call this 'Product Revenue', Net (document currency) & call this 'Total Revenue'.

The Details view will now show all Opportunities, each line item and the Product selected, the Net Value of the line item and the Net Value of the Opportunity as a whole. This can built upon using a Summary view to show the specific information we wish to view. To do this:

  • Select 'Add Summary View' and 'Add grouping column'. Group by 'Object Reference'.
  • Add a value column and select 'Calculated Column'. Enter the following formula:

'GROUP_CONCAT( PARENT('Product') SEPARATOR ', ')'

 

This formula will show all the Products selected within a single Order separated by a comma. Note, the part highlighted in yellow is simply the 'Product' column from the details view which has been selected using the Formula builder.

  • Add another value column and select 'Calculated Column'. Enter the following formula:

 

'SUM(IF( PARENT('Product') LIKE 'VOiP%' OR  PARENT('Product') LIKE 'SUPPORT%', PARENT("Product Revenue"), 0))'

 

In non-reporting language, the formula says 'If a product is like 'VOIP%' OR 'SUPPORT%' then give the net value of that line, otherwise 0. Then give the sum of this. Stating the product is like 'VOIP' or 'SUPPORT' is specific to this example and you would replace this with your own product codes.

  • Add a final Value Column and select the column 'Total Revenue'.

You should end up with a report which looks something like the one below (click to enlarge).