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
No Direct Measure Filtering: SAC supports filtering dimensions using setDimensionFilter() but lacks a direct way to filter measures through scripts.Limited Scripting Functions: Functions like isNaN(), null, and ! are not available, making error handling difficult.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_1Description: CatputreIPType: Integer (since we going to capture measure)Default value: 0Enable Expose variable and enable dynamic URL
Step 2: Create an Input Field and Link to Script Variable
Add an Input Field:Input field is used to get the value from the user.
Data Source: Script variableBinding display: Map the created Script variableMake 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.
Problem StatementIn 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 FacedNo Direct Measure Filtering: SAC supports filtering dimensions using setDimensionFilter() but lacks a direct way to filter measures through scripts.Limited Scripting Functions: Functions like isNaN(), null, and ! are not available, making error handling difficult.Dynamic Table Complexity: Since the table structure is not static, traditional filters cannot be applied easily.OutputFilter.gif Solution ApproachTo overcome these challenges, we use a calculated measure combined with scripting. Here’s a structured approach:Step 1: Create a Script VariableNavigate to the Story in SAC.Create a Script Variable to capture user input.Provide required details such as, Name: ScriptVariable_1Description: CatputreIPType: Integer (since we going to capture measure)Default value: 0Enable Expose variable and enable dynamic URL Step 2: Create an Input Field and Link to Script VariableAdd an Input Field:Input field is used to get the value from the user. Data Source: Script variableBinding display: Map the created Script variableMake 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 MeasureGo 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 TableAdd a Table:Connect it to your data model.Display the calculated measure in a column.Step 5: Handle Empty Input with ScriptingIf 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 valuevar inputValue = InputField_1.getValue(); // Check if the input field is empty using a boolean comparisonvar isEmpty = inputValue === “”; // Set the input to “0” if it is emptyif (isEmpty) { inputValue = “0”; InputField_1.setValue(“0”); // Reflect 0 in the input field} // Show a success messageApplication.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. ConclusionThis 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. Read More Technology Blogs by Members articles
#SAP
#SAPTechnologyblog
