Filter Expression

Hi

I have some data that need to be filtered based on a SET of Id's.
If it's about a single ID, then i would pass it as a parameter in a
stored procedure and use it within the the WHERE Clause, but here those
ID's are determined in run time and I can't simply create a a stored
procedure for an unknown amount of ID's.
I looked into the SQL Server 8.0 Manual but had no examples how to use
the Function Filter.
Generaly, how can filter some records based on a set of ID's?

Best regardscoosa (coosa76@.gmail.com) writes:
> I have some data that need to be filtered based on a SET of Id's.
> If it's about a single ID, then i would pass it as a parameter in a
> stored procedure and use it within the the WHERE Clause, but here those
> ID's are determined in run time and I can't simply create a a stored
> procedure for an unknown amount of ID's.
> I looked into the SQL Server 8.0 Manual but had no examples how to use
> the Function Filter.
> Generaly, how can filter some records based on a set of ID's?

FROM tbl t
JOIN iter_intlist_to_tbl(@.list_of_ids, DEFAULT) f ON t.id = f.number

iter_intlist_to_tbl is table-valued function that unpacks a space-
separared list of integers into table which you then join with.

Code at http://www.sommarskog.se/arrays-in-...ist-of-integers.
The rest of the article provides more discussion about this kind of problem.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Filter by parameter value

Is this possible? I searched and couldnt find a solution that would
allow me to filter my results by a paramter value that I want the user
to be able to enter. My filtering works fine when entering in a numeric
value in manually but not when running it by the parameter. I get the
following error message: 'Fail to Evaluate
FilterExpression/FilterValues'Most likely the data types of the filter expression and the filter value
expression don't match.
Please try something similar to this:
Filter expression:
=CInt(Fields!SomeField.Value)
Filter value expression:
=CInt(Parameters!SomeParameter.Value)
The important part is the CInt() function call which will ensure that you
are comparing values of identical data types. More information on data type
conversion functions can be found on MSDN:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vagrptypeconversion.asp
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brent" <Brent.Raymond@.gmail.com> wrote in message
news:1126284534.290617.315760@.g14g2000cwa.googlegroups.com...
> Is this possible? I searched and couldnt find a solution that would
> allow me to filter my results by a paramter value that I want the user
> to be able to enter. My filtering works fine when entering in a numeric
> value in manually but not when running it by the parameter. I get the
> following error message: 'Fail to Evaluate
> FilterExpression/FilterValues'
>

Filter by "all" or actual value issue

I have a string parameter bound to a dataset that contains a "UNION Select
'(All)' " statement to provide the user a way to return all values instead of
just a single value.
I have other integer parameters that use this exact syntax for filtering
(ALL) and they work fine. The syntax looks like this and it doesn't work and
I have no idea why:
Expression: =Iif( Parameters!PropCode.Label = "All", True,
Fields!PropertyCode.Value = Parameters!PropCode.Label)
Operation: = Value: =True
Maybe it has to do with the "(" character? Maybe it has to do with it being
a string? Please advise how I might get past this issue.Did you try:
Iif( Parameters!PropCode.Label = "(All)", True,
Fields!PropertyCode.Value = Parameters!PropCode.Label)
'|||Yes. That was a typo. That is actually what the filter looks like. This
doesn't work.
"Jihong.Liu" wrote:
> Did you try:
> Iif( Parameters!PropCode.Label = "(All)", True,
> Fields!PropertyCode.Value = Parameters!PropCode.Label)
> '
>sql

Filter based on a parameter

