Orange Belt: Report Design Part 1

1. DataSelf Architecture

1. Data warehouse: DataSelf ETL+ extracts the data from the source system(s) into a MS SQL Server data warehouse for a single version of the truth, data consolidation, ease of reporting and fast performance.

2. Analytics: Streamlines the data sources for easier reporting design and distribution.

3. Actionable Insights: Preconfigured views and dashboards provide value on day one, as well as allow users to easily perform ad hoc analysis and create new views.
   

The DataSelf Value Proposition:

2. Sign in to DataSelf Training Site

(self-service training only)

1. Go to https://bi1.dataself.com

2. Sign in with:

   1. User: orange

   2. Pw: DataSelf1!

3. If prompted to Select a Site, select training_orange.

3. Opening the Workbook Editor

Self-service: See the animation below (no audio).

Tableau help link: Sign in and explore projects

1. Depending on where you are on the DataSelf portal:

   1. Click the Explore path link in the top left of the black banner.

      1. Click New button → Workbook.

   2. Or click the Home icon on the left.

      1. Click New button → Workbook.

2. Select a data source to work with. For training, select _Sales_Invoice.
   Tableau help link: Connecting to Data

4. Workbook Editor Main Areas

1. 

2. Main areas of the Workbook editor:

   1. Data source for workbook: (additional data sources can be added later).

   2. Data structures for reporting:

      1. Dimensions (top section): groupings for row and column headers. Ex.: Customer.

      2. Measures (bottom section): Aggregations for Worksheets. Ex.: Qty Sold.

   3. Workbook area.

   4. Sets the Workbook’s Rows and Columns.

   5. Sets the Workbook’s Filters.

   6. Sets the Workbook's Marks.

Tableau Help Link: Edit Tableau Views on the Web

5. Examples of data source schemas

This section is for techies interested in taking a peek at DataSelf' back-end.

The star schemas shown below are examples of Sales data feeding the Workbook Editor’s Data panel. Editing the star schemas requires user licenses for DataSelf Tableau OEM Desktop and MS SQL. However, addition of fields to tables already existing in star schemas only require editing the tables DataSelf ETL+.

Acumatica

NetSuite

Sage 100

Sage 300

Sage X3

6. Drag and Drop, creating a basic view

Self-service: See the animation below (no audio).

How to create a view showing Sales by Customer. (See step 3 on how to create a new workbook)

1. From the left panel, drag the Customer dimension into Rows.

2. From the bottom of the left panel, either double click on the Sales measure to let Tableau auto-add it or drag the Sales measure into the Abc area. The Abc indicates you are displaying a numeric measure value as text rather than as color or size.

3. The Sales by Customer view is done!
   

4. FYI - To undo steps, clear the sheet or start a new sheet:

   1. To undo your steps, use the Undo/Redo icons on the top left of the Workbook editor.

      

   2. Use the Clear Sheet icon to erase all settings on the sheet.

      

   3. To start a new blank sheet, click on the New Worksheet Icon.

      

Hands-on Exercise creating a basic view

1. Click on the New Worksheet tab icon and create a view showing Sales by Salesperson.

   

2. Click on the New Worksheet tab icon and create a view showing Qty Sold & Sales by Item.

   

3. Note that the measure column headers automatically appear when multiple measures are displayed.

   

Tip: How to see a column header for only one measure

As you noticed above, when only one measure is selected, there is no column header (measure name), but when a second measure is added the measure names field is automatically added to the columns or rows, and you see column headers.

You can manually do the same process to see a column for only one measure. There are two methods.

A. Double click on the Measure Values field. This automatically adds the Measure Values as a Text field in the Marks area.

B. Tableau Help Link: The One-Click Trick to Creating Headers for Single-Measure Tables (tableau.com)
Drag your measure to the Abc section of the display area, then drag the Measure Values field also to the Abc section of the display area.

7. Publishing (Saving your work)

Saving/Publishing applies to the the whole workbook. Workbooks are like Excel files, containing one or more sheets (also call tabs or views), which are like Excel tabs/sheets.

1. Click File → Publish As. Or use the Publish icon in the upper right corner.

2. Name the workbook. For this training course, user your name with a number suffix. i.e., “Pat Smith 2”

3. Check Show sheets as tabs (You can change this setting after the fact. You’ll see later what it does).

4. Click Publish.

5. Options to save next times:

   1. File → Publish

   2. Click the Publish icon in the top right

   3. Ctrl+S.

Exiting the Edit Mode

• Click File → Close

• Or click the white X on the top right of the black ribbon.

• If the workbook has been published/saved, you will return to the explorer page.

Returning to the Edit Mode

1. From a worksheet or dashboard tab, click the Edit icon on the top left icon menu.

   

