I am trying to create a customer search report. Often times users will have need to search for a name like 'Johnathan Smith-Doe', and the record in the database will be 'John Smith'. They need the flexibility to add wildcards before or after (or both) to the name they wish to search.
(http://i1305.photobucket.com/albums/s557/PSRNHME/last%20name%20search_zpsmmukrnai.jpg) (http://s1305.photobucket.com/user/PSRNHME/media/last%20name%20search_zpsmmukrnai.jpg.html)
So my requirements are:
1.) Create a text prompt to allow the user to type in a first or last name (?p_last_name?)
2.) Allow the user to select an optional addition of a wildcard before, after, or both before and after the string they enter. (Static choices value prompt: ?p_wldcard?
The use case scenarios are:
1.) User enters Last Name = 'Smi' and does not select a wildcard option. No records are returned because that last name does not exist.
2.) User enters Last Name = 'Smi' and selects 'Add wildcard to end'. This returns 'Smith', 'Smithson', etc.
3.) User enters Last Name = 'Smith' and selects 'Add wildcard to start'. This returns 'Hopkins-Smith', or any other record that meets the wildcard criteria.
4.) User enters Last Name = 'Smith' and selects 'Add wildcard to both start and end'. This returns 'Hopkins-Smith', 'Smithson', etc.
Pretty simple stuff, but I am struggling to use the presence or absence of ?p_wldcard? (null, if none selected) to drive the report filter.
Here is what I have tried so far:
1.) OR-separated scenarios to drive the filter:
((?p_wldcard? = '' OR ?p_wldcard? ISNULL) AND UPPER([Last Name]) = UPPER(?p_last_name?))
OR (?p_wldcard? = 'Add wildcard to end' AND UPPER([Last Name]) LIKE UPPER(?p_last_name?)+'%')
OR (?p_wldcard? = 'Add wildcard to start' AND UPPER([Last Name]) LIKE '%'+UPPER(?p_last_name?))
OR (?p_wldcard? = 'Add wildcard to both start and end' AND UPPER([Last Name]) LIKE '%'+UPPER(?p_last_name?)+'%' )
2.) Filter CASE statement, which doesn't work, because the operator has to be '=' for when the user DOES NOT SELECT a wildcard option, and 'LIKE' when they DO SELECT a wildcard option.
CASE
WHEN ?p_wldcard? = 'Add wildcard to end' THEN UPPER(?p_last_name?)+'%'
WHEN ?p_wldcard? = 'Add wildcard to start' THEN '%'+UPPER(?p_last_name?)
WHEN ?p_wldcard? = 'Add wildcard to both start and end' THEN '%'+UPPER(?p_last_name?)+'%'
ELSE UPPER(?p_last_name?)
END
I cannot see the forest from the trees. Please help!!
Are you using DQM or CQM?
I'm not sure - is there a way I can find out? I'm a lowly analyst
In the expression editor, click on the macros tab and expand the Macro Functions folder. Do you see the "simple case" function? If you do, you're on DQM and this is very simple. If not, it's CQM and we have to come up with a clever solution.
For some reason I whenever I paste snippets the post gets blocked. Take a look at the attached snippet. It works great for me, but it requires the package be set to DQM.
Thanks CognosPaul, can you briefly describe how this 'snippet' works? (I'm also unable to post the code from your .txt)
Macros are evaluated before SQL is generated, so the trick is to massage the output into what you need.
DQM added the simple case statement to the macro functions list, and that opens up a lot of different possibilities.
case prompt('p_wldcard','token','NA')
when 'NA' then ' = '
else ' like '
end
In that case statement it's evaluating the p_wldcard parameter. It has a default value, NA, making it an optional prompt. If nothing is selected, then return ' = ' otherwise ' like '. In the end, the filter will become either [Data Item1] = something or [Data Item1] like something.
The next bit is wrapped in the sq() function. This simply returns the next string wrapped in single quotes. It's important as TSQL expects the wildcards to be INSIDE the quotes, so you can't use a simple string prompt. The token prompt returns the parameter value as an unformatted snippet - no quotes.
The preceeding and proceeding case statements also evaluate the p_wldcard parameter. If the value is Before or Both it will insert the wildcard before the selected string.
Again, this only works in DQM, which is what all models should be based on now. If it's in CQM don't immediately convert the model - CQM to DQM projects are painful. There are a few ways to do this in CQM, but it's more involved.