Reporting and Analytics for Service

Get Involved. Join the Conversation.


    Simon Kilgarriff
    All vs. Any - Analytics Cookbook Recipe
    Topic posted September 27, 2017 by Simon KilgarriffGold Trophy: 10,000+ Points, last edited September 29, 2017 
    301 Views, 1 Comment
    All vs. Any - Analytics Cookbook Recipe

    "All vs. Any" Reporting

    Using Multiple Joins to the Same Table to Report on Different Metrics within the Table


    Author: Simon Kilgarriff

    Difficulty rating:  (Intermediate)

    Target persona: This scenario could be applied by many target personas based on the report you are trying to create. 


    7 Tables (cloud_results, cloud_search2results, cloud_searches, channel_accounts, incidents,

                        transactions, transactions2)

    5 Inner / 1 Double Joins 

    2 Columns using formulas (1 min(); 1 date_diff() with a nested min() ) 

    4 Filters

    1 Rollup

    Overall description/purpose: 

    The main goal of this report recipe is to demonstrate how to use multiple joins on the same table.

    Anyone wanting to look for several AND actions on the same table could use the technique demonstrated in this report recipe to look at the same table several times to compare data/results.

    NOTE: If reporting can be done without multiple joins to the same table, it should be from a reporting performance perspective.

    Use case being served:

    Sometimes you may wish to build a report and ensure that someone meets several criteria.  For example, someone who has been sent a marketing email or survey AND viewed the email AND clicked a link – so they have performed ALL actions rather than ANY of the actions. 

    Alternatively, perhaps it is a way of recognizing a preferred customer and you could be looking for a contact who has asset type X AND asset type Y AND asset type Z registered to them.  Taking that scenario a step further, a real use case I had once was to show customers who had registered at least 4 asset Xs AND at least one asset Y AND at least one asset Z.  In that case, the same technique was used, but you also need to look at group filters to look for the ‘at least 4’ portion of the use case. Perhaps that is a recipe for another day.

    In this particular example, we are looking for contacts who have been sent a survey, then viewed the email and also clicked the survey link (someone who has been sent an email AND viewed it AND clicked a survey link). The report contains just two columns to illustrate the point.

    For this report, there is an assumption that one or more broadcast or transaction surveys with invitation emails being sent to contacts are set up and being reported on.

    The important reporting technique we are trying to demonstrate: 

    When you try and build a report and one join to a table and use the filters you will find that even though the filters are ANDed together if you select multiple options they turn out to be ORed and you end up with an ANY of the selected options being returned.   

    Trying separate filters for each condition and ANDing them returns no records either.

    I want them all. So, how do you overcome this?  This recipe shows you how to overcome this issue and it can be applied to many different scenarios.  

    Screen shot: 

    Special functionality usage: 

    The output is simple to help show the report functionality. The key point is the joins and filters.

    Note: Joining several times to the same table could dramatically increase the number of rows queried, so you may need to consider various other techniques to make sure the report doesn’t get too big to run.

    Master Chef Approved (a.k.a. Data Validated)? Yes

    Included are the following (in a .ZIP file):

    • Human readable PDF report definition
    • Complete Report Recipe and Report Configuration Details (PDF)
    • XML Report definition 



    • Erica (Leep) Anderson

      We are leaving these report recipe posts open for comments, because we want report chefs to hear how their report recipes helped you. If you found this report recipe helpful, please give Simon's post a thumbs up and leave a comment letting him know you appreciate his help, how this report helped you and your organization, etc.

      If you have a question about this report recipe, please start a new topic in the "Reporting and Analysis" forum board and link to this report recipe in your question. DO NOT post your reporting question here. We will remove any comments asking for help in the comments of this post.