Customer Forum

Pulling through first line item start date and last line item end date

External Posted: 2012-09-04 08:38

When there are multiple line items in a customer quotation or an opportunity, how can I bring through just the first line item start date and the last line item end date ?

i.e. we may have 3 line items on a quotation, which to us added together constitute a campaign, but we'd like to report on the start and end dates of the whole campaign?

Thanks!

Sara

Workbooks Support Posted: Thu, 15.08.2013 - 11:30

It is possible to show the start date of the first line item and the end date of the last line items by taking the following steps:

Create a new blank report (based on Quotations in this example).
Add a column for Object Reference.
Add a Calculated column which uses the following formula:

'CONCAT(DATE_FORMAT(MIN( order_line_items.start_date), '%d/%m/%y'), ' to ', DATE_FORMAT(MAX( order_line_items.end_date), '%d/%m/%y'))'

This formula finds the earliest start date and the latest end date for the line items of a record, and concatenates them into a single string. The Date Format function is used to format the date into a form which is easier to read.

Summarise by 'Object Reference'.

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

Workbooks Support Posted: Thu, 15.08.2013 - 11:43

In the 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 Quotations. Within the Details view add a column for Object Reference, Line Items Start Date & Line Items End Date.
Select 'Add Summary View' and select 'Add grouping column'. Group by 'Object Reference'.
Add a Calculated column which uses the following formula:

'CONCAT(DATE_FORMAT(MIN( PARENT('Start Date')), '%d/%m/%y'), ' to ', DATE_FORMAT(MAX( PARENT('End Date')), '%d/%m/%y'))'

The report should look the same as the one shown in the post above.