Dynamic Measure Filtering Using Input Fields in SAP Analytics Cloud (SAC)

[vignesh3027]

🚀Problem Statement

In SAP Analytics Cloud (SAC), users often need to filter data in tables with both dimension and measure input controls. Typically, you can use:

• Dimension Filters to filter based on text or categorical data.
• Measure Filters to filter numeric data like totals, averages, or sales values.

However, when tables are dynamic (due to dimension and measure input controls), a direct measure filter using scripting is not available. This limitation creates the need for a custom solution.

🧩 Challenges Faced

1. No Direct Measure Filtering: SAC supports filtering dimensions using setDimensionFilter() but lacks a direct way to filter measures through scripts.
2. Limited Scripting Functions: Functions like isNaN(), null, and ! are not available, making error handling difficult.
3. Dynamic Table Complexity: Since the table structure is not static, traditional filters cannot be applied easily.

✅Output

Filter.gif

 🛠️ Solution Approach

To overcome these challenges, we use a calculated measure combined with scripting. Here’s a structured approach:

✅Step 1: Create a Script Variable

• Navigate to the Story in SAC.
• Create a Script Variable to capture user input.
• Provide required details such as,
•  Name: ScriptVariable_1
• Description: CatputreIP
• Type: Integer (since we going to capture measure)
• Default value: 0
• Enable Expose variable and enable dynamic URL

✅Step 2: Create an Input Field and Link to Script Variable

1. Add an Input Field:
• Input field is used to get the value from the user.

• Data Source: Script variable
• Binding display: Map the created Script variable
• Make sure enable the write-back runtime. (It will dynamically fetch the value without a apply button)
• Map the script variable to the write-back runtime.

Ensure Real-Time Update:

• The input field should update the script variable whenever the user enters a value.

✅Step 3: Create a Calculated Measure

• Go to your SAC Model and create a Calculated Measure using the formula:

IF ("Total Sales" >= ScriptVariable_1, "Total Sales")

Explanation:

• ScriptVaraible_1 stores the user input.
• Displays the Total Sales value, only if it greater than or equals the input threshold.

✅Step 4: Add the Calculated Measure to the Table

• Add a Table:
• Connect it to your data model.
• Display the calculated measure in a column.

✅Step 5: Handle Empty Input with Scripting

If no input is provided, SAC won't display any data since the calculated measure in the table awaits input. To address this, the following script ensures the input value defaults to 0 if left empty.

• Forgotten Reset: If users apply a filter and forget to enter a new input, no data will appear. The script ensures the input resets to 0 in such cases.
• Continuous Monitoring: After applying the filter, users can still adjust the threshold dynamically without facing data unavailability.

Script Code:

// Get the input value

var inputValue = InputField_1.getValue();

// Check if the input field is empty using a boolean comparison

var isEmpty = inputValue === "";

// Set the input to "0" if it is empty

if (isEmpty) {

    inputValue = "0";

    InputField_1.setValue("0"); // Reflect 0 in the input field

}

// Show a success message

Application.showMessage(ApplicationMessageType.Success, "Total Sales filter value set to: " + inputValue);

💡Explanation:

• inputField_Sales.getValue(): Captures user input.
• inputValue === "": Checks if the input field is empty.
• inputField_Sales.setValue(): Ensures input is set to 0 if empty.
• Application.showMessage(): Provides feedback to users.

⚙️UI/UX enhancement:

• Create a clear filter functionality, in my case I have used an image to clear the value.

🎯Conclusion

This approach effectively handles dynamic measure filtering using input fields and scripting in SAC. Users can confidently filter data based on their criteria, while ensuring a smooth user experience with clear feedback.