Training by Example: Use Case — Changes Report

This is the fourth use case in the Training by Example series. Review it after finishing the Training by Example: Use Case — Catalog,  Training by Example: Use Case — Bursting and Training by Example: Use Case — Exceptions Report.

In this example, a business unit that oversees the company's Wine Sales requested to receive Reports displaying changes among sellers. Using the information from Top Sellers Report, created in the Training by Example: Exceptions Report play, the new Report has to contain:

  • Products that appear in the Top Sellers Report for the first time in a current month
  • Products that were on the prior Top Sellers Report, but have dropped off this month
  • Products which have increased in value this month

Use the example workflow outlined below to:

  • Learn how to use a CSV Dataset as an SQL Data Source
  • Understand how to apply an SQL fetch statement to a Dataset
  • Discover how to create a Custom View and build a Changes Report

Make sure you have completed all the following steps before beginning this Use Case:

  • Created an SQL Data Source
  • Created a Category
  • Added a User Map
  • Created a Folder
  • Built a Burst

Data Collection

In this section you will learn how to create a Data Collection Trigger that collects data once a month.

Besides that, you will find out how to apply an SQL Statement to a Dataset. The SQL Statement can only apply to Datasets that either were created from the SQL Data Source or from the other Dataset, used as an SQL Data Source. Choose the option that is most suitable for you.

For this Use Case you will need the Trigger that updated data once a month.

  1. Access Admin > Collection & Storage > Data Collection Triggers
  2. [+ New Data Collection Trigger]
  3. Name: Name the Trigger "<your name> Monthly Refresh". In this use case "Student ##" is used instead of a name
  4. [Save]
  1. Open Configuration tab
  2. Collect data every: Select "Calendar Month" option
  3. [Save]

The CSV Dataset, created in the Training by Example: Exceptions Report doesn't fit for this use case, so you need to create a new one.

Download the CSV file. It contains the same data that is stored in our SQL Data Source. Use this alternative if you're unable to connect to the cloud-based Data Source.

  1. Access Content > Datasets
  2. Scroll to the bottom and [+ New Dataset]
  1. Measured: Select "Monthly" option
  2. Name: Name the Dataset "<your name> Wine Sales Monthly (CSV)". In this use case "Student ##" is used instead of a name
  3. Category: Choose the "<your name> Wine Sales Dashboard" Category
  1. Open the Data tab
  2. Data Source: Select the "CSV or Excel File" option
  3. [Load data]
  4. Month: Choose a random date in the past
  5. [Select]
  6. [Browse] and choose the downloaded CSV file
  7. [Load]
  8. [Enable & View]
  1. Access + New > Dataset > Create New
  2. Measured: Select "Monthly" option
  3. Name: Name the Dataset "<your name> Monthly Wine Sales Top Sellers". In this use case "Student ##" is used instead of a name
  4. Category: Choose the "<your name> Wine Sales Dashboards" Category

Completing the Data tab slightly varies depending on how the Data Source was created previously (in Training by Example: Exceptions Report). Perform one of them according to the Data Source method used. You need to do only one variant, not both.

  1. Open the Data tab
  2. Data collection trigger: Select the "<your name> Monthly Refresh" Data Collection Trigger
  3. SQL statement: Enter the code below
  4. [Validate]
  5. Measurement Time: Select the 1st day of the previous month
  6. [Select]

Here's the code for the SQL Statement field:

SELECT date_format(calendar_date,'%Y-%m-01') calendar_date, country, channel, product_category, product_subcategory, product_name,
SUM( total_sales_amount) AS total_sales_amount,
SUM(total_gross_profit) AS total_gross_profit,
SUM(total_unit_count) AS total_unit_count,
SUM(total_cost) AS total_cost
FROM daily_sales_summary
WHERE calendar_date =:measurement_time
GROUP BY 1,2,3,4,5,6
HAVING (`total_sales_amount`>= '10000' OR `total_gross_profit`>= '5000' OR `total_cost` <= 0.5*`total_sales_amount`)
  1. Open the Data tab
  2. Data Source: Select the "Datasets (SQL) - Default MySQL" option
  3. Data collection trigger: Choose the "<your name> Monthly Refresh" Trigger
  4. Click Source of Reports button
  5. Available Datasets: Select the "<your name> Wine Sales Dataset" CSV Dataset you created
  6. SQL statement: Enter the code below
  7. In the code, replace the table name after  the SQL "FROM" clause with the Table name of the CSV Dataset
  8. [Validate]
  9. Measurement Time: Select a date in the past
  10. [Select]

Here's the code for the SQL Statement field:

