Help with Select in Field Trigger

[mikecrengland]

Hello All -

I need to select the top N values for a field when a user navigates to a tab. I'm experimenting  with the Adventureworks DB and created a listbox with the correct values, but can't figure out how to translate it to a trigger. The expression I have so far is:

=If(Aggr(Rank(Sum(SalesAmount)),City) < 5, City)

and it works fine and gives me the top 4 Cities by sales. How do I translate that to the Search String for a Select in Field trigger (or action)?

Thanks in advance,

Mike

www.fortunecookiebi.com

[#2]

Hey Mike,

You can go to Settings -> Sheet Properties -> Triggers and click Add Actions under OnActivateSheet.

Here you can select 'Select in Field' and give City as the Field Name and following expression in search string:

=If(Aggr(Rank(Sum(SalesAmount)),City) < 5, '('& Replace(City,',','|')&')')

Please let me know if you find any issues.

Thanks

AJ

[mikecrengland]

Hi AJ,

Thanks for the reply. Unfortunately, I can't get it to work. If I put

'('& Replace(City,',','|')&')'

into a text object and select a single City, it shows, for example (Paris). If I select multiple values from City, it shows ()

I've been messing with it and think it needs a 'concat' in there somewhere, but still can't get it right. I tried

'(' & Concat(Distinct City,'|') & ')'

and that brings back what looks like the right format for multiple values

but the trigger won't set the values.

I think we're close... Any thoughts?

Thanks!

Mike

www.fortunecookiebi.com

[#4 · Accepted solution]

Yup you are right. Should use concat.

I used this formula and it worked.

='('&concat(if(Aggr(Rank(sum(Sales)),City)<4,City),'|')&')'

Also attached a sample qvw with some dummy data. If you click the second tab 'Tab with Trigger' it will automatically select top three cities.

Does this meet your requirement?

Thanks

AJ

[mikecrengland]

Thanks, AJ!

That is awesome!