sql server reporting services: Apply code to parameter before query execution -- sql server reporting services

Apply code to parameter before query execution

Joel Daniels
12/1/2005 7:55:03 AM

sql server reporting services:

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

Re: Apply code to parameter before query execution

Bruce L-C [MVP]
12/1/2005 10:29:54 AM

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

[quoted text, click to view]