Customize where clause c# (RDL) -- sql server reporting services

sql server reporting services : Customize where clause c# (RDL)

dgator
1/12/2006 1:12:02 PM

We have may reports that need to customize the where clause based on
parameters entered by the end user. Is there a way to modify the RDL on the
fly in order to achieve this? I am using VS2005 and SQLServer 2005.

Example:
if user selects run report by week ending date the following line needs to
be used
"and we_dt between ??? and ???
if the user selects the run the report by export date then the line changes
to:

Potter
1/12/2006 1:48:06 PM

You can handle this via expressions in the command text of the dataset.
It is much easier than modifying the RDL on the fly.

For example, you can use VB expressions in the command text to
something like this:

="SELECT field1, field2 FROM tblName WHERE " &
iif(Parameter!Param1.Value = something, "do between stuff", "don't do
between stuff")

Andy Potter

dgator
1/12/2006 2:51:02 PM

We'll look into this, but what if the entire WHERE cluase needs to be modified?

Are there any good examples of people doing this? Either through changing
the RDL or passing the entire where clause into the report?

Thanks

[quoted text, click to view]

Potter
1/13/2006 5:22:53 AM

Using expressions, your entire command text is available for
manipulation. Just do something like this:

="SELECT field1, field2 FROM tblName " & iif(Parameter!Param1.Value =
"all", "", "WHERE field=" & Parameter!Param1.Value )

Personally, I prefer this kind of logic in a stored procedure. I find
large expressions to handle string manipulation to be a bit unwieldy.
The other option to handle large string manipulation is the custom code
section of the report, which give you a little more flexibility as far
writing your string manipulation code.

Andy Potter

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server reporting services

home · blog · groups

Questions? Comments? Contact the dn