Use of Wildcard in Attribute Qualification?

[Mars2003]

Hi, I'm trying get a dataset back from our warehouse that matches any one of a list of text strings, unfortunately I can't guarantee these Strings and Therefore I'm attempting to use wildcards at either end of the text string, so that I capture all results. It's not working.

On the attribute Qualification window I'm using the "Qualify On" option and the "In List" operator with the following list structure "*Bill Topping*","*Richard Smith*"

Can anyone point me in the right direction.

 

[jonhoward]

I don't think in a SQL IN 'xyz' statement you can have wildcards. If it's IN then it is a literal string.

Depending on the size of your list, use LIKE *Bill Topping*

Unfortunately this means each entry in your list would have to be done seperately. Make the filter operators OR and you'll end up with SQL like...

where (Fact.AttributeDesc like '%Bill Topping%'
or Fact.AttributeDesc like '%Richard Smith%')

 

[Mars2003]

Thanks, that's what I feared.

Had this been in MS access then I'd have been fine, but I can seem to get to a point in Microstrategy desktop where I could write the SQL for each of my 200-300 criteria strings.

It would be good if I could amend an exiting MS report in a freeform SQL mode, but I dont believe it's possible. I'd be happy to be wrong though.

Thanks for your reply anyway

 

[jonhoward]

You could use Command Manager to build the filters?

Much easier for mass object creation... bit of messing round in notepad to get the list first then use Command Manager to run your script. Will look something like

CREATE FILTER "Bill Topping" IN FOLDER "\Public Objects\Filters\TekTips" EXPRESSION
"AttributeTitle@DESC like (^"%Bill Topping%^")" ON PROJECT "TekTips";
CREATE FILTER "Richard Smith" IN FOLDER "\Public Objects\Filters\TekTips" EXPRESSION
"AttributeTitle@DESC like (^"%Richard Smith%^")" ON PROJECT "TekTips";
ad nauseum

Then join them all up with OR statements.

But I agree, it's not good!

 

[Mars2003]

Thanks, I'll take this up with our project administrator.

 

[jonhoward]

Hmmm... I wonder also (it's a slow afternoon!) if you could write a piece of freeform SQL in MSTR and use the resultant report as a filter for a second report (so Report As Filter).

 

[Mars2003]

Nice idea, but attribute outputted needs processed into a less specific String that can be used between my wildcards and I can't work out how to do this as it's not a calculated metric.

eg [ANDREW LANGWORTHY (ADVICE) D]

needs to be translated as [*ANDREW LANGWORTHY*], which I was doing in excel, hoping to load it all back up as a concatenated filter list.