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:
"and export_dt between ? and ?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|||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
"Potter" wrote:
> 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
>|||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
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment