Apply code to parameter before query execution

Hello, is there any way to perform custom calcuations on a parameter before
retrieving the data set? I have a table with an encrypted account number
field. The encryption is done at the application level using the .NET
encryption libraries. I need to prepare a report retrieving the transactions
for a particular account. Obviously, I cannot use the plain text account
number as the query parameter. I need to take the user input, encrypt it, and
then use the encrypted value as the parameter. I could perform the encryption
on one of the asp.net pages and pass the encrypted value as a url parameter,
but I would prefer to allow the users to type in the account number on the
report page itself. Is there a way to do this? (I am using SQL Server 2000
Reporting Services, not 2005) Thanks!No problem. When you tie a report parameter to a query parameter you can
first manipulate it in an expression. The fact that they are two different
things (query parameter versus report parameter) is very important.
In the dataset tab click on the ..., parameters tab. This is where the
mapping occurs. On the right instead of selecting the parameter select
expression.
Your expression would look like this:
= Code.Mycode(Parameters!MyParam.value)
You can do code behind report or you can use custom assemblies. Search books
online using the work Code.
There are hoops to go through with custom assemblies, mostly dealing with
permissions. I haven't done this myself but plenty of people have.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Joel Daniels" <JoelDaniels@.discussions.microsoft.com> wrote in message
news:DE91CBA6-67D0-4EBD-A07B-9F6B770DEFF9@.microsoft.com...
> Hello, is there any way to perform custom calcuations on a parameter
> before
> retrieving the data set? I have a table with an encrypted account number
> field. The encryption is done at the application level using the .NET
> encryption libraries. I need to prepare a report retrieving the
> transactions
> for a particular account. Obviously, I cannot use the plain text account
> number as the query parameter. I need to take the user input, encrypt it,
> and
> then use the encrypted value as the parameter. I could perform the
> encryption
> on one of the asp.net pages and pass the encrypted value as a url
> parameter,
> but I would prefer to allow the users to type in the account number on the
> report page itself. Is there a way to do this? (I am using SQL Server 2000
> Reporting Services, not 2005) Thanks!