Link to home
Start Free TrialLog in
Avatar of Gregory3
Gregory3

asked on

Using Count with Like and Wildcard in SSRS Expression

I need a cell in my SSRS report to show the total number of records in report, filtering by a LIKE operator and using a wildcard.  This is what I have.  It returns "2" everytime, no matter what the criteria is in the Like.  

=CountDistinct(Fields!parcel.Value like "*Deleted*")

Basically some of these parcel fields have the word "Deleted" in them.  I wanted to count those.  

- If I use Count() it returns the total as if the Like filter wasn't there
- If I use CountDistinct() it returns the incorrect value of "2"
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

<wild guesses>

* It would probably be better to handle this expression as a separate column in your data set SP/View/Whatever, with a bit value that calculates a 1 if the value LIKE '%DELETED%', 0 if it's not.  Then all the SSRS report has to do is sum the 1's and 0's in that column.

* Find out if Upper/Lower/Proper case is an issue.

* Wildards in SQL are %, I forget if that means it's also % in SSRS or *.
ASKER CERTIFIED SOLUTION
Avatar of Nico Bontenbal
Nico Bontenbal
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of James0628
James0628

I'm not really looking for any points here, since I believe Nicobo has already given you a solution, but just to add some explanation:

 You appear to be trying to use 'like "*Deleted*"' to control which records are included in the count.  It doesn't work that way.  You can't control which records are included in the count.  You can change the values and affect the count that way (which was why you got 2 when you used that condition, because you were counting the True/False results from that test, instead of the parcel values).

 Instead, you can use something like in Nicobo's post to produce a 1 or 0, depending on your condition, and sum those values to get your count.

 James