Before you Begin

This tutorial shows you how to use parameters with data actions in Oracle Analytics.

Background

You can use parameters with data actions to pass values from a source workbook to a target workbook. The source workbook contains summary data about customers and order priority by city. The target workbook contains detailed information about customers by order priority and city.

What Do You Need?

Create a Dataset

In this section, you create a dataset using the sample_order_lines.xlsx file.

  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_lines.xlsx, click OK.
  5. Click Save Save icon. 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 the Source Workbook

In this section, you create a source workbook containing the data action that passes a parameter value to a target workbook. You also create a calculation for the number of customers.

  1. Click Create Workbook.
  2. In the Data Data panel icon panel, right-click My Calculations and click Create Calculation.
  3. In New Calculation, enter # of Customers in Name. In the expression field, enter Count, select CountDistinct. In column, enter Cust and select Customer Name.
  4. Click Validate. Click Save.


    You use in the # of customers and # of orders in the workbook's visualizations just like other data elements in the dataset.

    Description of dataset_calcs.png follows
    Description of the illustration dataset_calcs.png
  5. Click Save Save icon. In Save Workbook, enter Order Priority Summary, and then click Save.

Create a Parameter in the Source Workbook

In this section, you use the Developer options to capture the Logical SQL statement to help in creating a City parameter.

  1. In the Data panel, select City, and then drag it to the canvas.
  2. Click the workbook's Menu Workbook Menu icon, select Developer.
  3. In the Developer pane, click Logical SQL in the Performance Tools tab. Click Refresh.
  4. In the Logical SQL tab, copy the SELECT statement for City. Close the Developer pane.
  5. Click Parameters Parameter icon. Click Add Add icon. In Create Parameter, enter City in Name.
  6. Click Text in the Data Type row and select Integer.
  7. Select Logical SQL from Possible Values.
  8. In the expression field, paste the SELECT statement that you copied from the Logical SQL tab.


    Create this filter to act as though all values are selected in the parameter when no values are selected.

    Your Logical SQL should look similar to the following with your credentials rather than Admin as seen in this example:

    SELECT
   
   XSA('Admin'.'sample_order_lines')."sample_order_lines2"."City" s_1
FROM XSA('Admin'.'sample_order_lines')
ORDER BY 2 ASC NULLS LAST
FETCH FIRST 125001 ROWS ONLY
  9. Click OK.


    The City parameter appears in the Parameter list.

    Description of city_parameter.png follows
    Description of the illustration city_parameter.png
  10. In the City table visualization, click Menu Menu icon, and then select Delete Visualization.

Create Visualizations in the Source Workbook

  1. In the Data panel, hold down the Ctrl key, select Order Priority and select # of Customers from My Calculations. Right-click, select Pick Visualization, and then select Donut Donut visualization icon.


    Description of orderpriority_num_cust.png follows
    Description of the illustration orderpriority_num_cust.png
  2. Click the visualization's Menu Visualization Menu icon, select Edit, and then select Duplicate Visualization.
  3. In the second visualization, click Visualization Type Change visualization type icon, and then select Table Table visualization icon.
  4. Drag City to Rows in the Grammar pane as the first data element.
  5. Click Table in the Visualization Type list and select Pivot Pivot icon.
  6. Drag Order Priority from Color to Columns in the Grammar pane.


    Description of orderpriority_vizs.png follows
    Description of the illustration orderpriority_vizs.png
  7. Click Save.

Define a Filter Expression