2. From the list of workbooks or views, click the '…' icon → Edit Workbook.

   

8. Filters

Self Service: See the animation below (no audio).

Filters control which records are included or excluded based on the value of a dimension.

Tableau help link: Filter Data from Your Views - Tableau

Modify your previous worksheet showing Sales by Item and add filters for years and items.
Select only the last 2 years.

1. Drag Inv Date to the Columns (top center section). Notice it automatically groups it by Year.

2. Right-click the YEAR(Inv Date) dimension field on the Columns. Select the Show Filter property.

3. In the Year of Inv Date filter on the right, unselect All, then check the boxes for the last two years.

4. Right-click the Item dimension field on the Rows and select Show Filter.

5. In the Item filter on the right panel, unselect Null.

6. Rename the sheet. Right-click Sheet 1 and rename it to Sales by Item by Year.

7. Publish the workbook by clicking File → Publish As or click the Publish icon in the upper right.

   

8. You can also drag dimensions and measures directly from the Data panel to the Filters shelf (center panel). This method often shows more Filter options. But it does not automatically show the filter on the right.

   1. Drag Salesperson to the Filters shelf, then right click to show filter and select/unselect some of its members on the right panel.

Hands-on Exercises (optional)

Duplicate the Sales by Customer worksheet and modify it to show Sales by Item Class for the last two years.

Right-click the sheet tab and select Duplicate

Drag the Item Class dimension on top of the Customer dimension in the Rows.

Filter the Item Class to exclude the NULL Item Class value.

Method 1: Right-click the Item Class dimension in the Rows. Select Show Filters, then unselect Nulls on the filter card.

Method 2: Click on the NULL Item Class Value, select Exclude from the shortcut menu. Note that it creates an Item Class object in the Filters section.

9. Drag and Drop: Multiple Dimensions

Self Service: See the animation below (no audio).

Tableau Help Link: Dimensions and Measures Intro (tableau.com)

Dimensions define how records are sorted and grouped, and how measures are subtotaled (aggregated).
Multiple Dimensions form a grouping hierarchy based on the order they display in the Rows or Columns.
The last Dimension shown is called the lowest level of detail, which is the level of the measure total.

Create a new worksheet showing Sales by Customer, Invoice No, and Item in the last 2 years.

1. Create a new worksheet and insert Customer, Invoice No, and Item in the Rows.

2. Sizing Columns: Drag the row column lines to change widths.
   TIP:
   For Dimensions select the column line within in the body of the rows.
   For Measures select the column line in the header area of the rows.

3. Rearranging Row Field Order: note how subtotals and row totals change.
   On Rows, drag Customer to the middle of the fields (note the red down arrow when dragging and dropping fields - the down arrow indicates where the field will be inserted). Drag Customer to the far right, then back to the left.
   

   

10. Grand Totals and Subtotals

Self Service: See the animation below (no audio).

Modify the Sales by Customer InvoiceNo Item worksheet to add totals and subtotals.

1. Click the Sigma icon → Show Column Grand Totals.
   TIP: Click the Sigma → Column Totals to Top.

2. Click the Sigma icon → Add All Subtotals.

You can also add Row Grand Totals. Adding subtotals works for both columns and rows.

TIP: Add All Subtotals only adds subtotals for existing groups. If you add additional groups after the fact, they will not automatically have subtotals. This can be a handy feature if you only want certain dimensions subtotaled.

11 Sorting

Each Dimension can be sorted within the higher-level Dimension that it is grouped. This is called Nesting.

Tableau Help Link: Sort Data in a Visualization - Tableau

1. Go to the worksheet Sales by Customer in the last 2 years, then Show Row Grand Totals.

2. Row Grand Total Sorting: click the Sort icon on the Grand Total column header. Select Descending.

3. Sorting by Column headers:

   1. Hover the mouse over the column header and click the Sort icon that pops up on the far right.

   2. The first click sorts descending for measures and alphabetical for dimensions. Clicking a second time reverses the sort, the third click resets the sort to data order.

      

12. Dashboards

A Dashboard displays multiple worksheets (dashboard objects) within that workbook in one view.
You can add actions to make the different dashboard objects to make them interactive.

How to create a Dashboard combining a Sales by Salesperson worksheet with a Sales by Customer worksheet as an interactive dashboard.

1. Verify your worksheets for Sales by Salesperson and Sales by Customer have filters set for the last two years.

2. Create a new Dashboard sheet. On any sheet name (tab), right-click → New Dashboard. Or click on the 2nd new sheet icon.

   

3. On the Dashboard Sheet, in the top left, select Size → Fixed Size → Automatic.

4. In the left panel, drag the Sales by Salesperson worksheet into the Add sheets here area.

