Dynamic Text Boxes in Oracle Analytics

​​​​​​Want to make your dashboards more engaging and informative?

Dynamic text boxes in Oracle Analytics can transform static reports into interactive storytelling experiences. By combining parameters with SQL queries, you can create narrative descriptions that automatically update based on user selections.

In this article, we’ll create a dynamic title that displays total rocket launches and success rate for a selected time period, along with information about the top rocket launcher.

Example output:

“1,500 rockets have been launched in the last 35 last years with an average of 94.2% success. The top rocket launcher is SpaceX, with 434 launches during this period.”

Prerequisites

Begin by enabling Developer Mode:

1. Click the profile at the top-right corner of the page
2. Select Advanced
3. Toggle Enable Developer Options to on

Understanding the Parameter Structure

This implementation uses a two-tier parameter system:

1. Root Filter Parameter – Acts as the primary control
   • Parameter users will interact with (in our case, the time window)
   • Serves as an input for all other parameters
   • Controls the data scope for the entire dashboard
2. SQL Display Parameters – Dependent parameters that show values
   • These parameters contain SQL queries that use the Root Filter Parameter
   • Each represents a specific metric or attribute to display
   • Automatically updates when the Root Filter Parameter changes

Think of it as a tree structure:

Step 1: Create the Root Filter Parameter

This is the foundation parameter that will drive all dynamic updates:

1. Create the time filter:
   • Drag Date to the top filter
   • Click Relative Time
   • Click the (x) icon and select Create a Parameter on the Increment
   • Rename the parameter as RootFilter_Look Back Window
2. Understand the role:
   • Stores the user’s selected time window
   • When changed, it triggers updates in all dependent SQL parameters
   • Acts as a single control point for the entire dynamic text system

Step 2: Generate SQL Queries

Create parameters that depend on the root filter:

1. Create these visualizations to get base SQL queries:
   • Drag the measure #Launches to a Tile
   • Drag the measure #Success Rate to a Tile
   • Drag the attribute Rocket Launcher to a Table and filter it to Top 1 Rocket Launcher by #Launches
   • Drag the measure #Launches to a Table and filter it to Top 1 Rocket Launcher by #Launches
2. Retrieve SQL queries:
   • Click the three dots at the top-right corner of the page and select Developer
   • In the Developer Console, click Refresh to view a table with your visualizations
   • Click on each visualization and copy its Logical SQL
3. Modify the SQL queries:
   • Remove everything before SELECT
   • Remove 0 s_0,
   • Remove the last rows that are sorting or limiting the result: FETCH FIRST 500001 ROWS ONLY or ORDER BY 2 ASC NULLS LAST
   • Replace the hardcoded lookback period with the parameter: @parameter(“RootFilter_Look Back Window”)

Before/After SQL example:

Original SQL:

Modified SQL:

Step 3: Create SQL Display Parameters

1. On the left panel, click (x), then select Create a Parameter
2. Configure the parameter:
   • Name: Enter an appropriate name
   • Data Type:
     • Select Integer for whole numbers and toggle Format Numbers to on
     • Select Double for decimals and toggle Format Numbers to on
     • Select Text for categorical attributes
     • Initial Value: Paste in the modified Logical SQL query
3. Click OK.

Step 4: Build the Dynamic Text Box

Combine all parameters to create your dynamic narrative:

Parameter Flow Example:

1. Change Look Back Window to 25 years
2. Change RootFilter_Look Back Window updates to 25
3. All SQL parameters automatically recalculate:
   1. SQL_Launches retrieves launches for past 25 years
   2. SQL_Success calculates success rate for past 25 years
   3. etc…
4. Text box updates with all new values

This structure ensures that a single filter control can update multiple data points in your narrative, maintaining consistency across your dashboard while providing dynamic, real-time insights.

Call to Action

By implementing dynamic text boxes with parameters in Oracle Analytics, you can transform static dashboards into engaging, interactive experiences that tell your data’s story more effectively. This approach not only makes your dashboards more user-friendly but also ensures that your narrative stays consistent and up to date with user interactions.

You can find the dva file shown in this article here.

Ready to take your Oracle Analytics dashboards to the next level? Learn more about parameters in the Create and Use Parameters documentation.

Now that you’ve read this article, try it yourself and let us know your results in the Oracle Analytics Community, where you can also ask questions and post ideas.