Before You Begin

In this tutorial, you learn how to create a date part parameter that enables interacting with the workbook to select a date part that filters the dataset.

Background

Date parts are the year, month, or year-month portion of a date value such as ship date, order date, and other date columns. You might want to filter the data to find the sales related to ship dates in a specific year and sales related to orders in that same time period.

This tutorial shows sales by order date and sales by ship date to demonstrate how to pass a value between separate visualizations with different date columns. You create expression filters in the visualizations, and enable users to select the date part parameter in a dashboard filter control.

What Do You Need?

Create a Dataset

In this section, you add the sample_order_lines.xlsx file to Oracle Analytics to create a dataset table.

  1. Sign in to Oracle Analytics.
  2. On the Home page, click Create, and then click Dataset.
  3. In Create Dataset, click Drop data file here or click to browse, select the sample_order_lines.xlsx file, and then click Open.
  4. In Create Dataset Table from sample_order_lines.xlsx, click OK. Click Save Save icon.
  5. In Save Dataset As, enter sample_order_lines, and then click OK.


    Description of sample_order_lines.png follows
    Description of the illustration sample_order_lines.png

Create Visualizations

In this section, you create two visualizations. Each visualization uses Sales with different date columns, Ship Date and Order Date.

  1. Click Create Workbook.
  2. In the Data pane, hold down the Ctrl key, select Sales, expand Ship Date and select Month, and then drag the data elements to the canvas.


    Oracle Analytics automatically creates a line visualization of the data.

    Description of sales_by_shipmonth.png follows
    Description of the illustration sales_by_shipmonth.png
  3. In the Data pane, hold down the Ctrl key, select Sales, expand Order Date and select Month.
  4. Drag the data elements to the canvas and then release them next to or above the Sales by Ship Date visualization.


    Description of sales_by_orderdate.png follows
    Description of the illustration sales_by_orderdate.png

Create a Date Part Parameter

In this section, you create a parameter for the date part. This parameter doesn't accept multiple values or use an alias.

  1. In the workbook, click Parameters parameters icon.
  2. In the Parameters pane, click Add Parameter Add icon.
  3. In Create Parameter, enter Year in Name.
  4. In the Data Type row use the default value, Text.
  5. From the Available Values list, select Value.
  6. In the Value field, enter 2013. Click Add Value Add Value icon. In the Value field enter 2014.
  7. Click Add Value three more times to add three fields. In those fields, enter 2015, 2016, and 2017 (one value per field).
  8. In the Initial Value field, enter 2014.


    Description of create_param.png follows
    Description of the illustration create_param.png
  9. Click OK.


    The Year parameter appears in the Parameters pane.

    Description of year_param.png follows
    Description of the illustration year_param.png

Create Filter Expressions

In this section, you create filter expressions for the Order Date and Ship Date visualizations. The expression uses the CAST function to extract year from the date and compare the value with the Year parameter.

  1. In the workbook filter bar, click Add Filter Add filter icon and select Add Expression Filter.
  2. In Expression Filter, enter Ship in Label.
  3. In the Expression field, enter cas and select CAST.
  4. In the parentheses, enter Yea to replace expression, and select Year. In dimension, enter Shi and select Ship Date.
  5. After AS, enter varchar(4), and then enter equals =.
  6. Click Parameters parameters icon. Drag the Year parameter to the expression after the equals sign. In default value, enter '2014'. Click Validate. Click Apply.


    Your expression should like similar to CAST(YEAR(Ship Date) AS varchar(4)) = @parameter("Year")('2014').

    The Ship expression filter updates both visualizations with parameter's initial value year of 2014.

    Description of ship_param_filter.png follows
    Description of the illustration ship_param_filter.png
  7. In the workbook filter bar, click Add Filter Add filter icon and select Add Expression Filter.
  8. In Expression Filter, enter Order in Label. In the Expression field, enter cas and select CAST.
  9. In the parentheses, enter Yea to replace expression, and select Year. In dimension, enter Ord and select Order Date.
  10. After AS, enter varchar(4)), and then enter equals =. Click Parameters parameters icon. Drag the Year parameter to the expression after the equals sign. In default value, enter '2014'. Click Validate. Click Apply.


    Your expression should like similar to CAST(YEAR(Order Date) AS varchar(4)) = @parameter("Year")('2014').

Add the Parameter as a Dashboard Filter Control

  1. Click Visualizations Visualizations tab icon.
  2. Under Dashboard Control, drag Dashboard Filters dashboard filter control icon to the canvas.
  3. Click Parameters. Drag the Year parameter to the Dashboard Filters visualization.
  4. In the Dashboard Filters control, click Year and select 2016.


    Description of param_db_filter.png follows
    Description of the illustration param_db_filter.png
  5. Click Save. In Save Workbook, enter a Name and click Save.
  6. Click Go back Go back icon.
  7. On the Home page, open the workbook. Select a Year to update the visualizations.


    In the consumer view, the date part filter uses the initial value of 2014.

    Description of consumer_view.png follows
    Description of the illustration consumer_view.png

Learn More