Report Parameter

[ggeorgiou01]

Hi all,

I have set up a report in SQL server solutions with 2 parameters, Years & Month. These parameters are NON - Queried parameters, and i have manually set them up.

At the top of each of my parameter dropdownlist i have a show all option. but this will not work. How do i unable this function to work within my report.

Do i have to setup an expression against this option ??? If so could somebody give me a helping hand.

Many Thanks,
George

 

[eksortso]

These can be tricky, but it's possible to work with them.

As far as I can see, you have two options. You could include special conditions in your dataset to handle "Show All" functionality, or you could use a dynamic query.

The first option involves using a special value in your parameter list. For instance, let's use -1 for Month to indicate "Show All Months."

Then your dataset query should include a reference to this parameter value, like so:

WHERE ([COLOR=red]@Month = -1 OR [/color]MONTH(x.ReportDate) = @Month)

(This is what it would look like in SQL. If you're defining your query with the design grid, use the Criteria columns to set this up.)

This type of query works best when you're dealing with a relatively small amount of data, because this sort of parametrization may ignore certain indices on your tables. If you have other filters that you can place in your dataset query, be sure to use them.

The second option is described in detail here:
[URL unfurl="true"]http://msdn2.microsoft.com/en-us/library/ms166931(SQL.90).aspx[/url]

Let me know how well this works out for you.