Dynamic filter based on parameter

I have searched and haven't found the bets way to handle this:
I have a parameter that has 1 of 2 values: sold, not sold
I want to filter out the records that appear in a table based on the user
selected value to the paramter.
It doesn't appear that I can do this in the group filter, since I can't say
something like...
if p1 = "sold' then.. etc
What is the best way to handle? Is it possible to create a nestled Iif on
the row detail to handle?
Tx
LesOk.. not sure if this is the best route, but I used a nestled Iif clause on
the row detail and it works nicely (perhaps crude, but works!):
=Iif(((Parameters!Report.Value)<>"Sold" and Fields!UNITPRCE.Value > 0),
True, (Iif(((Parameters!Report.Value)="Sold" and Fields!UNITPRCE.Value = 0),
True, False)))
Cheers!
Les
"LesWright" wrote:
> I have searched and haven't found the bets way to handle this:
> I have a parameter that has 1 of 2 values: sold, not sold
> I want to filter out the records that appear in a table based on the user
> selected value to the paramter.
> It doesn't appear that I can do this in the group filter, since I can't say
> something like...
> if p1 = "sold' then.. etc
> What is the best way to handle? Is it possible to create a nestled Iif on
> the row detail to handle?
> Tx
> Les|||On Nov 22, 11:32 am, LesWright <LesWri...@.discussions.microsoft.com>
wrote:
> It doesn't appear that I can do this in the group filter, since I can't say
> something like...
> if p1 = "sold' then.. etc
1. Pass the Report Parameter into your SQL. This will filter your
data on the server side.
SELECT * FROM ...
WHERE ( @.Report = 'Sold' AND UNITPRCE > 0 )
OR ( @.Report <> 'Sold' AND UNITPRCE = 0 )
2. Add a Calculated Field to your Dataset, then filter on the
Calculated field. Right click on the dataset, Add..., then fill in
the Expression in the Calculated field area. Then, in the Table, go
to Properties, then the Filter tab, then add an expression here. The
Dataset field is evaluated when the data is pulled, and I believed
cached with the data. This is faster than filtering row by row.
3. Do what you did and put an Expression on the Visible property. I
find this to be the slowest, since it has to go through every row in
the dataset at render time, but it gives you the most control.
-- Scott|||Scott,
Thanks for your post. That is definately a much better and more efficient
method! I was reusing the same sproc for a number of reports and once
completed, didn't even think to add another parameter.
Tx
Les
"Orne" wrote:
> On Nov 22, 11:32 am, LesWright <LesWri...@.discussions.microsoft.com>
> wrote:
> > It doesn't appear that I can do this in the group filter, since I can't say
> > something like...
> >
> > if p1 = "sold' then.. etc
> 1. Pass the Report Parameter into your SQL. This will filter your
> data on the server side.
> SELECT * FROM ...
> WHERE ( @.Report = 'Sold' AND UNITPRCE > 0 )
> OR ( @.Report <> 'Sold' AND UNITPRCE = 0 )
> 2. Add a Calculated Field to your Dataset, then filter on the
> Calculated field. Right click on the dataset, Add..., then fill in
> the Expression in the Calculated field area. Then, in the Table, go
> to Properties, then the Filter tab, then add an expression here. The
> Dataset field is evaluated when the data is pulled, and I believed
> cached with the data. This is faster than filtering row by row.
> 3. Do what you did and put an Expression on the Visible property. I
> find this to be the slowest, since it has to go through every row in
> the dataset at render time, but it gives you the most control.
> -- Scott
>