Recently, we were building a model and was trying to update/enhance the existing code versus having to start from scratch. One thing in particular was a join that contained 40+ lines of code to check a user’s security to see which rows to return. This join connected a LOCATION table to a USERACCESS table. The join did a check to see which of four possible fields a user had access to or if a user had ‘ALL’:
field1 = field1 and field2=field2 and field3=field3 and field4=field4
OR
field1 = ‘ALL’ and field2=field2 and field3=field3 and field4=field4
OR
….
field1 = ‘ALL’ and field2=‘ALL’ and field3=‘ALL’ and field4=‘ALL’
All possible combinations of the four fields. Ridiculous, yes? Yes. This original join was done by someone in 2005 and updated in 2007. Unfortunately, none of these people are still around and the new group had no idea what this was doing, but knew it was getting appended to every piece of report SQL they executed. Wasteful, bad code and a candidate for improvement. Our initial thought on this was to use four parameter maps instead with a query subject filter for each of the conditions.
The next thing was the amount of potential values per EMPLOYEEID:
Parameter1 = max 5 values — no problem
Parameter2 = max 10 – 15 values —a little more, but still not a huge problem maintaining
Parameter3 = max 300 values — um… a bit of a challenge
Parameter4 = max 4,000 values — nope. Not going to happen as a KEY/VALUE pairing
The catch with parameters 3 and 4 is you either have a few values or ALL 4,000 values. So, this is what we did for the KEY/VALUE pairing in the parameter map this:
KEY VALUE
A123452 ‘U430’,’3421’,’2344’
…
X444222 ‘ALL’
So far so good, right? We built out some CSV files from the source data, loaded the parameter maps and everything was looking good. We built the filter on the query subject as:
[FieldValue1] in (#$ParameterMap1{$accountPersonalInfo.username}#)
Works PERFECT for anyone who has specific values. But does not work for anyone with ‘ALL’ parameter map. Why? Because there is no ‘ALL’ in the source table. There are only unique records with actual values. What we needed was something in the filter that checked to see if the value was ‘ALL’ and if yes, skip the parameter and show all the values, otherwise, lookup the user’s available records. The only problem: you can’t put a classic ‘if…then…else’ in a model filter. What to do, what to do…
After battling the syntax, the solution was a bit of an old school programming hack. The new filter looks like this:
‘ALL’ in (#$ParameterMap1{$accountPersonalInfo.username}#)
OR
[FieldValue1] in (#$ParameterMap1{$accountPersonalInfo.username}#)
Essentially it’s a 1=1 situation. If the user has ‘ALL’ in the parameter map, 1=1 and no filter is applied. If the user doesn’t, it continues on and only shows the proper values. Works like a charm.
Contact us if you can think of a more elegant way to do this; we would love to hear how to improve it further. It does currently work and gets everyone out of huge amounts of manual coding.
This post was contributed by our own training team: expert instructors with real-life, pragmatic experience.
