Lurker here, and I have registered today to pose a unique problem concerning keywords that I have yet to find a solution for. I've searched for a solution to no avail.
My goal is to:
- Cross-reference a list of keywords, inputted via prompt or hard-coded detail filter, against three fields ([Description], [Expense Country], [Expense City]). The report would also return instances when any of these fields are null.
- Display ALL keywords matched in the each of the above fields in three additional respective columns (i.e. [Description MATCH], [Expense Country MATCH], [Expense City MATCH] data items).
Currently, I have implemented the following:
Detail Filter:[SQL1].[DESCRIPTION] is null or
[SQL1].[DESCRIPTION] contains ('%KEYWORD1%') or
[SQL1].[DESCRIPTION] contains ('%KEYWORD2%') or
[SQL1].[EXPENSE_CITY] is null or
[SQL1].[EXPENSE_CITY] contains ('%KEYWORD1%') or
[SQL1].[EXPENSE_CITY] contains ('%KEYWORD2%') or
[SQL1].[EXPENSE_COUNTRY] is null or
[SQL1].[EXPENSE_COUNTRY] contains ('%KEYWORD1%') or
[SQL1].[EXPENSE_COUNTRY] contains ('%KEYWORD2%')[Description MATCH] data item (and similar setup for [Expense Country MATCH] and [Expense City Match] data items):case
when [SQL1].[DESCRIPTION] is null then '(BLANK)'
when [SQL1].[DESCRIPTION] contains ('%KEYWORD1%') then 'KEYWORD1'
when [SQL1].[DESCRIPTION] contains ('%KEYWORD2%') then 'KEYWORD2'
endThere are a couple observations that should be noted:
- Microsoft SQL Server Full Text Indexing/Searching is disabled
- Ideally, we would input a multiple keyword listing via a textbox prompt, although textbox prompts revert from multi-select to single-select due to "CONTAINS" being used in a detail filter/data item. Any way we can get around this, or would we have to hard-code the keywords in the detail filter, like above? (Since this report will be automated/scheduled, we are leaning towards hard-coding the keywords anyway)
- Although there may be multiple keyword matches per transaction, due to the case statement above, only one keyword will return in the "match" columns. How can we get the "match" columns to return all found keywords?
A single, hopeful bump. :)
Instead of a single parameter accepting multiple keywords how about a fixed number of parameters each accepting a keyword. All but one could be optional. Not necessarily elegant but might be better than hard coding.
Can you use wildcards in your search? The reason for asking is that I was thinking you could concatenate your fields into one field, then you could perhaps filter along the lines of:
[ConcatenatedField] contains if(?Prompt1? is missing) then (%) else (?Prompt1?) or [ConcatenatedField] contains if(?Prompt2? is missing) then (%) else (?Prompt2?) ...
Quote from: Lynn on 10 Nov 2015 02:16:16 AM
Instead of a single parameter accepting multiple keywords how about a fixed number of parameters each accepting a keyword. All but one could be optional. Not necessarily elegant but might be better than hard coding.
Thanks for the recommendation, Lynn! This would be awesome if we had a low amount of keywords, although we will be searching for a little above 400. :(
Quote from: BigChris on 10 Nov 2015 03:18:31 AM
Can you use wildcards in your search? The reason for asking is that I was thinking you could concatenate your fields into one field, then you could perhaps filter along the lines of:
[ConcatenatedField] contains if(?Prompt1? is missing) then (%) else (?Prompt1?) or [ConcatenatedField] contains if(?Prompt2? is missing) then (%) else (?Prompt2?) ...
Thanks for the recommendation, BigChris! I will check to see if I can work something like that to list multiple keywords into the match columns.
Just wanted to add some closure to this post - I ended up hardcoding the keywords into a detail filter, as described in the original post.
Fortunately, any keywords would be updated quarterly, so it's not much of a headache. Hope this helps someone in the future. ;)