In this section, you define a filter expression with the City parameter and use it in a Dashboard Filter Control.

  1. Click Visualizations Visualizations pane icon. In the Visualizations pane, drag Dashboard Filters Dashboard filters icon to the canvas.
  2. Click Data Data panel icon. From the sample_order_lines dataset, drag City to Filter Controls.


    Description of city_dashboard_filter.png follows
    Description of the illustration city_dashboard_filter.png
  3. Hover over Filters in the Grammar pane, click Drop Target Options, and then select Create Expression Filter.


    Use uppercase for '~ALL~' in the expression.

  4. In Expression Filter, enter City in Label.


    You must enter the expression. You cannot copy this code to produce a valid expression.

  5. In the Expression field, enter the expression as follows:
    • Enter CASE. Delete the default text.
    • Enter WHEN '~All~' IN (
    • Click Parameters Parameter icon and drag the City parameter to the expression after the opening parenthesis
    • Enter ('~All~') in default value and enter a closing parenthesis
    • Enter THEN 1 ELSE (CASE WHEN
    • Enter Cit, select City from the dataset, enter IN
    • Drag the City parameter to the expression
    • Enter ('~All~') in default value, and then enter a closing parenthesis
    • Enter THEN 1 ELSE 0 END) END = 1


    Your expression should look similar to the following:

    CASE
WHEN '~All~' IN (@parameter ("City") ('~All~'))
THEN 1 ELSE (CASE WHEN City in (@parameter ("City") ('~All~'))
THEN 1 ELSE 0 END) END = 1
  6. Click Validate. Click Apply, and click Save.
  7. In the Dashboard Filter, select Adelaide to update the data in the visualizations.


    Description of param_dashboard_filter.png follows
    Description of the illustration param_dashboard_filter.png
  8. Click Save Save icon. Click Go back Go back icon.

Create a Target Workbook

In this section, you create the workbook and visualization to receive the parameter values from the source workbook.

  1. On the Home page, hover over the sample_order_lines dataset, click Actions, and then select Create Workbook. Close the Insights pane.
  2. In the Data pane, hold down the Ctrl key, select Order Priority, Customer Name, City, and Sales. Right-click, select Pick Visualization, and then select Pivot Pivot icon.
  3. Click Visualizations Visualizations panel icon.
  4. In the Visualizations pane, drag Dashboard Filters Dashboard filters icon to the canvas.
  5. Click the Data Data pane icon pane. Drag Order Priority to the dashboard filters visualization.
  6. In the Data pane, drag City to the dashboard filters visualization.
  7. Click list List icon in the City filter to open the dialog. Click Bind to Parameter Bind to Parameter icon. Click Create Parameter and select City.


    The Bind to Parameter changes to green from black.

  8. Click Save Save icon. In Save Workbook, enter Detailed Report in Name, and then click Save. Click Go back Go back icon.

Define the Data Action in the Source Workbook

In this section, you create a data action in the Order Priority Summary workbook to pass values to the Detailed Report workbook.

  1. On the Home page, hover over the Order Priority Summary workbook, click Actions Menu icon, and then click Open.
  2. Click Edit Edit icon.
  3. Click the workbook Menu Menu icon and select Data Actions.
  4. In Data Actions, click Create Action Create Action icon.
  5. Enter City by Sales and Order Priority in Name. Select Analytics Link from the Type list.
  6. Click Select Data in Anchor To, and click Order Priority. Click outside the selection list.
  7. In Target, click This Workbook, and then click Select from Catalog.
  8. In Select a workbook, click Detailed Report, and then click OK. Leave the value, All, in Pass Values and Pass Parameter Values. Click OK.
  9. Click Save Save icon.

Test the Analytics Link

In this section, you select a city from the dashboard filter control, select an order priority value in the donut visualization, and then test the data action link in the source workbook.

  1. In the dashboard filter visualization, select Adelaide from the City list.


    If the value in City is Adelaide already, go to step 2.

  2. In the donut visualization, select Order Priority Critical.


    Description of adelaide_critical.png follows
    Description of the illustration adelaide_critical.png
  3. Click Save Save icon.
  4. In the workbook canvas, right-click and select City by Sales and Order Priority.


    The target workbook opens with the filter and parameter values from the source workbook.

    Description of detailed_passed_values.png follows
    Description of the illustration detailed_passed_values.png

Learn More