Customer Forum

Reporting on Organisations that do NOT have a particular product in their Contract(s)

Workbooks Support Posted: 2011-09-07 15:26

Using the Group Concatenate functionality is one of the most useful techniques to learn when reporting in Workbooks as it enables you to identify records that do not include certain pieces of information rather than ones that do.

Two other Forum articles that look at this subject may be helpful:

Another example is to identify Organisations which do NOT have a particular Product in their Contract. Here's how:

Create a report based on Contracts.
Add a column for Party's Object Reference (pick Party and use the breadcrumb then select Object Reference). This column will be used to summarise by later.
Add a column for Party (which will display the organisation name).
Add a calculated column to display all the products from all the contracts in one row by using the Group Concatenation function. In the example below, I've Group Concatenated on the Product Code but you could use other product identifiers, such as Description. The separator part is simply to make the information easier to read as it separates each entry using a comma followed by a space but you don't have to use this if you don't want to.

GROUP_CONCAT( order_line_items.product.refcode SEPARATOR ', ')

If you click Refresh preview you'll see that your report shows a single row. To show a different row for each Organisation open the Summarise by tab and choose to summarise by the Party's Object Reference. (You could summarise by the Party but if you have two Organisation records with the same name, this will roll them up into one row.)
Finally, you want to see just the rows that do NOT contain a particular product so open the Criteria tab, click Add calculated criteria and choose the column that contains all the Products. Apply an operator of does not contain and in the text field type the Product Code (or description or whatever field you've Group Concatenated).

You'll now have a report showing only those Organisations which do NOT have a particular product in their Contract.

External Posted: Fri, 09.09.2011 - 10:37

Hi,

Thanks for posting this; I have one question though. I have tried to create a report like this but some of our contracts have blank product codes, which still get eliminated by the 'does not contain' filter. Is there a way to make a criteria for 'does not contain X OR is NULL'? I tried a few different combinations but have not worked it out yet.

Thanks!

Workbooks Support Posted: Fri, 09.09.2011 - 13:14

Hello,

This article explains the topic of Reporting on NULL or Blank values.

Hopefully that helps but let us know if not.

External Posted: Fri, 09.09.2011 - 16:53

Sorry, I didn't explain very well. I have already tried what it suggests there, but it does not seem to work when I combine NULL with an actual value (here product 'X')?

Thanks

Workbooks Support Posted: Mon, 12.09.2011 - 08:14

There's more than one way you could approach this but here's one suggestion, which should work nicely.

Essentially, you want a column that will tell you whether or not the Contract has a Line Item with a Product Code of 'Product X' or where the Product Code is blank. Remember that blank can be NULL or ''. Next, you'll want to see the results concatenated into one row, for which you can use GROUP CONCAT. It's fine to create this formula all in one go, but for ease of explaining what's going on, I'm breaking it out into two columns.

So, first you'll need to create an IF statement that uses OR, that looks something like:

(IF(order_line_items.product.refcode IS NULL OR order_line_items.product.refcode ='' OR order_line_items.product.refcode = 'Product X', 1, 0)

The formula above looks at the Product Code and if that code is either null, blank or is 'Product X', it returns a 1, otherwise it returns a 0. (You can, of course, use terms other than 1 or 0 - just use whatever makes sense to you and remember that if you want to use a text string you need to put apostrophes around it.)

Next, use the above formula within GROUP_CONCAT so that all the results show on one line, as follows:

GROUP_CONCAT(IF( order_line_items.product.refcode IS NULL OR order_line_items.product.refcode ='' OR order_line_items.product.refcode = '123ABC', 1, 0) SEPARATOR ', ')

As described in an earlier post, if you click Refresh Preview you'll now just see one row of data so open the Summarise By tab and use the Object Reference to summarise on.

Finally, apply a calculated criterion. Remember, you just want to see the records for which the Product Code is null, blank or is 'Product X' so apply a calculated criterion to the relevant column (ie, the one that you've Group Concatenated) using the Contains operator and then type in the word you've used to identify the relevant records. In the example I've used, it looks like the screenshot below.