Training by Example: Use Case — Exceptions Report

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

In this example, a request has been received from a business unit that oversees the company's Wine Sales. Users require a Top Sellers Report added as both a catalog tile and distributed via a Burst. The Report has to be visually compelling and detail the following exceptions if they exist:

  • List any products that report greater than 5M in sales, or
  • Products that report greater than 2M in profits, or
  • Products that report total costs less than total sales by 50% over the last 90 days.

Use the example workflow outlined below to:

  • Learn how to create an SQL Data Source or 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 an Exceptions Report

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

  • Created a Category
  • Added a Data Collection Trigger
  • Built an External Report
  • Created a Folder
  • Built a Burst

Data Collection

In this Use Case you will learn 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.

After setting a Data Source, you will also create a Dataset and build an SQL Fetch Statement in it.

Follow the steps described in the Create an SQL Data Source section if your company's security policy allows you to connect to an external cloud-based Data Source. Use the Create a CSV Dataset alternative if you're unable to connect to the cloud-based Data Source.  You need only complete one or the other, not both.

For the purposes of training there is a publicly accessible SQL Data Source.

  1. Access Admin > Collection & Storage > Data Sources
  2. Scroll the to the bottom and [+ New Data Source]
  3. In the Select the Type of New Data Source screen select "SQL"
  4. [Next Step]
  1. Name: Provide a descriptive name for the Data Source
  2. Auth Type: Select "Password" option
  3. Data Source Username: Use the "mi_read" username
  4. Data Source Password: Insert the "zxz6rxe0YXN1nfa!fzk" password
  5. Host name: Enter "trainingdb.metricinsights.com"  as host name
  6. Database name: Insert "training"
  7. JDBC string: Select the "MySQL Connector/J" option
  8. Collect Table and Column Metadata: Select "Yes"
  9. [Save]

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 as described above.

  1. Access Content > Datasets
  2. Scroll to the bottom and [+ New Dataset]
  1. Measured: Select "Daily" option
  2. Name: Name the Dataset "<your name> Wine Sales Dataset (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. Day: 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 "Daily"
  3. Name: Name the Dataset "<your name> Wine Sales Dataset". 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 in Step 1. Perform one of them according to the Data Source method used above.

  1. Open the Data tab
  2. Data collection trigger: Select the "<your name> Daily at 7am" Data Collection Trigger
  3. SQL statement: Enter the code below
  4. [Validate]
  5. Measurement Time: Select a date in the past
  6. [Select]
  7. [Enable & View]

Here's the code for the SQL Statement field:

SELECT date_format(calendar_date,'%Y-%m-01') date,
country,
channel,
product_category,
product_subcategory,
product_name,
SUM(total_sales_amount) AS total_sales_ammount,
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 date_format(calendar_date, '%Y-%m-01') = date(:measurement_time)
GROUP BY 1,2,3,4,5,6
HAVING (total_sales_ammount >= 1000000 OR 
total_gross_profit >= 500000 OR total_cost  <= 0.5* total_sales_ammount)
  1. Data Source: Select the "Datasets (SQL) - Default MySQL" option
  2. Data collection trigger: Choose the "<your name> Daily at 7am" Trigger
  3. Click Source of Reports button
  4. Available Datasets: Select the CSV Dataset you created
  5. SQL statement: Enter the code below
  6. In the code, replace the table name after  the SQL "FROM" clause with the Table name of the CSV Dataset
  7. [Validate]
  8. Measurement Time: Select a date in the past
  9. [Select]
  10. [Enable & View]

Here's the code for the SQL Statement field:

SELECT date_format(calendar_date,'%Y-%m-01') date,
country,
channel,
product_category,
product_subcategory,
product_name,
SUM(total_sales_amount) AS total_sales_ammount,
SUM(total_gross_profit) AS total_gross_profit,
SUM(total_unit_count) AS total_unit_count,
SUM(total_cost) AS total_cost
FROM dataset_18239
WHERE date_format(calendar_date, '%Y-%m-01') = date(:measurement_time)
GROUP BY 1,2,3,4,5,6
HAVING (total_sales_ammount >= 1000000 OR 
total_gross_profit >= 500000 OR total_cost  <= 0.5* total_sales_ammount)

Content Creation

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. Besides the Table View you can also add Charts, Graphs, and other visual elements to the Report. For more information, see the Dataset Reports section.

