Manipulating parameters passed into a report - errors with parameters

Hi,
I've discovered a problem with repserv sp1 that I just can't see a way
around at the moment.
Basically we have a custom front end that allows the user to select
the params from either dropdown controls or textboxes. The text boxes
allow four states:
1/ All records
2/ Exact match
3/ Partial Match
4/ Starts with
The partial match is causing me the problem. Basically in the code
behind i'm prepending/appending the like clause character % to the
contents of the textbox i.e.
user enters 0123 into the textbox, the param passed into the report is
%0123%
Thats where the problem lies, repserv doesnt seem to accept that as a
valid param. It doesnt give any errors but it doesnt display the
required resultset. In the params returned to the user on the report,
it shows
23% has been passed in. The same thing happens if I type the url in
manually i.e.
http://localhost/Reportserver/MyReports/ListReport&rs:Command=Render&rs:Format=HTML4.0&rc:parameters=false&NameRefValue=%0123%&NameRefType=3
NameRefValue is declared as a string param, NameRefType is declared as
an integer param
Anyone have any ideas on this? Is it possible to manipulate the params
passed into the report before the engine actually processes them? If
this is possible I could just pass in the string without the %
characters & based on NamRefType value, add the % in the report itself
before the dataset is returned.
Cheers
SiSi wrote:
> Hi,
> I've discovered a problem with repserv sp1 that I just can't see a way
> around at the moment.
> Basically we have a custom front end that allows the user to select
> the params from either dropdown controls or textboxes. The text boxes
> allow four states:
> 1/ All records
> 2/ Exact match
> 3/ Partial Match
> 4/ Starts with
> The partial match is causing me the problem. Basically in the code
> behind i'm prepending/appending the like clause character % to the
> contents of the textbox i.e.
> user enters 0123 into the textbox, the param passed into the report is
> %0123%
>
Just read this the other day:
"NOTE Wildcards in Your SQL The filters you specify for your reports
are based on Visual Basic .Net 2003 syntax, so you'll find that the
pattern matching with the Like operator uses * as a wildcard and not %,
as you might use in SQL Like expressions."
_Hitchhiker's Guide to SQL Server 2000 Reporting Services_ by Peter
Blackburn and William R. Vaughan, Addison-Wesley 2005, p. 272.
hth
--Mike|||Thanks Mike,
I've just tried it and although the strange corruption of the
parameter is now ok, I still get incorrect results returned.
I use the % syntax in the starting with section with no problems, it
just seems to fail on integers and partial matches
Cheers anyway, it gave me a couple of ideas there!
Si
On Mon, 14 Feb 2005 07:54:53 -0600, "Mike Donnellan"
<spamspamspamandspam@.AT@.donnellanDOTcom> wrote:
>Just read this the other day:
>"NOTE Wildcards in Your SQL The filters you specify for your reports
>are based on Visual Basic .Net 2003 syntax, so you'll find that the
>pattern matching with the Like operator uses * as a wildcard and not %,
>as you might use in SQL Like expressions."
>_Hitchhiker's Guide to SQL Server 2000 Reporting Services_ by Peter
>Blackburn and William R. Vaughan, Addison-Wesley 2005, p. 272.
>hth
>--Mike|||Percent character is used to URL encode/escape other characters, so it
should itself also be encoded.
Check if this url works:
http://localhost/Reportserver/MyReports/ListReport&rs:Command=Render&rs:Format=HTML4.0&rc:parameters=false&NameRefValue=%250123%25&NameRefType=3
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Si" <no@.spam.thanks> wrote in message
news:mn4111pcl3qv3p6803i6pbstmhpch4lcge@.4ax.com...
> Hi,
> I've discovered a problem with repserv sp1 that I just can't see a way
> around at the moment.
> Basically we have a custom front end that allows the user to select
> the params from either dropdown controls or textboxes. The text boxes
> allow four states:
> 1/ All records
> 2/ Exact match
> 3/ Partial Match
> 4/ Starts with
> The partial match is causing me the problem. Basically in the code
> behind i'm prepending/appending the like clause character % to the
> contents of the textbox i.e.
> user enters 0123 into the textbox, the param passed into the report is
> %0123%
> Thats where the problem lies, repserv doesnt seem to accept that as a
> valid param. It doesnt give any errors but it doesnt display the
> required resultset. In the params returned to the user on the report,
> it shows
> 23% has been passed in. The same thing happens if I type the url in
> manually i.e.
> http://localhost/Reportserver/MyReports/ListReport&rs:Command=Render&rs:Format=HTML4.0&rc:parameters=false&NameRefValue=%0123%&NameRefType=3
> NameRefValue is declared as a string param, NameRefType is declared as
> an integer param
> Anyone have any ideas on this? Is it possible to manipulate the params
> passed into the report before the engine actually processes them? If
> this is possible I could just pass in the string without the %
> characters & based on NamRefType value, add the % in the report itself
> before the dataset is returned.
> Cheers
> Si|||Thanks Lev,
Yes that does seem to work ok. IS there a specific way I should be
encoding these characters or simply replace % with %25 ?
Cheers
Si
On Mon, 14 Feb 2005 21:16:54 -0800, "Lev Semenets [MSFT]"
<levs@.microsoft.com> wrote:
>Percent character is used to URL encode/escape other characters, so it
>should itself also be encoded.
>Check if this url works:
>http://localhost/Reportserver/MyReports/ListReport&rs:Command=Render&rs:Format=HTML4.0&rc:parameters=false&NameRefValue=%250123%25&NameRefType=3|||As a rule, I always try to limit query-string parameter values to the
actual value, and do formatting elsewhere.
Instead of passing the value %0123% via the query-string, why don't you
just pass the 0123 and add the "%" characters via an expression within
the RDL. This should completely avoid the problem you encountered with
encoding.
~Lance
http://weblogs.asp.net/lhunt/|||Thanks Lance,
Thats what I originally wanted to do but was unsure as to how to
manipulate the parameters before passing through to the stored proc.
In the end i've passed it all through as you say but direct to the
stored proc for processing & it now works fine.
I'd still be interested to know how to do it the way you mention.
Regards
Si
On 15 Feb 2005 07:02:36 -0800, "Lance" <lancehunt@.gmail.com> wrote:
>As a rule, I always try to limit query-string parameter values to the
>actual value, and do formatting elsewhere.
>Instead of passing the value %0123% via the query-string, why don't you
>just pass the 0123 and add the "%" characters via an expression within
>the RDL. This should completely avoid the problem you encountered with
>encoding.
>~Lance
>http://weblogs.asp.net/lhunt/|||I would normally recommend using the exact solution you chose, except I
wasnt sure if you were using a StoredProcedure or SQL. You definitely
should stick with your current implementation.
However, there are some cases where you can't use Stored Procedures,
such as with many ODBC connections to legacy systems. In such cases, I
recommend dynamically building your value for the LIKE expression in
the RDL.
Here are the basic steps:
1. Open "Data" tab from designer.
2. Select your DataSet from dropdown
3. Click on the "..." to go to properties.
4. Click on the Parameters tab
5. Locate the parameter in question
6. Modify the parameter value (right hand column) to use an expression.
7. Use the expression:
"="%" & Parameters!MyParam.Value & "%"
Instead of the default expression
"=Parameters!MyParam.Value"
8. Close Properties and you're ready to go!
Enjoy!
Lance Hunt
http://weblogs.asp.net/lhunt/