I'm creating a report but I'm getting stuck on a total. There are a number of other questions similar to this, but the twist I'm dealing with doesn't appear to be in any of them.
I have a found set of records based on a date range, status, and location, with a sub summary on state (locations are in states). Since a location may appear numerous times, I need a count of how many times it's in the total. I tried various attempts with ExecuteSQL, but it's more parameters than I'm able to pass and SQL apparently ignores a found set (pity that). I tried the value list approach but that also isn't working for me as I'm not able to create a value list just on a found set. I also tried creating a List from the found set, which I thought should work, but no luck there either.
Is this even possible? The only technique I haven't applied is to loop through the found set, count the unique location and store that in a variable, which seems like overkill and prone to break if I don't account for n-rooms.