Hi, the problem that I'm having is when passing a parameter into a report
that is using that value in a LIKE clause. Reporting Services seems to be
taking the SELECT statement and turning it into an = instead of a LIKE.

I have a column in my table called "Category" of fixed length, which
contains many different values, "Money Market" being one of them. Because
it's a fixed length char field, there is obviously some extra white space on
the end. Additionally, I have another column called "Sector" which could
contain "Consumer Staples" and this is set up exactly the same. In my main
report, I pull both of these fields.

My problem is this:

In my main report, the whitespace gets trimmed off of Category but not
Sector - I have no idea why. I have a link to another report, to which I am
passing both Category and Sector as parameters. The recipient report is
using these parameters in a LIKE clause. Both parameters have default values
of '%' so that if they are left blank, they become wild cards.

If I leave Category blank (therefore defaulting to a wildcard), the Sector
parameter, complete with 35 chars of whitespace on the end, will get passed
on to the next report and the SELECT statement will return the expected
results.

However, if I leave Sector blank (therefore defaulting to a wildcard), the
Category parameter, which has been mysteriously trimmed of whitespace, will
get passed on to the next report and will return 0 results, which is most
definitely unexpected. Strangely, if I go into the table, copy a value
directly out of the Category column (complete with 20+ characters of white
space), and then manually pass this parameter to the next report, it will
pull the expected results.

I don't really care all that much that Reporting Services is automatically
trimming white space off of one of my fields and not the other...this is not
the fundamental reason for why I am getting 0 results.

I have tried the same SELECT statement in Query Analyzer, replacing the
parameters with literal values, and I get the expected results, with or
without white space. This is what I expect to get because I'm using LIKEs
and not ='s.

I have tried Profiler to catch Reporting Services' execution of my report's
SELECT statement. In looking at the execution, I'm unable to see what the
problem might be. The only thing that I can consider is that somehow
Reporting Services is transforming my LIKEs into ='s.

I always edit the query in the data tab and routinely use queries like

SELECT ....
FROM ....
GROUP BY ...
HAVING (<field> LIKE '%' + @param + '%')

Never had a problem.

The only other solution is to use a stored procedure instead of the
query.

Hope this helps