In this section you will learn how to create a custom View in the Dataset Viewer and build a Report from that View.

  1. Open the Dataset Viewer and select "All Data" view
  2. Select the following text fields:
    • Country
    • Channel
    • Product_category
    • Product_name
  3. 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
  4. [Apply Changes]
  1. Switch the toggle to "OR"
  2. [+ Rule]
  3. Define the filter Rules:
    • "Sum of total_sales_amount" +  "is greater than or equal to" + "a value" + "5000000"
    • "Sum of total_gross_profit" + "is greater than or equal to" + "a value" + "2000000"
    • "Sum of total_cost" + "is less than or equal to" + "Sum of total_sales_amount" + "by a percent" + "50"
    • 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 Sellers". In this use case "Student ##" is used instead of a name
  3. [Save]

Reports are live connections to the custom View of the Dataset. In the Dataset Viewer you created a custom View that surfaces relevant data exceptions which can be used to create Elements in the catalog.  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 Reports.

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

Click [Actions] and select "Build Report".

The Report Editor opens in a separate tab.

  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"
    • "Sum of total_cost" to "Total Cost"
  6. [Apply]
  1. [+ Rule]
  2. Set the Conditional Formatting Rule:
    • "Sum of total_sales_amount" +  "is greater than or equal to" + "a value" + "5000000"
  3. Apply green bold font to "Sum of total_sales_amount"
  4. [Done]
  5. [Apply]

Repeat for two more rules:

  • "Sum of total_gross_profit" + "is greater than or equal to" + "a value" + "2000000"
    • Apply green bold font to "Sum of total_gross_profit"
  • "Sum of total_cost" + "is less than or equal to" + "Sum of total_sales_amount" + "by a percent" + "50"
    • Apply green bold font to "Sum of total_cost"

For more details refer to Dataset Reports Overview article.

Drag the Line/Bar/Area Chart to the Drop Components Here section.

  1. X-Axis: Select "product_name" option
  2. Select the "Show as bar" icon in the measure row
  3. Choose "Sum of total_sales_amount"
  4. [+ Measure]
  5. Repeat for "Sum of total_gross_profit" and "Sum of total_cost"
  6. X-Axis Title: Type "Product Name"
  7. Y-Axis Title: Insert "Values"
  8. [Apply]

For more details refer to the Creating Charts in Dataset Reports article.

Drag the Text Block to the Drop Components Here section.

  1. Add a "Total value of Top Sellers" text to the field
  2. Mark font as bold and align it to center
  3. Open Variables > Manage Variables
  4. Name the Text Block "Total Top Sellers"
Define the Variables
  1. [+ Variable]
  2. Variables: Choose the "Sum of total_sales_amount"
  3. Height: Enter the "80" height
  4. [Apply]
  1. Place the cursor under the typed text
  2. Click Variables and choose the "Sum of Sum of total_sales_ammount"
  3. Activate the Show on Tile checkbox
  1. Drag the External Report icon to the Drop Components Here section
  2. External Visualization: Select the "<your name> Wine Sales Dashboard" External Report
  3. Choose "Link Visualization to Data table" option
  4. In the Report Columns select the "channel" option and in the Filters choose "Channel" option
  5. [+ Add]
  6. In the Report Columns select the "product_category" option and in the Filters choose "Product Category" option
  7. Activate the Show in Viewer checkbox
  8. Maximum number of instances to display: Insert "1"
  9. [Apply]

Access Control

In this section you restrict the User's 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 (Apply Row Level Security) article.

  1. Access Content > Datasets
  2. Find the "<your name> Wine Sales Top Sellers" 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
  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]

Distribution of Content

In this section you will add the Report to the Burst and manage the distribution so the sent email will include attached files.

Drag-and-drop the "<your name> Top Sellers" 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.

  1. Access Content > Elements
  2. Find the "<your name> Top Sellers" External Report and click its Active Name link
  1. Open the Distribution tab
  2. Attach to Email: Select the "PDF" option
    • The report can be attached to the email as a PDF, XLSX, PPT or CSV file. You can choose the format that best suits your needs
  3. [Save]

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.

  1. Access Admin > Users & Groups
  2. Open the Groups tab
  3. Find the "<your name> User Group" Group and click its Active Name link

As an Admin, you can log into the accounts of created Users. Try to log in as a User to confirm that your settings work.

  1. Open Members tab
  2. Choose one of the Users added to the Group and click the "Log in as this user" icon in its row

Go to the Homepage and check if the Report tile is displayed there.

Congratulations! You finished the workflow!

You are done. Great job!