Hi all,
How would you go about creatig a filter that is used based on the value of a
parameter? Can this be done?
Lets say we have a report parameter called 'Show cases missing <attribute>
data?'
If the user sets this to true, then the filter would kick in, which is a
filter that would check for this.field Is Nothing AND that.Field Is Nothing
OR thisother.field Is Nothing Or thatother.field Is Nothing --and show only
the rows that meet the criteria above.
I can create the filter, but I am not sure how to get it to toggle based on
the selection of the parameter by the user...is this possible?
Thanks,Do you want to filter out the data, or to hide it?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Myles" <Myles@.discussions.microsoft.com> wrote in message
news:44D54311-BA0E-40E2-A927-C875577E3CAF@.microsoft.com...
> Hi all,
> How would you go about creatig a filter that is used based on the value of
> a
> parameter? Can this be done?
> Lets say we have a report parameter called 'Show cases missing <attribute>
> data?'
> If the user sets this to true, then the filter would kick in, which is a
> filter that would check for this.field Is Nothing AND that.Field Is
> Nothing
> OR thisother.field Is Nothing Or thatother.field Is Nothing --and show
> only
> the rows that meet the criteria above.
> I can create the filter, but I am not sure how to get it to toggle based
> on
> the selection of the parameter by the user...is this possible?
> Thanks,|||Filter it out.
And yes, it should probably be done in the SP. But, can you use filters
like that? We would like to mimc all the existing crytal reports which
entails changing as little as possible (in the SP) for now. I am not worried
about the overhead data associated with this.
"Lev Semenets [MSFT]" wrote:
> Do you want to filter out the data, or to hide it?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Myles" <Myles@.discussions.microsoft.com> wrote in message
> news:44D54311-BA0E-40E2-A927-C875577E3CAF@.microsoft.com...
> > Hi all,
> >
> > How would you go about creatig a filter that is used based on the value of
> > a
> > parameter? Can this be done?
> >
> > Lets say we have a report parameter called 'Show cases missing <attribute>
> > data?'
> >
> > If the user sets this to true, then the filter would kick in, which is a
> > filter that would check for this.field Is Nothing AND that.Field Is
> > Nothing
> > OR thisother.field Is Nothing Or thatother.field Is Nothing --and show
> > only
> > the rows that meet the criteria above.
> >
> > I can create the filter, but I am not sure how to get it to toggle based
> > on
> > the selection of the parameter by the user...is this possible?
> >
> > Thanks,
>
>|||Assuming there is an Attribute parameter which represents the actual field
name you want to filter on, the filter would look similar to this:
Filter expression: =Fields(Parameters!Attribute.Value).Value is Nothing
Filter operator: =Filter value: =True
Note: I'm using the collection syntax to dynamically determine the field
name and access the fields collection based on the selected parameter value.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Myles" <Myles@.discussions.microsoft.com> wrote in message
news:24251135-8BE2-4607-B923-6F0C5749AB4A@.microsoft.com...
> Filter it out.
> And yes, it should probably be done in the SP. But, can you use filters
> like that? We would like to mimc all the existing crytal reports which
> entails changing as little as possible (in the SP) for now. I am not
> worried
> about the overhead data associated with this.
> "Lev Semenets [MSFT]" wrote:
>> Do you want to filter out the data, or to hide it?
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Myles" <Myles@.discussions.microsoft.com> wrote in message
>> news:44D54311-BA0E-40E2-A927-C875577E3CAF@.microsoft.com...
>> > Hi all,
>> >
>> > How would you go about creatig a filter that is used based on the value
>> > of
>> > a
>> > parameter? Can this be done?
>> >
>> > Lets say we have a report parameter called 'Show cases missing
>> > <attribute>
>> > data?'
>> >
>> > If the user sets this to true, then the filter would kick in, which is
>> > a
>> > filter that would check for this.field Is Nothing AND that.Field Is
>> > Nothing
>> > OR thisother.field Is Nothing Or thatother.field Is Nothing --and show
>> > only
>> > the rows that meet the criteria above.
>> >
>> > I can create the filter, but I am not sure how to get it to toggle
>> > based
>> > on
>> > the selection of the parameter by the user...is this possible?
>> >
>> > Thanks,
>>|||Thanks Robert -
no, the parameter is just a simple yes/no question and is does not directly
correlate to any single attribute or field in the report - it does indirectly
take in to consideration the Null values of three fields in the report.
"Robert Bruckner [MSFT]" wrote:
> Assuming there is an Attribute parameter which represents the actual field
> name you want to filter on, the filter would look similar to this:
> Filter expression: =Fields(Parameters!Attribute.Value).Value is Nothing
> Filter operator: => Filter value: =True
> Note: I'm using the collection syntax to dynamically determine the field
> name and access the fields collection based on the selected parameter value.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Myles" <Myles@.discussions.microsoft.com> wrote in message
> news:24251135-8BE2-4607-B923-6F0C5749AB4A@.microsoft.com...
> > Filter it out.
> >
> > And yes, it should probably be done in the SP. But, can you use filters
> > like that? We would like to mimc all the existing crytal reports which
> > entails changing as little as possible (in the SP) for now. I am not
> > worried
> > about the overhead data associated with this.
> >
> > "Lev Semenets [MSFT]" wrote:
> >
> >> Do you want to filter out the data, or to hide it?
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "Myles" <Myles@.discussions.microsoft.com> wrote in message
> >> news:44D54311-BA0E-40E2-A927-C875577E3CAF@.microsoft.com...
> >> > Hi all,
> >> >
> >> > How would you go about creatig a filter that is used based on the value
> >> > of
> >> > a
> >> > parameter? Can this be done?
> >> >
> >> > Lets say we have a report parameter called 'Show cases missing
> >> > <attribute>
> >> > data?'
> >> >
> >> > If the user sets this to true, then the filter would kick in, which is
> >> > a
> >> > filter that would check for this.field Is Nothing AND that.Field Is
> >> > Nothing
> >> > OR thisother.field Is Nothing Or thatother.field Is Nothing --and show
> >> > only
> >> > the rows that meet the criteria above.
> >> >
> >> > I can create the filter, but I am not sure how to get it to toggle
> >> > based
> >> > on
> >> > the selection of the parameter by the user...is this possible?
> >> >
> >> > Thanks,
> >>
> >>
> >>
>
>

