Status: Solved
Priority: Medium
Security: Public
Views: 3202
Last Modified: 2014-02-12

How to Filter with "starts with" in Report Builder 3.0 Reporting Model Query Designer?

I have a filter for a text field. I want to filter out any items that starts with two letters "AB".
I'm working with a reporting model and the related query designer.
There is a "contains" option but I don't see a starts with or ends with option. Nor do I see an option to ender my own expression. I've tried contains with variations of the % and *, but neither worked. Any suggestions as to how to use a "starts with"/"ends with" type of filter with the query designer and a reporting mode? (not sql)

Thank you,

jaisy99

Asked: 2014-02-10

Advertise Here

1 Solution

LVL 37

ValentinoVBI ConsultantCommented: 2014-02-11

It should be possible to use the Filter functionality on the dataset. One of the operators is "like". If you use the like in combination with a search value that ends with an asterisk (and not starts), you've got exactly the same as you'd have with Starts With.

Here's what the complete filter would look like:

Expression: YourTextField
Operator: Like
Value: AB*

Please note: this filter is different functionality than the filtering through the query designer...

LVL 1

jaisy99Author Commented: 2014-02-11

Thank you. This is working to find all that start with AB, but I want to filter these out/exclude them. Where would I put the NOT using this filter section?

LVL 37

ValentinoVBI ConsultantCommented: 2014-02-11

but I want to filter these out/exclude them

Ow right, missed that part, sorry. This makes it a little more complicated because there's no "not like" operator, but it is possible. The trick is to build an expression that filters out the records you don't want and then use a simple boolean comparison. Here's how that goes:

Expression: =Left(Fields!YourTextField.Value, 2) = "AB"
Type: Boolean
Operator: =
Value: false