Add 'ALL' option

[KNoha]

We're building a report that filters data based on a dynamic attribute, "Operation ", linked to the database column CUSTOMPROPERTY5, which contains a comma-separated list of OPERATIONS for each role. Our goal is to enable two filtering behaviors: display all roles when "All" is selected and show only roles with a specific OPERATION in their ATTRIBUTE6 column when a specific value is chosen.

However, there's a critical challenge: "All" is not an inherent value in either CUSTOMPROPERTY5 or the dataset_values table correspondant to ATTRIBUTE6. While we can manually add "All" to CUSTOMPROPERTY5, adding it to the dataset is not feasible.

Our current main query aims to handle this with the following WHERE clause:

SELECT    

    e.displayname AS "Application",

    dv.ATTRIBUTE1 AS "ENTITY",

    dv.ATTRIBUTE3 AS "Metier",

    r.CUSTOMPROPERTY5 AS "Operation ",

    dv.ATTRIBUTE6 AS "Operation (Dataset)",

    r.ROLE_NAME AS "ROLE NAME",

    r.DESCRIPTION AS "ROLE DESCRIPTION"

FROM dataset_values dv

LEFT JOIN roles r ON

  r.CUSTOMPROPERTY5 LIKE CONCAT('%',  dv.ATTRIBUTE6, '%')

LEFT JOIN endpoints e ON (e.ENDPOINTKEY = r.ENDPOINTKEY)

WHERE

   dv.DATASETNAME = 'Referentials'

   AND (

       ${Operation} = 'All' OR

       dv.ATTRIBUTE6 = ${Operation}

   )

   AND r.status = 1

   AND e.ENDPOINTKEY IN ("64","5","46","96")

The dynamic attribute query provides the distinct values of ATTRIBUTE6 and includes an "All" option:

SELECT DISTINCT dv.ATTRIBUTE6, dv.ATTRIBUTE6     

FROM dataset_values dv

LEFT JOIN roles r ON r.CUSTOMPROPERTY5   LIKE CONCAT('%',  dv.ATTRIBUTE6, '%')   

WHERE dv.DATASETNAME = 'Referentials'

UNION ALL

SELECT 'All', 'All'

The problem lies in how the reporting tool handles the ${Operation} placeholder. Instead of substituting the single selected value, it incorrectly inserts the entire list of possible ATTRIBUTE6 values. This leads to the persistent "Operand should contain 1 column(s)" error as the SQL code tries to compare the entire list to a single value or use it in a LIKE comparison.

The limitation of not being able to modify the dataset to include "All" further complicates finding a SQL-based solution.

can you help me with this ?

[rushikeshvartak]

Are you building analytics ?

Sample condition : ( CASE WHEN FIND_IN_SET('All' , '${Input_Multiple_Managers_Workday_IDs}') THEN (1 = 1) END OR CASE WHEN '${Input_Multiple_Managers_Workday_IDs}' NOT LIKE '%All%' THEN (U.OWNER IN ( '${Input_Multiple_Managers_Workday_IDs.replace(",","', '")}' ) ) END )

[KNoha · Accepted solution]

yes, a RunTime Analytic, i found the solution : AND ( 'All' IN (${Operation}) OR dv.ATTRIBUTE6 IN (${Operation}) )

[KNoha]

Hello @rushikeshvartak 
based on that; Could you please tell me if we can use multi-select from a List in our case (There is no option to do multi Select From a Query ) And we want to Select Multiple values ?

[rushikeshvartak]

Raise idea ticket. multiples select is not supported