Use of Wildcard in Attribute Qualification?

• Aug 15, 2008

• #2

jonhoward

IS-IT--Management

Apr 9, 2003

41

EU

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%')

 

Upvote 0 Downvote

• Aug 15, 2008

• Thread starter
• #3

Mars2003

Technical User

Dec 20, 2007

4

GB

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

 

Upvote 0 Downvote

• Aug 15, 2008

• #4

jonhoward

IS-IT--Management

Apr 9, 2003

41

EU

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!

 

Upvote 0 Downvote

• Aug 15, 2008

• Thread starter
• #5

Mars2003

Technical User

Dec 20, 2007

4

GB

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

 

Upvote 0 Downvote

• Aug 15, 2008

• #6

jonhoward

IS-IT--Management

Apr 9, 2003

41

EU

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).

 

Upvote 0 Downvote

• Aug 15, 2008

• Thread starter
• #7

Mars2003

Technical User

Dec 20, 2007

4

GB

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.

 

Upvote 0 Downvote