5. On the left panel, drag the Sales by Customer worksheet into the right of the dashboard area to insert it after the Sales by Rep.

6. Note the dashboard objects auto fit themselves on the sheet due to the Tiled property setting in the lower left corner.

7. Adjust the dashboard object sizes by moving the borders as desired.

8. Note the filters shown on the worksheets were automatically added to the Dashboards, which might result in duplicate filters. Select values on the filters and notice they only affect one object on the dashboard.

9. 

   Making filters affect the whole workbook and removing duplicates:

   1. Select a filter, click the arrow down button on the top right (or left) of the gray box around the filter, and select Apply to Worksheets → All Using This Data Source.

      

      Change the filter selections and see how they affect the whole dashboard.

   2. Remove duplicated filters by selecting them, and then clicking the X on the top right (or left) of the gray box around the filter.

      

13. Drill Down with Dashboards

1. Using dashboard object Action Filters:

   1. Click on the Sales by Salesperson dashboard object.

   2. On the top corner of the gray box around the object, click the Use as Filter icon.

      

      Click on data points within the Sales by Rep object and see that it selects only related data in the other dashboard objects.
      

      

2. Add the Sales by Item worksheet to the dashboard and apply the action filter. Now you can filter by salesperson, customer, or item and see the interactions.

Hands-on Exercise: Drill Down with Dashboards - Customer, Invoice#, Item

1. You already have worksheets for Sales by Customer and Item. Create an additional worksheet showing Sales by Invoice with Inv#, Invoice Date, Item, Qty Sold, Sales.

   1. Invoice Date: Tableau will automatically group the Invoice Date by Year. To convert it to an exact date is two steps.

      1. Once you add it to the Rows, right click and select Exact Date. This changes it to a continuous type of data and shows it as a chart object.

      2. Right click again and select Discrete instead of Continuous. This will show the exact date as text.

         

   2. After adding the Invoice Date as an exact date, add the Sales Measures.

   3. Filter all 3 worksheets to select only Invoice Year 2022.
      

2. Create a dashboard displaying Sales by Customer, Sales by Invoice#, and Sales by Item worksheets.
   Make one of the Year of Invoice Date Filters apply to all worksheets using this Data Source and remove the extra filters.

   

3. Activate the Use as Filter action filters for all three dashboard objects. Select one customer and see it shows only the invoices and items for that customer. Select one invoice and see it shows only the items for that invoice.

   

14. Drill Down with Hierarchies

Drilling down is a function of Tableau Hierarchies. There are three types of hierarchies: Tableau built-in, DataSelf built-in, and custom workbook hierarchies. Built-in hierarchies cannot be modified in the Web App. All date type fields are subject to the Tableau built-in hierarchy of Year-Quarter-Month-Day. In the example below, the ShipTo Country is a DataSelf built-in hierarchy.

Drill Down with Built-in Hierarchies

1. Create a report showing Sales by ShipTo Country by Year, with Inv Date Year column filtered for 2022.

   

2. Using the '+' icon on the Columns and Rows or column headers, drill down to the next hierarchy levels:

   1. Country → State → City (DataSelf built-in hierarchy).

      

   2. Year → Quarter → Month → Day (Tableau built-in hierarchy).

   3. Watch the video (no audio) for all variations.

      

Drill Down with Custom Hierarchies

• Create a custom hierarchy to drill down from Customer → Inv No → Inv Date → Item.

• On the left panel, right-click Customer → Hierarchy → Create Hierarchy → name it Customer.Invoice.Item → Ok.

• Add the Invoice No, Inv Date, and Item fields to the hierarchy
  Method 1: Drag the fields right below the Customer dimension inside of the new hierarchy.
  Method 2: Right click on the field, select Hierarchy → Add to Hierarchy → Customer.Invoice.Item

• Create a view showing Sales by Customer.

• Drill down all the way to Item.

• Add All Subtotals, and Column Totals to Top.

TIP 1: A dimension cannot be in more than one hierarchy. ShipTo State already belongs to a DataSelf built-in hierarchy created in the Tableau data source (it can only be changed using Tableau Desktop). To work around such limitations, you can right-click a dimension → Duplicate. This will create a copy of that dimension and rename it. You can use the duplicated field in custom hierarchies.

TIP 2: If a date field is inserted into a hierarchy, it no longer follows the default Tableau hierarchy of Year→ Qtr→ Month→ Day. You can duplicate the date field and use the duplicate in the hierarchy so the original date field still follows the default Tableau date hierarchy

Hands-on Exercise: Custom Hierarchies
Create a hierarchy to drill down from ShipTo State → Salesperson → Customer.

Next: Orange Belt: Report Design Part 2.