SELECT date_format(calendar_date,'%Y-%m-01') calendar_date, country, channel, product_category, product_subcategory, product_name,
SUM( total_sales_amount) AS total_sales_amount,
SUM(total_gross_profit) AS total_gross_profit,
SUM(total_unit_count) AS total_unit_count,
SUM(total_cost) AS total_cost
FROM dataset_18259
WHERE date_format(calendar_date, '%Y-%m-01') = date(:measurement_time)
GROUP BY 1,2,3,4,5,6
HAVING (`total_sales_amount`>= '1000000' OR `total_gross_profit`>= '500000' OR `total_cost` <= 0.5*`total_sales_amount`)
  1. Snapshot Dataset: Select "Yes"
  2. Can historical instances be backfilled: Choose "Yes"
  3. Include current Month: Select "No"
  4. [Enable & View]
  5. Do you want to update historical instances of Dataset: Choose "Yes"
  6. Update Dataset Instances: Select "from specified date"
  7. Update all Dataset Instances Since: Choose the date that will cover last 12 month
  8. [Update]

Datasets are designed to serve as a source of data for new Elements such as Metrics and Reports. By creating a Dataset View you can save customized filters and rules which generate specific valuable data. The View can be used to create a table of data displayed in an Internal Report.

In this section you will learn how to create a custom View that highlights changes appearing between the data collections in the Dataset Viewer and build a Report from that View.

  1. Access Content > Datasets
  2. Click the "<your name> Monthly Wine Sales Top Sellers" Dataset name link

Click [View].

  1. Select the following text fields:
    • country
    • channel
    • product_category
    • product_name
  2. Select the following numeric & date fields:
    • Total Sales Amount: Select "Sum" option in the Aggregation column
    • Total Gross Profit: Select "Sum" option in the Aggregation column
    • Total Cost: Select "Sum" option in the Aggregation column
  3. [Apply Changes]
  1. Select Last Two Instances radio button
  2. [+ Rule]
  3. Define the filter Rules:
    • "Sum of total_sales_amount" +  "is greater than or equal to" + "Prior Sum of total_sales_amount" + "by percent" + "5"
    • For more details refer to the Create a Dataset View article
  4. [Apply Changes]
  1. [Save As View]
  2. Name: Name the View "<your name> Top Movers & Shakers". In this use case "Student ##" is used instead of a name
  3. [Save]

Reports display the custom View of the Dataset. In the Dataset Viewer you created a custom View that surfaces data changes. In this example, we'll build an Element tile for a Report. That Report can be shown on the Homepage and added to Bursts. For more information, refer to Dataset Reports Overview.

In this section you will learn to build Changes Reports.

  1. Access Content > Datasets
  2. Open the Views tab
  3. Find the "<your name> Top Movers & Shakers" View you've created and click its Active Name link

Click [Actions] and select "Build Report".

The Report Editor opens in a separate tab.

Click [Edit Filters].

Add Report Filters
  1. Drag the "Change Type" and "product_category" columns to the Filters section
  2. [Apply]
  1. Click Edit
  2. Click the gear icon in the "country" row
  3. Display Name: Capitalize the column name "country" to "Country"
  4. [Done]
  5. Replace the names of other columns:
    • "channel" to "Channel"
    • "product_category" to "Product Category"
    • "product_name" to "Product"
    • "Sum of total_sales_amount" to "Total Sales"
    • "Sum of total_gross_profit" to "Total Gross Profit"
    • "Prior Sum of total_sales_amount" to "Prior Sales"
    • "Sum of total_cost" to "Total Cost"
  6. [Apply]
  1. Deactivate checkboxes near the "Change Type", "Prior Sum of total_gross_profit" and "Prior Sum of total_cost" columns
  2. [Apply]
  1. Show on Tile: Select "row counts" option
  2. When Report has: Enter labels
    • "Movers" into "no rows"
    • "Mover" into "1 row"
    • "Movers" into "multiple rows"
  3. [Apply]

Access Control

In this section you restrict the Users access to the Dataset data by applying a User Map to it. This way you control what a specific user sees when opening both the Dataset and any reports. For additional information refer to Create a User Map article.

  1. Access Content > Datasets
  2. Find the "<your name> Monthly Wine Sales Dataset" Dataset and click the Active Name link
  1. Open the Access tab
  2. Access mode: Select the "only specified rows" option
  3. User Map: Choose the "<your name> User Map" User Map you created previously
  4. In the Dataset drop down menu choose "channel" option and in the User Map drop down menu select the "channel" option
  5. [Update Data]

In this section you will add the Report to the Burst so the sent email includes it.

Drag-and-drop the "<your name> Top Movers & Shakers" External Report tile to the "<your name> VP Wine Sales" Folder you created.

You won't be able to see data on the tile if your Username is not included in the User Map applied to the Dataset. You will still be able to see data in the Dataset and Report Editors.

The following steps are available to Admins only.

Click the gear icon in the row of a "<your name> VP Wine Sales" Folder.

Click the "<your name> VP Wine Sales" Burst Active name link.

  1. [Send Now]
  2. Select the "Send me" option
  3. Burst for: Choose one of the Users that are in the User Map to check what they see in the email
  4. [Send]

Go to the mail box and check if the Notification reached the recipient.

Congratulations! You finished the workflow!

You are done. Great job!