I have two queries in my report, based on two separate data sources. Query A returns 100 usernames (object name 'usnm'), and contains only that single object; Query B returns 18,000 usernames (object name 'username'), along with other objects I need such as user location. What I want is a list of user locations (held in Query B) for the 100 users returned by Query A. Can you advise?
Here is what I have tried so far:
1. Query level - In query B, add username object as a filter and select 'result from object in another query' = 'usnm'. This did not work - database error, 'more than 1000 expressions in list'.
2. report level - merge 'usnm' and 'username' objects, and then make 'user location' a detail of that merged dimension. This did not work. I thought it would restrict the list to 100, but instead the merged dimension returns the 18,000 usernames!
3. report level - create a variable with an IF formula:
This did not work. Neither did using Inlist, as this requires a literal list of values to be specified.
Thanks in advance for your help! :)