Dynamic Filter

Hi,
I have a Reporting Services report that I would like to add a dynmaic filter
to. I want to allow the users to filter based on two seperate fields (or
view all data). Example: if the user selects "Field1" from the filter
drop-down, I only want to display records in the dataset that have a value in
Field1 (i.e. ignore those with null values). The same would hold true if the
user would select "Field2". If the user does not select a value for this
parameter (i.e. it is blank), I don't want to limit the data.
Is this possible? I would prefer to accomplish this without using dynamic
sql.
Let me know if you have any questions...
Thanks for the help!
-David.are you using embedded sql code, or a stored procedure call?
"David" wrote:
> Hi,
> I have a Reporting Services report that I would like to add a dynmaic filter
> to. I want to allow the users to filter based on two seperate fields (or
> view all data). Example: if the user selects "Field1" from the filter
> drop-down, I only want to display records in the dataset that have a value in
> Field1 (i.e. ignore those with null values). The same would hold true if the
> user would select "Field2". If the user does not select a value for this
> parameter (i.e. it is blank), I don't want to limit the data.
> Is this possible? I would prefer to accomplish this without using dynamic
> sql.
> Let me know if you have any questions...
> Thanks for the help!
> -David.|||Embedded sql...
"Carl Henthorn" wrote:
> are you using embedded sql code, or a stored procedure call?
> "David" wrote:
> > Hi,
> >
> > I have a Reporting Services report that I would like to add a dynmaic filter
> > to. I want to allow the users to filter based on two seperate fields (or
> > view all data). Example: if the user selects "Field1" from the filter
> > drop-down, I only want to display records in the dataset that have a value in
> > Field1 (i.e. ignore those with null values). The same would hold true if the
> > user would select "Field2". If the user does not select a value for this
> > parameter (i.e. it is blank), I don't want to limit the data.
> >
> > Is this possible? I would prefer to accomplish this without using dynamic
> > sql.
> >
> > Let me know if you have any questions...
> >
> > Thanks for the help!
> >
> > -David.|||An example of your code would be helpful. when you say "Filter", are talkign
about reducing the size of your result set by using the defined parameters in
a where clause. it looks like you want to return different columns from a
table depending on which parameters are picked. Which is it? both?
if you are talking columns, use the visibility expression to hide the
unwanted columns based on the parameter value. if you are talking where
clause, then you can get around using dynamic sql by using a case stmt in the
stmt. I.e. where field1=case when @.param1<>'' then @.Param1 else field1 end
hth!
"David" wrote:
> Embedded sql...
> "Carl Henthorn" wrote:
> > are you using embedded sql code, or a stored procedure call?
> >
> > "David" wrote:
> >
> > > Hi,
> > >
> > > I have a Reporting Services report that I would like to add a dynmaic filter
> > > to. I want to allow the users to filter based on two seperate fields (or
> > > view all data). Example: if the user selects "Field1" from the filter
> > > drop-down, I only want to display records in the dataset that have a value in
> > > Field1 (i.e. ignore those with null values). The same would hold true if the
> > > user would select "Field2". If the user does not select a value for this
> > > parameter (i.e. it is blank), I don't want to limit the data.
> > >
> > > Is this possible? I would prefer to accomplish this without using dynamic
> > > sql.
> > >
> > > Let me know if you have any questions...
> > >
> > > Thanks for the help!
> > >
> > > -David.|||If a user selects one of the filter criteria, I want to only show those
records that have a value in that field. I do not want to change the columns
that are returned. In effect, it would reduce the size of the dataset.
However, I just want to filter the data not exclude it from the dataset (to
reduce round-trips to the database). Also, the report parameter will contain
the field name for the users to select from (not the value).
Example: if the user selects "Field1" from the filter
drop-down, I only want to display records in the dataset that have a value in
Field1 (i.e. not show those with null values). The same would hold true if
the
user would select "Field2". If the user does not select a value for this
parameter (i.e. it is blank/null), I want to show all records.
"Carl Henthorn" wrote:
> An example of your code would be helpful. when you say "Filter", are talkign
> about reducing the size of your result set by using the defined parameters in
> a where clause. it looks like you want to return different columns from a
> table depending on which parameters are picked. Which is it? both?
> if you are talking columns, use the visibility expression to hide the
> unwanted columns based on the parameter value. if you are talking where
> clause, then you can get around using dynamic sql by using a case stmt in the
> stmt. I.e. where field1=case when @.param1<>'' then @.Param1 else field1 end
> hth!
> "David" wrote:
> > Embedded sql...
> >
> > "Carl Henthorn" wrote:
> >
> > > are you using embedded sql code, or a stored procedure call?
> > >
> > > "David" wrote:
> > >
> > > > Hi,
> > > >
> > > > I have a Reporting Services report that I would like to add a dynmaic filter
> > > > to. I want to allow the users to filter based on two seperate fields (or
> > > > view all data). Example: if the user selects "Field1" from the filter
> > > > drop-down, I only want to display records in the dataset that have a value in
> > > > Field1 (i.e. ignore those with null values). The same would hold true if the
> > > > user would select "Field2". If the user does not select a value for this
> > > > parameter (i.e. it is blank), I don't want to limit the data.
> > > >
> > > > Is this possible? I would prefer to accomplish this without using dynamic
> > > > sql.
> > > >
> > > > Let me know if you have any questions...
> > > >
> > > > Thanks for the help!
> > > >
> > > > -David.