Before You Begin

This tutorial shows you how to create a parameter from derived date elements in a dataset in Oracle Analytics.

Background

When you create a workbook, Oracle Analytics automatically creates derived date elements from Date and DateTime data type columns. You can use these data elements in your visualizations and parameters.

What Do You Need?

Create a Dataset

In this section, you create a dataset and examine the date data elements.

  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.
  4. In Create Dataset Table from sample_order_lines2023.xlsx, click OK.
  5. Click Save Save icon. In Save Dataset As, enter sample_order_lines2023, and then click OK.


    Description of sample_order_lines2023.png follows
    Description of the illustration sample_order_lines2023.png
  6. Click the sample_order_lines2023 tab. Click Metadata Metadata icon.


    The metadata view of the dataset shows the data type and sample data for the dataset. Ship Date is a Date data type and Order Date is a DateTime data type.

    Description of metadata_view.png follows
    Description of the illustration metadata_view.png

Create a Workbook

In this section, you create a visualization and add a dashboard filter control to enable user interaction with the data.

  1. Click Create Workbook. Close the Auto Insights panel.
  2. In the Data Data pane icon pane, hold down the Ctrl key, select Product Category and Sales, and then drag the data elements to the canvas.
  3. In the Data pane, select Customer Segment and drag it to the canvas.


    Description of bar_vis.png follows
    Description of the illustration bar_vis.png
  4. Click the visualization type list icon list and select Line Line vis type icon.


    Description of line_vis.png follows
    Description of the illustration line_vis.png
  5. Click Visualizations Visualizations pane icon. Select Dashboard Filters, drag it to the canvas and release above the line visualization.
  6. Click Data Data pane icon. In the Data pane, select Product Sub Category and drag it to the Dashboard Filters control.


    Description of dashboard_filter_ctrl.png follows
    Description of the illustration dashboard_filter_ctrl.png

Bind a Parameter to a Date Type Derived Date

In this section, you create a parameter using a derived date data element. Oracle Analytics creates a parameter for the Ship Date Year derived date and adds it to the Parameters pane.

  1. In the Data pane, expand Ship Date and expand Order Date to show the derived date data elements.


    Description of ship_date_order_date.png follows
    Description of the illustration ship_date_order_date.png
  2. In the Data pane, under Ship Date select Year and drag it to the workbook filter bar.


    Description of ship_date_year.png follows
    Description of the illustration ship_date_year.png
  3. Click Ship Date (Year) in the Filter bar, select 2020. In the Ship Date (Year) filter and click Bind to Parameter Bond to parameter icon and select Create Parameter.


    Oracle Analytics updates the line visualization to reflect Sales by Product Category and Customer Segment for 2020.

    Description of ship_date_year_2020.png follows
    Description of the illustration ship_date_year_2020.png
  4. Click Preview Preview icon.
  5. In Preview, click ship Date (Year) list icon, click 2020 to remove it as a filter, and then select 2023. Click outside the dialog.


    Oracle Analytics updates the line visualization to sales for the selected Ship Date Year.

    Description of ship_date2023.png follows
    Description of the illustration ship_date2023.png
  6. Click Edit Edit icon.

Bind a Parameter to a DateTime Derived Date

In this section, you create another visualization and bind a parameter to a timestamp derived date.

  1. Click the Ship Date Year filter and click Clear.
  2. Click Data Data pane icon. Hold down the Ctrl key. In the Data pane, select Sales, Product Sub Category, and Order Date (Year), right-click, select Pick Visualization, and then select Line line vis icon.
  3. If needed, move Order Date (Year) to Color and Product Sub Category to Category (X-Axis) in the Grammar pane.


    4. Description of sales_order_date_year_psc.png follows
    Description of the illustration sales_order_date_year_psc.png
  4. Click Parameters Parameters icon. Click Menu Menu icon and select Add Parameter.
  5. In Create Parameter, enter Order Date Year in Name. In the Data Type row, click Text and select Timestamp.
  6. In Available Values, click Any and select Column. Click list List icon in the empty field. Under sample_order_lines2023, expand Order Date, click Year, and then click OK.
  7. Click Data Data pane icon. In the Parameters pane, select Order Date Year and drag it to the dashboard filter control.


    Description of order_date_year_control.png follows
    Description of the illustration order_date_year_control.png
  8. Click Save Save icon. In Save Workbook, enter a Name and click Save.
  9. Click Preview Preview icon.
  10. Click All list icon in Order Date (Year) and select 2022. In Product Sub Category, click All and select Bookcases, Chairs and Chairmats, Computer Peripherals and Office Furnishings.


    Oracle Analytics updates the visualization to the sales results for the year selected.

    Description of preview_order_date_year.png follows
    Description of the illustration preview_order_date_year.png

Learn More