"Begins With" Filter in Report Builder?

Hello,

Anyone know how to setup a "Begins With" or "Ends With" Promptable Filter in Report Builder?

Thanks!!

-Brian

Can't you use the LIKE clause? ie, WHERE name LIKE 'A%' etc.?

dlesko|||As per the original post, I am using Report Builder, not Report Designer to build the reports. With Report Builder you are not using an actual SQL Query for the report's data source, you are using what is called a "Semantic Query".

When I specify a Filter criteria on a string field in Report Builder, there are no choices for "Begins With" or "Ends With". There is only a "Contains". I have tried using the standard Wild Card characters (%, *) with the "Equals" filter condition and parameter value (i.e. "A%"), but that doesn't seem to work.

"Begins With" has got to be a pretty standard filter criteria and I have a hard time believing that Microsoft left this ability out of Report Builder. Although they don't support the ability to specify a range for a single parameter, which is also pretty common (like a Date Range).

Any info is greatly appreciated...

Thanks!

-Brian|||Sorry, my bad. I always confuse the two names!

Anyway, I haven't done this but according to the docs you can create a filter condition for "begins with". See here:

http://msdn2.microsoft.com/en-us/library/ms189375(en-US,SQL.90).aspx

Before you can create the filter condition you have to drag a field onto the filter box which may make that option more obscure than it should be.

dlesko|||Thanks for your help, but that really didn't say much more than I already knew...

I even tried creating the filter as an expression, but there doesn't look like an easy way of doing it here either...

Still looking for a way to do "Begins With" in Report Builder or confirmation that it cannot be done in this release.

Thanks!

Brian|||What about creating a column with a left(columnname,1) and using the Equal to operator?
(Of course this would only work if you're looking for the first character.)|||Thanks for the info...I was thinking of something like this, but I guess I really just want confirmation now about whether or not there is a "Begins With" or "Ends With" paramter criteria for String values.

Thanks!

Brian|||

The documentation is incorrect. Begins With and Ends With are not currently supported as filter condition operators, although you can create an equivalent formula filter condition:

AND(LENGTH(MyField)>7, LEFT(MyField, 7)="MyValue")

Support for parameters in the formula dialog is very limited, though, and creating a formula filter condition that includes multiple references to the same parameter (as you would need in your scenario) is not possible in this version of Report Builder.|||

Does the Report Designer (not Report Builder) support the "Begins With" or "Ends With" filter?

I played a little with it last night and didn't see anything...

-Brian

|||All the .NET System.String functions are available in RDL expressions, so you can use String.StartsWith, etc.