How to set Field values dynamically in Default Bookmark. Whenever value will be avilable in Reports, it will set to Default Bookmark

[Nandita27]

Our rquirement is to use default dynamic bookmark values like A,B,C,D for [XYZ ColumnName]. Out of four values( A, B, C, D) whenever A will available Default filter should be A. If A is not available then it should display B. If A, B not available then it will be C and If A,B,C not avilable it will be D.

CASE WHEN Year = year(current_date) and [XYZ ColumnName] = 'A' then 'A'
WHEN Year = year(current_date) and [XYZ ColumnName]= 'B' then 'B'
WHEN Year = year(current_date) and [XYZ ColumnName]= 'C' then 'C'
ELSE 'D'
END

I have written below expression in search option of the [XYZ columnName] but it is not working

=[XYZ columnName]=(if([Year]=$(=(Year(Today()))) and match([XYZcolumnName],'A','B','C','D')>0,'A',
if([Year]=$(=(Year(Today()))) and match([XYZ columnName],'B','C','D')>0,'B',
if([Year]=$(=(Year(Today()))) and match([XYZ columnName],'C','D')>0,'C',
if([Year]=$(=(Year(Today()))) and match([XYZ columnName],'D')>0,'D')))))

Do I need to use loop. if yes then how can I define loop in default dynamic bookmark

[marcus_sommer]

You may try something like this:

pick(rangemax(
    sign(count({< [Year]={$(=(Year(Today()))}, [XYZcolumnName] = {'A'} >} distinct [XYZcolumnName])) * 4,
    sign(count({< [Year]={$(=(Year(Today()))}, [XYZcolumnName] = {'B'} >} distinct [XYZcolumnName])) * 3,
    sign(count({< [Year]={$(=(Year(Today()))}, [XYZcolumnName] = {'C'} >} distinct [XYZcolumnName])) * 2,
    sign(count({< [Year]={$(=(Year(Today()))}, [XYZcolumnName] = {'D'} >} distinct [XYZcolumnName])) * 1),
'A', 'B', 'C', 'D')