Filter and parameter

I am using SQL Server reporting service against Oracle DB. Is there any way
I can make filters optional? I want that if user does not select any value
from filter dropdown list then the query should run only with query
parameters. In my report I have 3 query parameters and 1 filter. Filter is
using expression (=Iif(Parameters!Equip1.Value is Nothing, true,
Fields!AC_AIRCRAFT_ID.Value=Parameters!Equip1.Value)) to use one of the
report parameters(not query parameter). Now when I deploy the report, I am
forced to provide value for the filter. Please note that I have allowed null
and blank value for the report parameter I am using for this filter.
--
ThanksOpen the report in report designer and add a default value for the report
parameter Equip1, e.g. =Nothing
Before republishing to the server, you have to delete the existing report
from the server. Then you are no longer forced to provide an explicit value.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"tiwanap" <tiwanap@.discussions.microsoft.com> wrote in message
news:DB936A62-60E9-4B66-9A3F-D2D2308B6A36@.microsoft.com...
>I am using SQL Server reporting service against Oracle DB. Is there any
>way
> I can make filters optional? I want that if user does not select any value
> from filter dropdown list then the query should run only with query
> parameters. In my report I have 3 query parameters and 1 filter. Filter is
> using expression (=Iif(Parameters!Equip1.Value is Nothing, true,
> Fields!AC_AIRCRAFT_ID.Value=Parameters!Equip1.Value)) to use one of the
> report parameters(not query parameter). Now when I deploy the report, I am
> forced to provide value for the filter. Please note that I have allowed
> null
> and blank value for the report parameter I am using for this filter.
> --
> Thanks

Filter & Parameter pane missing in MDX Query Designer in SQL Reporting Services

I've posted this in the SQL Reporting Services forum as well, but thought I'd try here as some of you may be using Analysis server with SRS also. I'm developing my first Analysis Server report in SRS. Everything was going well when I discovered the filters & parameters pane was missing in the MDX Query Designer in data mode. I have my columns and rows but need to drag a dimension into the filters & parameters pane in MDX Query Designer and set a filter and parameter on it. Any idea how to make it viewable?

Additional information on this thread. The filter and parameter pane does appear in the MDX Query Designer in SQL Server Business Intelligence Development Studio on a report created from a SQL Server 2005 database, but not on a SQL Server 2000 database. Am I on to something and is there a work around for SQL Server 2000 databases?