I am dealing with a set of 5 parameters. I want to filter the data based on
these parameters using the 'or' condition instead of an 'And'. In the filter
tab the condition defaults to an 'And' and is greyed out. How do I change it
to use an 'Or' condition.You can rewrite filter to use singe expression that combines all parameters
instead of 5 separate expressions.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sumi" <Sumi@.discussions.microsoft.com> wrote in message
news:9BD59230-0A07-4AA3-9E58-4F11640D3D4A@.microsoft.com...
>I am dealing with a set of 5 parameters. I want to filter the data based on
> these parameters using the 'or' condition instead of an 'And'. In the
> filter
> tab the condition defaults to an 'And' and is greyed out. How do I change
> it
> to use an 'Or' condition.|||Combine the in one expression
fe
filter-expresion:
Switch( Parameters!par1.Value = x or Parameters!par2.Value = y, 1, true, 0)
filter-value:
=1
"Sumi" wrote:
> I am dealing with a set of 5 parameters. I want to filter the data based on
> these parameters using the 'or' condition instead of an 'And'. In the filter
> tab the condition defaults to an 'And' and is greyed out. How do I change it
> to use an 'Or' condition.|||Thank you very much! This made things very clear.
"Antoon" wrote:
> Combine the in one expression
> fe
> filter-expresion:
> Switch( Parameters!par1.Value = x or Parameters!par2.Value = y, 1, true, 0)
> filter-value:
> =1
> "Sumi" wrote:
> > I am dealing with a set of 5 parameters. I want to filter the data based on
> > these parameters using the 'or' condition instead of an 'And'. In the filter
> > tab the condition defaults to an 'And' and is greyed out. How do I change it
> > to use an 'Or' condition.|||Antoon,
Thanks a bundle.I tried this and it works wonderfully. But this has
triggered off another question.
I have a stored proc that takes in Startdate and Enddate as two parameters.
Then I have a set of 5 parameters which I am using to further filter the
data, this is to give the user flexibility to choose whichever parameter they
want. These parameters have a drodown box which has all the values they can
select along with a blank row, incase the user does not want to use that
parameter to filter.
Using the expression described below, I can filter by one or more
parameters, but if I select blank for all the 5 parameter, I was expecting
the expression to be ignored and the result set to have all data for the
entered Startdate and EndDate. Which are not a part of this filter
expression. But the report pulls up blank.
Any ideas as to how to fix this?
filter-expresion:
Switch( Parameters!par1.Value = x or Parameters!par2.Value = y, 1, true, 0)
filter-value:
=1
"Antoon" wrote:
> Combine the in one expression
> fe
> filter-expresion:
> Switch( Parameters!par1.Value = x or Parameters!par2.Value = y, 1, true, 0)
> filter-value:
> =1
> "Sumi" wrote:
> > I am dealing with a set of 5 parameters. I want to filter the data based on
> > these parameters using the 'or' condition instead of an 'And'. In the filter
> > tab the condition defaults to an 'And' and is greyed out. How do I change it
> > to use an 'Or' condition.|||I use a specific value for the all parameter
Select parname, parvalue from parlist
union
select '<All>', -1 from dual
You can then filter in the query
Select ...
where
... (table.value = :parameter or :paremeter = -1)
or you can change the filter, and add
Parameters!par1.Value = -1, 1
to the switch statement
(I work in oracle)
"Sumi" wrote:
> Antoon,
> Thanks a bundle.I tried this and it works wonderfully. But this has
> triggered off another question.
> I have a stored proc that takes in Startdate and Enddate as two parameters.
> Then I have a set of 5 parameters which I am using to further filter the
> data, this is to give the user flexibility to choose whichever parameter they
> want. These parameters have a drodown box which has all the values they can
> select along with a blank row, incase the user does not want to use that
> parameter to filter.
> Using the expression described below, I can filter by one or more
> parameters, but if I select blank for all the 5 parameter, I was expecting
> the expression to be ignored and the result set to have all data for the
> entered Startdate and EndDate. Which are not a part of this filter
> expression. But the report pulls up blank.
> Any ideas as to how to fix this?
> filter-expresion:
> Switch( Parameters!par1.Value = x or Parameters!par2.Value = y, 1, true, 0)
> filter-value:
> =1
>
> "Antoon" wrote:
> > Combine the in one expression
> > fe
> > filter-expresion:
> > Switch( Parameters!par1.Value = x or Parameters!par2.Value = y, 1, true, 0)
> > filter-value:
> > =1
> > "Sumi" wrote:
> >
> > > I am dealing with a set of 5 parameters. I want to filter the data based on
> > > these parameters using the 'or' condition instead of an 'And'. In the filter
> > > tab the condition defaults to an 'And' and is greyed out. How do I change it
> > > to use an 'Or' condition.|||The only way I've achieved any 'clever' from parameters regardin
optional parameters ands and ors is by building up a where string o
the fly
DECLARE @.select as varchar(100
DECLARE @.where as varchar(100
DECLARE @.sql as varchar(100
SET @.select = 'SELECT * FROM poext WHERE '
SET @.where = ''
IF @.vendor <> 'not set' SET @.where = @.where + 'vendor = '''
@.vendor + ''''
IF len(@.where) > 0 SET @.where = @.where + ' and '
IF @.buyer <> - 1 SET @.where = @.where + 'buyer = '
cast(@.buyer as varchar(5))
if len(@.where)>0 and right(@.where,5) <> ' an
' SET @.where = @.where + ' and '
set @.where = @.where + 'orddate between '''+@.orderdatefrom + ''' an
''' + @.orderdateto + ''''
SET @.sql = @.select + @.where
EXEC sp_executesql @.sq
And use that as my dataset query. This works fine, although you hav
to refresh it a few times or the preview doesn't work, it tends t
get a little confused as it has no idea about the data you're pullin
until it's got it.[/code
Showing posts with label filter. Show all posts
Showing posts with label filter. Show all posts
Sunday, February 19, 2012
Filters Top 5 showing more than 5 if a tie
I have a filter with the top 5, and if I have 10 items with the same top
value, it will show all 10. Is there anyway to cut this off show it just
shows 5?Hi,
Maybe using the distinct operator in your query?
SELECT DISTINCT TOP 5 col1
FROM table1
HTH,
Eric|||Why not try using the TOP N in the SQL instead of the filter.. By default
SQL will NOT include duplicates unless you include TOP N WITH TIES...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Cindy Lee" <cindylee@.hotmail.com> wrote in message
news:eUf3l%23m%23EHA.4092@.TK2MSFTNGP09.phx.gbl...
> I have a filter with the top 5, and if I have 10 items with the same top
> value, it will show all 10. Is there anyway to cut this off show it just
> shows 5?
>|||Problem I'm using MDX queries and want to limit the number of queries.
I have a Top 5/ Show all toggle button. So I need all results back from the
query.
Using 2 queries kills me timewise.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OAacjNo%23EHA.2192@.TK2MSFTNGP14.phx.gbl...
> Why not try using the TOP N in the SQL instead of the filter.. By default
> SQL will NOT include duplicates unless you include TOP N WITH TIES...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Cindy Lee" <cindylee@.hotmail.com> wrote in message
> news:eUf3l%23m%23EHA.4092@.TK2MSFTNGP09.phx.gbl...
> > I have a filter with the top 5, and if I have 10 items with the same top
> > value, it will show all 10. Is there anyway to cut this off show it
just
> > shows 5?
> >
> >
>
value, it will show all 10. Is there anyway to cut this off show it just
shows 5?Hi,
Maybe using the distinct operator in your query?
SELECT DISTINCT TOP 5 col1
FROM table1
HTH,
Eric|||Why not try using the TOP N in the SQL instead of the filter.. By default
SQL will NOT include duplicates unless you include TOP N WITH TIES...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Cindy Lee" <cindylee@.hotmail.com> wrote in message
news:eUf3l%23m%23EHA.4092@.TK2MSFTNGP09.phx.gbl...
> I have a filter with the top 5, and if I have 10 items with the same top
> value, it will show all 10. Is there anyway to cut this off show it just
> shows 5?
>|||Problem I'm using MDX queries and want to limit the number of queries.
I have a Top 5/ Show all toggle button. So I need all results back from the
query.
Using 2 queries kills me timewise.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OAacjNo%23EHA.2192@.TK2MSFTNGP14.phx.gbl...
> Why not try using the TOP N in the SQL instead of the filter.. By default
> SQL will NOT include duplicates unless you include TOP N WITH TIES...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Cindy Lee" <cindylee@.hotmail.com> wrote in message
> news:eUf3l%23m%23EHA.4092@.TK2MSFTNGP09.phx.gbl...
> > I have a filter with the top 5, and if I have 10 items with the same top
> > value, it will show all 10. Is there anyway to cut this off show it
just
> > shows 5?
> >
> >
>
Filters in combination with the aggregate function
Hi all,
I have a report and use the aggregate function in a table. Now I want
to show the top 50 from that table. But when I use a filter on a group
and use the top N function i get a error message:
The aggregate function "Aggregate" cannot be used in a report that
contains any filters.
How can i filter the top 50 with aggregate functions in a report?
Regards,
Robbert
HollandIn your dataset you can use your select statement.
Select Top(50) Field1
From Sometable
Group By Field1
That will set the whole dataset to show only your top 50. THen you can
graph it or do whatever you want with those records.
<robbert_visscher@.hotmail.com> wrote in message
news:1155037378.956732.155110@.i3g2000cwc.googlegroups.com...
> Hi all,
> I have a report and use the aggregate function in a table. Now I want
> to show the top 50 from that table. But when I use a filter on a group
> and use the top N function i get a error message:
> The aggregate function "Aggregate" cannot be used in a report that
> contains any filters.
> How can i filter the top 50 with aggregate functions in a report?
> Regards,
> Robbert
> Holland
>|||My query is on a Cube. If I use top 50 it is not working. Now i have
used topcount to select the top 50 but is it not possible to do een top
50 in a report with aggregate functions? If not, why?
I have a report and use the aggregate function in a table. Now I want
to show the top 50 from that table. But when I use a filter on a group
and use the top N function i get a error message:
The aggregate function "Aggregate" cannot be used in a report that
contains any filters.
How can i filter the top 50 with aggregate functions in a report?
Regards,
Robbert
HollandIn your dataset you can use your select statement.
Select Top(50) Field1
From Sometable
Group By Field1
That will set the whole dataset to show only your top 50. THen you can
graph it or do whatever you want with those records.
<robbert_visscher@.hotmail.com> wrote in message
news:1155037378.956732.155110@.i3g2000cwc.googlegroups.com...
> Hi all,
> I have a report and use the aggregate function in a table. Now I want
> to show the top 50 from that table. But when I use a filter on a group
> and use the top N function i get a error message:
> The aggregate function "Aggregate" cannot be used in a report that
> contains any filters.
> How can i filter the top 50 with aggregate functions in a report?
> Regards,
> Robbert
> Holland
>|||My query is on a Cube. If I use top 50 it is not working. Now i have
used topcount to select the top 50 but is it not possible to do een top
50 in a report with aggregate functions? If not, why?
Filters for dataset from Parameter - How to ignore if the parameter is NULL ?
Hi,
I am starting to use reporting services and I created a report that takes 4 parameters for a Filter on the dataset.
The idea is the create snapshot nightly and then display the report filtered based on parameters to users.
I'd like that the filter be ignored for a parameter if the parameter is passed as NULL,
Right now I defined it this way :
Left =Fields!RegionCode.Value Operator = Right =IIF(IsNothing(Parameters!RegionCode.Value),Fields!RegionCode.Value,Parameters!RegionCode.Value)I do this for the 4 parameters that are passed, if they are null, make then equals to the field.
I was wondering if there is a way to ignore the whole parameter all together, I would guess it'll be faster to execute.
Thanks
One way would be to use this in your stored procedure or query, with coalesce...
eg. AND COALESCE(@.RegionCode,[RegionCode]) = [RegionCode]
That way you're not bringing the data into the report and then filtering it out, you're filtering at the source.
|||No I don't want to do that,
The idea is the get ALL the data in a snapshot nightly then apply filters on the snapshot when we display to the users.
The result is that the DB will not get hammered everytime a report is run but only once at night.
So the coalesce will not work.
|||I guess you could do something then with the left side of the filter and an if statement?
If the parameter on the left side is null, then set it to 1, otherwise set it to the field. If the parameter on the right side is null, set it to 1, otherwise set it to the parameter. Then 1=1 and it will ignore the parameter.
Not sure if this will work but it sounds faster than comparing field to field in theory.
cheers,
Andrew
|||
Andrew - T4G wrote:
I guess you could do something then with the left side of the filter and an if statement?
If the parameter on the left side is null, then set it to 1, otherwise set it to the field. If the parameter on the right side is null, set it to 1, otherwise set it to the parameter. Then 1=1 and it will ignore the parameter.
Not sure if this will work but it sounds faster than comparing field to field in theory.
cheers,
Andrew
Thanks, I'll try something like that.
Filters Date
Hi:
How do I filter the output based on a date. I tried with CDate, without
CDate, with quotes, without quotes, putting time in the date. Nothing seems
to work. keep getting this error
Expression: =CDate(Fields!ISSUEDDATE.Value)
Value: CDate('1/1/2004')
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
The processing of filter expression for the table 'table1' cannot be
performed. The comparison failed. Please check the data type returned by
filter expression. (rsProcessingError) Get Online Help
Please suggest the write combination.
ThanksWhen doing expressions you need to use VB classes/methods. In particular
what you are looking for is the convert class.
The following will work:
expression Operator Value
=Fields!datetime.Value >
=convert.ToDatetime("9/17/2004 00:00:00")
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"NI" <NI@.discussions.microsoft.com> wrote in message
news:29C16728-4FD7-47E3-9B83-D4D5A71AA990@.microsoft.com...
> Hi:
> How do I filter the output based on a date. I tried with CDate, without
> CDate, with quotes, without quotes, putting time in the date. Nothing
seems
> to work. keep getting this error
> Expression: =CDate(Fields!ISSUEDDATE.Value)
> Value: CDate('1/1/2004')
>
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> The processing of filter expression for the table 'table1' cannot be
> performed. The comparison failed. Please check the data type returned by
> filter expression. (rsProcessingError) Get Online Help
> Please suggest the write combination.
> Thanks
>
>
>|||Thanks Bruce. That worked :)
"Bruce L-C [MVP]" wrote:
> When doing expressions you need to use VB classes/methods. In particular
> what you are looking for is the convert class.
> The following will work:
> expression Operator Value
> =Fields!datetime.Value >
> =convert.ToDatetime("9/17/2004 00:00:00")
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "NI" <NI@.discussions.microsoft.com> wrote in message
> news:29C16728-4FD7-47E3-9B83-D4D5A71AA990@.microsoft.com...
> > Hi:
> >
> > How do I filter the output based on a date. I tried with CDate, without
> > CDate, with quotes, without quotes, putting time in the date. Nothing
> seems
> > to work. keep getting this error
> >
> > Expression: =CDate(Fields!ISSUEDDATE.Value)
> > Value: CDate('1/1/2004')
> >
> >
> > An error has occurred during report processing. (rsProcessingAborted) Get
> > Online Help
> > The processing of filter expression for the table 'table1' cannot be
> > performed. The comparison failed. Please check the data type returned by
> > filter expression. (rsProcessingError) Get Online Help
> >
> > Please suggest the write combination.
> >
> > Thanks
> >
> >
> >
> >
> >
> >
>
>|||Bruce, that worked for me too.
However, I am trying to pass in parameter @.Start_Date.
I have tried the following and I cannot get it to work.
convert.ToDatetime("@.Start_Date 00:00:00")
convert.ToDatetime(@.Start_Date + " 00:00:00")
convert.ToDatetime(Start_Date + " 00:00:00")
Any ideas?
Thanks.
"Bruce L-C [MVP]" wrote:
> When doing expressions you need to use VB classes/methods. In particular
> what you are looking for is the convert class.
> The following will work:
> expression Operator Value
> =Fields!datetime.Value >
> =convert.ToDatetime("9/17/2004 00:00:00")
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "NI" <NI@.discussions.microsoft.com> wrote in message
> news:29C16728-4FD7-47E3-9B83-D4D5A71AA990@.microsoft.com...
> > Hi:
> >
> > How do I filter the output based on a date. I tried with CDate, without
> > CDate, with quotes, without quotes, putting time in the date. Nothing
> seems
> > to work. keep getting this error
> >
> > Expression: =CDate(Fields!ISSUEDDATE.Value)
> > Value: CDate('1/1/2004')
> >
> >
> > An error has occurred during report processing. (rsProcessingAborted) Get
> > Online Help
> > The processing of filter expression for the table 'table1' cannot be
> > performed. The comparison failed. Please check the data type returned by
> > filter expression. (rsProcessingError) Get Online Help
> >
> > Please suggest the write combination.
> >
> > Thanks
> >
> >
> >
> >
> >
> >
>
>
How do I filter the output based on a date. I tried with CDate, without
CDate, with quotes, without quotes, putting time in the date. Nothing seems
to work. keep getting this error
Expression: =CDate(Fields!ISSUEDDATE.Value)
Value: CDate('1/1/2004')
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
The processing of filter expression for the table 'table1' cannot be
performed. The comparison failed. Please check the data type returned by
filter expression. (rsProcessingError) Get Online Help
Please suggest the write combination.
ThanksWhen doing expressions you need to use VB classes/methods. In particular
what you are looking for is the convert class.
The following will work:
expression Operator Value
=Fields!datetime.Value >
=convert.ToDatetime("9/17/2004 00:00:00")
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"NI" <NI@.discussions.microsoft.com> wrote in message
news:29C16728-4FD7-47E3-9B83-D4D5A71AA990@.microsoft.com...
> Hi:
> How do I filter the output based on a date. I tried with CDate, without
> CDate, with quotes, without quotes, putting time in the date. Nothing
seems
> to work. keep getting this error
> Expression: =CDate(Fields!ISSUEDDATE.Value)
> Value: CDate('1/1/2004')
>
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> The processing of filter expression for the table 'table1' cannot be
> performed. The comparison failed. Please check the data type returned by
> filter expression. (rsProcessingError) Get Online Help
> Please suggest the write combination.
> Thanks
>
>
>|||Thanks Bruce. That worked :)
"Bruce L-C [MVP]" wrote:
> When doing expressions you need to use VB classes/methods. In particular
> what you are looking for is the convert class.
> The following will work:
> expression Operator Value
> =Fields!datetime.Value >
> =convert.ToDatetime("9/17/2004 00:00:00")
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "NI" <NI@.discussions.microsoft.com> wrote in message
> news:29C16728-4FD7-47E3-9B83-D4D5A71AA990@.microsoft.com...
> > Hi:
> >
> > How do I filter the output based on a date. I tried with CDate, without
> > CDate, with quotes, without quotes, putting time in the date. Nothing
> seems
> > to work. keep getting this error
> >
> > Expression: =CDate(Fields!ISSUEDDATE.Value)
> > Value: CDate('1/1/2004')
> >
> >
> > An error has occurred during report processing. (rsProcessingAborted) Get
> > Online Help
> > The processing of filter expression for the table 'table1' cannot be
> > performed. The comparison failed. Please check the data type returned by
> > filter expression. (rsProcessingError) Get Online Help
> >
> > Please suggest the write combination.
> >
> > Thanks
> >
> >
> >
> >
> >
> >
>
>|||Bruce, that worked for me too.
However, I am trying to pass in parameter @.Start_Date.
I have tried the following and I cannot get it to work.
convert.ToDatetime("@.Start_Date 00:00:00")
convert.ToDatetime(@.Start_Date + " 00:00:00")
convert.ToDatetime(Start_Date + " 00:00:00")
Any ideas?
Thanks.
"Bruce L-C [MVP]" wrote:
> When doing expressions you need to use VB classes/methods. In particular
> what you are looking for is the convert class.
> The following will work:
> expression Operator Value
> =Fields!datetime.Value >
> =convert.ToDatetime("9/17/2004 00:00:00")
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "NI" <NI@.discussions.microsoft.com> wrote in message
> news:29C16728-4FD7-47E3-9B83-D4D5A71AA990@.microsoft.com...
> > Hi:
> >
> > How do I filter the output based on a date. I tried with CDate, without
> > CDate, with quotes, without quotes, putting time in the date. Nothing
> seems
> > to work. keep getting this error
> >
> > Expression: =CDate(Fields!ISSUEDDATE.Value)
> > Value: CDate('1/1/2004')
> >
> >
> > An error has occurred during report processing. (rsProcessingAborted) Get
> > Online Help
> > The processing of filter expression for the table 'table1' cannot be
> > performed. The comparison failed. Please check the data type returned by
> > filter expression. (rsProcessingError) Get Online Help
> >
> > Please suggest the write combination.
> >
> > Thanks
> >
> >
> >
> >
> >
> >
>
>
filters and charts
Hi,
i am trying to filter the same data i have to two charts, each chart will
count the number of cases submiotted by each person.
Chart 1 to show all those with more than 1 case
chart 2 to show all those with 1 case or less
Each time i try and use the filter tabs i get errors. Would it be best to
have different datasets for each of the charts and let the sql do the
filtering? I would rather have one dataset and have all my charts come from
that.
Any help would be greatly appreciatedIn the value field of the filter, make sure you start it with =
Chris
Shaun Longhurst wrote:
> Hi,
> i am trying to filter the same data i have to two charts, each chart
> will count the number of cases submiotted by each person.
> Chart 1 to show all those with more than 1 case
> chart 2 to show all those with 1 case or less
> Each time i try and use the filter tabs i get errors. Would it be
> best to have different datasets for each of the charts and let the
> sql do the filtering? I would rather have one dataset and have all my
> charts come from that.
> Any help would be greatly appreciated|||thanks for responding. Could you explain further?
I have tried
Fields.User then = then = count(fields!app_id.value) > 1
This is in the filter on the chart properties.
But keep getting cant use an aggregate in a data region.
"Chris McGuigan" wrote:
> In the value field of the filter, make sure you start it with => Chris
>
> Shaun Longhurst wrote:
> > Hi,
> >
> > i am trying to filter the same data i have to two charts, each chart
> > will count the number of cases submiotted by each person.
> >
> > Chart 1 to show all those with more than 1 case
> > chart 2 to show all those with 1 case or less
> >
> > Each time i try and use the filter tabs i get errors. Would it be
> > best to have different datasets for each of the charts and let the
> > sql do the filtering? I would rather have one dataset and have all my
> > charts come from that.
> >
> > Any help would be greatly appreciated
>|||Thanks for responding, could you explain further?
I am assuming you mean in the filter box on the chart properties. I have
done this like so
Fields!User then = then =count(fields!app_id) > 1
But keep getting an error saying cant use an aggregate in this filter
Thanks
"Chris McGuigan" wrote:
> In the value field of the filter, make sure you start it with => Chris
>
> Shaun Longhurst wrote:
> > Hi,
> >
> > i am trying to filter the same data i have to two charts, each chart
> > will count the number of cases submiotted by each person.
> >
> > Chart 1 to show all those with more than 1 case
> > chart 2 to show all those with 1 case or less
> >
> > Each time i try and use the filter tabs i get errors. Would it be
> > best to have different datasets for each of the charts and let the
> > sql do the filtering? I would rather have one dataset and have all my
> > charts come from that.
> >
> > Any help would be greatly appreciated
>|||Looks like i have found the answer, i tried the = sign in the catergory types
filter and it has done the job.
Thanks for the pointer
"Chris McGuigan" wrote:
> In the value field of the filter, make sure you start it with => Chris
>
> Shaun Longhurst wrote:
> > Hi,
> >
> > i am trying to filter the same data i have to two charts, each chart
> > will count the number of cases submiotted by each person.
> >
> > Chart 1 to show all those with more than 1 case
> > chart 2 to show all those with 1 case or less
> >
> > Each time i try and use the filter tabs i get errors. Would it be
> > best to have different datasets for each of the charts and let the
> > sql do the filtering? I would rather have one dataset and have all my
> > charts come from that.
> >
> > Any help would be greatly appreciated
>
i am trying to filter the same data i have to two charts, each chart will
count the number of cases submiotted by each person.
Chart 1 to show all those with more than 1 case
chart 2 to show all those with 1 case or less
Each time i try and use the filter tabs i get errors. Would it be best to
have different datasets for each of the charts and let the sql do the
filtering? I would rather have one dataset and have all my charts come from
that.
Any help would be greatly appreciatedIn the value field of the filter, make sure you start it with =
Chris
Shaun Longhurst wrote:
> Hi,
> i am trying to filter the same data i have to two charts, each chart
> will count the number of cases submiotted by each person.
> Chart 1 to show all those with more than 1 case
> chart 2 to show all those with 1 case or less
> Each time i try and use the filter tabs i get errors. Would it be
> best to have different datasets for each of the charts and let the
> sql do the filtering? I would rather have one dataset and have all my
> charts come from that.
> Any help would be greatly appreciated|||thanks for responding. Could you explain further?
I have tried
Fields.User then = then = count(fields!app_id.value) > 1
This is in the filter on the chart properties.
But keep getting cant use an aggregate in a data region.
"Chris McGuigan" wrote:
> In the value field of the filter, make sure you start it with => Chris
>
> Shaun Longhurst wrote:
> > Hi,
> >
> > i am trying to filter the same data i have to two charts, each chart
> > will count the number of cases submiotted by each person.
> >
> > Chart 1 to show all those with more than 1 case
> > chart 2 to show all those with 1 case or less
> >
> > Each time i try and use the filter tabs i get errors. Would it be
> > best to have different datasets for each of the charts and let the
> > sql do the filtering? I would rather have one dataset and have all my
> > charts come from that.
> >
> > Any help would be greatly appreciated
>|||Thanks for responding, could you explain further?
I am assuming you mean in the filter box on the chart properties. I have
done this like so
Fields!User then = then =count(fields!app_id) > 1
But keep getting an error saying cant use an aggregate in this filter
Thanks
"Chris McGuigan" wrote:
> In the value field of the filter, make sure you start it with => Chris
>
> Shaun Longhurst wrote:
> > Hi,
> >
> > i am trying to filter the same data i have to two charts, each chart
> > will count the number of cases submiotted by each person.
> >
> > Chart 1 to show all those with more than 1 case
> > chart 2 to show all those with 1 case or less
> >
> > Each time i try and use the filter tabs i get errors. Would it be
> > best to have different datasets for each of the charts and let the
> > sql do the filtering? I would rather have one dataset and have all my
> > charts come from that.
> >
> > Any help would be greatly appreciated
>|||Looks like i have found the answer, i tried the = sign in the catergory types
filter and it has done the job.
Thanks for the pointer
"Chris McGuigan" wrote:
> In the value field of the filter, make sure you start it with => Chris
>
> Shaun Longhurst wrote:
> > Hi,
> >
> > i am trying to filter the same data i have to two charts, each chart
> > will count the number of cases submiotted by each person.
> >
> > Chart 1 to show all those with more than 1 case
> > chart 2 to show all those with 1 case or less
> >
> > Each time i try and use the filter tabs i get errors. Would it be
> > best to have different datasets for each of the charts and let the
> > sql do the filtering? I would rather have one dataset and have all my
> > charts come from that.
> >
> > Any help would be greatly appreciated
>
Filters - need help
I am having issues applying a filter to my report.
I have one dataset that i want to filter and display serveral times in one
report.
The query is complicated and somewhat slow(yes i am working on optimizing
it), so i want to pull it once, and then use it in 4 different tables in my
report.
The first table, will be unfiltered, the others will be filtered by one of
the fields in my dataset that returns an int datatype, none of the values are
null.
I tried following the instructions here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_3diq.asp,
but it doesn't work.
When i preview the report, it returns no build errors or warnings, but i get
this dialog:
Processing Errors
An error has occurred during the report processing.
The processing of filter expression for the table 'table1' cannot be
performed. The comparison failed. Please check the data type returned by the
filter expression.
----
What am i doing wrong?
This is driving me nuts...
--
RyanWhat is the filtering expression?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ryan" <Ryan@.discussions.microsoft.com> wrote in message
news:0CE890FC-4EAF-4BA6-8ADC-D4C6DBBE4B5F@.microsoft.com...
>I am having issues applying a filter to my report.
> I have one dataset that i want to filter and display serveral times in one
> report.
> The query is complicated and somewhat slow(yes i am working on optimizing
> it), so i want to pull it once, and then use it in 4 different tables in
> my
> report.
> The first table, will be unfiltered, the others will be filtered by one of
> the fields in my dataset that returns an int datatype, none of the values
> are
> null.
> I tried following the instructions here:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_3diq.asp,
> but it doesn't work.
> When i preview the report, it returns no build errors or warnings, but i
> get
> this dialog:
> Processing Errors
> An error has occurred during the report processing.
> The processing of filter expression for the table 'table1' cannot be
> performed. The comparison failed. Please check the data type returned by
> the
> filter expression.
> ----
> What am i doing wrong?
> This is driving me nuts...
> --
> Ryan|||Expression: Fields!ProcessStage.Value>=80
I think i found part of my issue.
When you select a field from the drop down in Table
Properties->Filters/Expressions, it leaves a "=" at the beginning. By
removing that, i no longer get the error. But it does not seem to filter the
data either...
"Lev Semenets [MSFT]" wrote:
> What is the filtering expression?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Ryan" <Ryan@.discussions.microsoft.com> wrote in message
> news:0CE890FC-4EAF-4BA6-8ADC-D4C6DBBE4B5F@.microsoft.com...
> >I am having issues applying a filter to my report.
> >
> > I have one dataset that i want to filter and display serveral times in one
> > report.
> > The query is complicated and somewhat slow(yes i am working on optimizing
> > it), so i want to pull it once, and then use it in 4 different tables in
> > my
> > report.
> > The first table, will be unfiltered, the others will be filtered by one of
> > the fields in my dataset that returns an int datatype, none of the values
> > are
> > null.
> >
> > I tried following the instructions here:
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_3diq.asp,
> > but it doesn't work.
> >
> > When i preview the report, it returns no build errors or warnings, but i
> > get
> > this dialog:
> > Processing Errors
> > An error has occurred during the report processing.
> > The processing of filter expression for the table 'table1' cannot be
> > performed. The comparison failed. Please check the data type returned by
> > the
> > filter expression.
> > ----
> > What am i doing wrong?
> > This is driving me nuts...
> > --
> > Ryan
>
>|||Ok;
I figured i out, picky little application:
Expression: =Fields!ProcessStage.Value
Operator: >=Value: =80
Thanks...
"Ryan" wrote:
> Expression: Fields!ProcessStage.Value>=80
> I think i found part of my issue.
> When you select a field from the drop down in Table
> Properties->Filters/Expressions, it leaves a "=" at the beginning. By
> removing that, i no longer get the error. But it does not seem to filter the
> data either...
>
> "Lev Semenets [MSFT]" wrote:
> > What is the filtering expression?
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "Ryan" <Ryan@.discussions.microsoft.com> wrote in message
> > news:0CE890FC-4EAF-4BA6-8ADC-D4C6DBBE4B5F@.microsoft.com...
> > >I am having issues applying a filter to my report.
> > >
> > > I have one dataset that i want to filter and display serveral times in one
> > > report.
> > > The query is complicated and somewhat slow(yes i am working on optimizing
> > > it), so i want to pull it once, and then use it in 4 different tables in
> > > my
> > > report.
> > > The first table, will be unfiltered, the others will be filtered by one of
> > > the fields in my dataset that returns an int datatype, none of the values
> > > are
> > > null.
> > >
> > > I tried following the instructions here:
> > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_3diq.asp,
> > > but it doesn't work.
> > >
> > > When i preview the report, it returns no build errors or warnings, but i
> > > get
> > > this dialog:
> > > Processing Errors
> > > An error has occurred during the report processing.
> > > The processing of filter expression for the table 'table1' cannot be
> > > performed. The comparison failed. Please check the data type returned by
> > > the
> > > filter expression.
> > > ----
> > > What am i doing wrong?
> > > This is driving me nuts...
> > > --
> > > Ryan
> >
> >
> >|||I tore my hair out for about 24 hours until I found this post.. Thanks
Ryan - likely I would be tearing my hair out for days more had I not
happened on this. The nest question is when will someone from MS get
around to addressing this "feature."|||Thanks for the post Ryan...you bet the application is really picky...I hope
the new version is really fine tuned in such areas
"Ryan" wrote:
> Ok;
> I figured i out, picky little application:
> Expression: =Fields!ProcessStage.Value
> Operator: >=> Value: =80
> Thanks...
> "Ryan" wrote:
> > Expression: Fields!ProcessStage.Value>=80
> >
> > I think i found part of my issue.
> > When you select a field from the drop down in Table
> > Properties->Filters/Expressions, it leaves a "=" at the beginning. By
> > removing that, i no longer get the error. But it does not seem to filter the
> > data either...
> >
> >
> > "Lev Semenets [MSFT]" wrote:
> >
> > > What is the filtering expression?
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > >
> > >
> > > "Ryan" <Ryan@.discussions.microsoft.com> wrote in message
> > > news:0CE890FC-4EAF-4BA6-8ADC-D4C6DBBE4B5F@.microsoft.com...
> > > >I am having issues applying a filter to my report.
> > > >
> > > > I have one dataset that i want to filter and display serveral times in one
> > > > report.
> > > > The query is complicated and somewhat slow(yes i am working on optimizing
> > > > it), so i want to pull it once, and then use it in 4 different tables in
> > > > my
> > > > report.
> > > > The first table, will be unfiltered, the others will be filtered by one of
> > > > the fields in my dataset that returns an int datatype, none of the values
> > > > are
> > > > null.
> > > >
> > > > I tried following the instructions here:
> > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_3diq.asp,
> > > > but it doesn't work.
> > > >
> > > > When i preview the report, it returns no build errors or warnings, but i
> > > > get
> > > > this dialog:
> > > > Processing Errors
> > > > An error has occurred during the report processing.
> > > > The processing of filter expression for the table 'table1' cannot be
> > > > performed. The comparison failed. Please check the data type returned by
> > > > the
> > > > filter expression.
> > > > ----
> > > > What am i doing wrong?
> > > > This is driving me nuts...
> > > > --
> > > > Ryan
> > >
> > >
> > >|||It is so helpful. Thanks!!!!!
"Ryan" wrote:
> Ok;
> I figured i out, picky little application:
> Expression: =Fields!ProcessStage.Value
> Operator: >=> Value: =80
> Thanks...
> "Ryan" wrote:
> > Expression: Fields!ProcessStage.Value>=80
> >
> > I think i found part of my issue.
> > When you select a field from the drop down in Table
> > Properties->Filters/Expressions, it leaves a "=" at the beginning. By
> > removing that, i no longer get the error. But it does not seem to filter the
> > data either...
> >
> >
> > "Lev Semenets [MSFT]" wrote:
> >
> > > What is the filtering expression?
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > >
> > >
> > > "Ryan" <Ryan@.discussions.microsoft.com> wrote in message
> > > news:0CE890FC-4EAF-4BA6-8ADC-D4C6DBBE4B5F@.microsoft.com...
> > > >I am having issues applying a filter to my report.
> > > >
> > > > I have one dataset that i want to filter and display serveral times in one
> > > > report.
> > > > The query is complicated and somewhat slow(yes i am working on optimizing
> > > > it), so i want to pull it once, and then use it in 4 different tables in
> > > > my
> > > > report.
> > > > The first table, will be unfiltered, the others will be filtered by one of
> > > > the fields in my dataset that returns an int datatype, none of the values
> > > > are
> > > > null.
> > > >
> > > > I tried following the instructions here:
> > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_3diq.asp,
> > > > but it doesn't work.
> > > >
> > > > When i preview the report, it returns no build errors or warnings, but i
> > > > get
> > > > this dialog:
> > > > Processing Errors
> > > > An error has occurred during the report processing.
> > > > The processing of filter expression for the table 'table1' cannot be
> > > > performed. The comparison failed. Please check the data type returned by
> > > > the
> > > > filter expression.
> > > > ----
> > > > What am i doing wrong?
> > > > This is driving me nuts...
> > > > --
> > > > Ryan
> > >
> > >
> > >|||Thanks Ryan!
I still have issue with the following combination
Expression: =Fields!X.Value
Operator: =Value: =0
(Operator: < or > worked fine)
I got around this problem by casting X in my sql statement to a varchar(1)
and setting -> Value: 0
"Ryan" wrote:
> Ok;
> I figured i out, picky little application:
> Expression: =Fields!ProcessStage.Value
> Operator: >=> Value: =80
> Thanks...
> "Ryan" wrote:
> > Expression: Fields!ProcessStage.Value>=80
> >
> > I think i found part of my issue.
> > When you select a field from the drop down in Table
> > Properties->Filters/Expressions, it leaves a "=" at the beginning. By
> > removing that, i no longer get the error. But it does not seem to filter the
> > data either...
> >
> >
> > "Lev Semenets [MSFT]" wrote:
> >
> > > What is the filtering expression?
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > >
> > >
> > > "Ryan" <Ryan@.discussions.microsoft.com> wrote in message
> > > news:0CE890FC-4EAF-4BA6-8ADC-D4C6DBBE4B5F@.microsoft.com...
> > > >I am having issues applying a filter to my report.
> > > >
> > > > I have one dataset that i want to filter and display serveral times in one
> > > > report.
> > > > The query is complicated and somewhat slow(yes i am working on optimizing
> > > > it), so i want to pull it once, and then use it in 4 different tables in
> > > > my
> > > > report.
> > > > The first table, will be unfiltered, the others will be filtered by one of
> > > > the fields in my dataset that returns an int datatype, none of the values
> > > > are
> > > > null.
> > > >
> > > > I tried following the instructions here:
> > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_3diq.asp,
> > > > but it doesn't work.
> > > >
> > > > When i preview the report, it returns no build errors or warnings, but i
> > > > get
> > > > this dialog:
> > > > Processing Errors
> > > > An error has occurred during the report processing.
> > > > The processing of filter expression for the table 'table1' cannot be
> > > > performed. The comparison failed. Please check the data type returned by
> > > > the
> > > > filter expression.
> > > > ----
> > > > What am i doing wrong?
> > > > This is driving me nuts...
> > > > --
> > > > Ryan
> > >
> > >
> > >
I have one dataset that i want to filter and display serveral times in one
report.
The query is complicated and somewhat slow(yes i am working on optimizing
it), so i want to pull it once, and then use it in 4 different tables in my
report.
The first table, will be unfiltered, the others will be filtered by one of
the fields in my dataset that returns an int datatype, none of the values are
null.
I tried following the instructions here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_3diq.asp,
but it doesn't work.
When i preview the report, it returns no build errors or warnings, but i get
this dialog:
Processing Errors
An error has occurred during the report processing.
The processing of filter expression for the table 'table1' cannot be
performed. The comparison failed. Please check the data type returned by the
filter expression.
----
What am i doing wrong?
This is driving me nuts...
--
RyanWhat is the filtering expression?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ryan" <Ryan@.discussions.microsoft.com> wrote in message
news:0CE890FC-4EAF-4BA6-8ADC-D4C6DBBE4B5F@.microsoft.com...
>I am having issues applying a filter to my report.
> I have one dataset that i want to filter and display serveral times in one
> report.
> The query is complicated and somewhat slow(yes i am working on optimizing
> it), so i want to pull it once, and then use it in 4 different tables in
> my
> report.
> The first table, will be unfiltered, the others will be filtered by one of
> the fields in my dataset that returns an int datatype, none of the values
> are
> null.
> I tried following the instructions here:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_3diq.asp,
> but it doesn't work.
> When i preview the report, it returns no build errors or warnings, but i
> get
> this dialog:
> Processing Errors
> An error has occurred during the report processing.
> The processing of filter expression for the table 'table1' cannot be
> performed. The comparison failed. Please check the data type returned by
> the
> filter expression.
> ----
> What am i doing wrong?
> This is driving me nuts...
> --
> Ryan|||Expression: Fields!ProcessStage.Value>=80
I think i found part of my issue.
When you select a field from the drop down in Table
Properties->Filters/Expressions, it leaves a "=" at the beginning. By
removing that, i no longer get the error. But it does not seem to filter the
data either...
"Lev Semenets [MSFT]" wrote:
> What is the filtering expression?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Ryan" <Ryan@.discussions.microsoft.com> wrote in message
> news:0CE890FC-4EAF-4BA6-8ADC-D4C6DBBE4B5F@.microsoft.com...
> >I am having issues applying a filter to my report.
> >
> > I have one dataset that i want to filter and display serveral times in one
> > report.
> > The query is complicated and somewhat slow(yes i am working on optimizing
> > it), so i want to pull it once, and then use it in 4 different tables in
> > my
> > report.
> > The first table, will be unfiltered, the others will be filtered by one of
> > the fields in my dataset that returns an int datatype, none of the values
> > are
> > null.
> >
> > I tried following the instructions here:
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_3diq.asp,
> > but it doesn't work.
> >
> > When i preview the report, it returns no build errors or warnings, but i
> > get
> > this dialog:
> > Processing Errors
> > An error has occurred during the report processing.
> > The processing of filter expression for the table 'table1' cannot be
> > performed. The comparison failed. Please check the data type returned by
> > the
> > filter expression.
> > ----
> > What am i doing wrong?
> > This is driving me nuts...
> > --
> > Ryan
>
>|||Ok;
I figured i out, picky little application:
Expression: =Fields!ProcessStage.Value
Operator: >=Value: =80
Thanks...
"Ryan" wrote:
> Expression: Fields!ProcessStage.Value>=80
> I think i found part of my issue.
> When you select a field from the drop down in Table
> Properties->Filters/Expressions, it leaves a "=" at the beginning. By
> removing that, i no longer get the error. But it does not seem to filter the
> data either...
>
> "Lev Semenets [MSFT]" wrote:
> > What is the filtering expression?
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "Ryan" <Ryan@.discussions.microsoft.com> wrote in message
> > news:0CE890FC-4EAF-4BA6-8ADC-D4C6DBBE4B5F@.microsoft.com...
> > >I am having issues applying a filter to my report.
> > >
> > > I have one dataset that i want to filter and display serveral times in one
> > > report.
> > > The query is complicated and somewhat slow(yes i am working on optimizing
> > > it), so i want to pull it once, and then use it in 4 different tables in
> > > my
> > > report.
> > > The first table, will be unfiltered, the others will be filtered by one of
> > > the fields in my dataset that returns an int datatype, none of the values
> > > are
> > > null.
> > >
> > > I tried following the instructions here:
> > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_3diq.asp,
> > > but it doesn't work.
> > >
> > > When i preview the report, it returns no build errors or warnings, but i
> > > get
> > > this dialog:
> > > Processing Errors
> > > An error has occurred during the report processing.
> > > The processing of filter expression for the table 'table1' cannot be
> > > performed. The comparison failed. Please check the data type returned by
> > > the
> > > filter expression.
> > > ----
> > > What am i doing wrong?
> > > This is driving me nuts...
> > > --
> > > Ryan
> >
> >
> >|||I tore my hair out for about 24 hours until I found this post.. Thanks
Ryan - likely I would be tearing my hair out for days more had I not
happened on this. The nest question is when will someone from MS get
around to addressing this "feature."|||Thanks for the post Ryan...you bet the application is really picky...I hope
the new version is really fine tuned in such areas
"Ryan" wrote:
> Ok;
> I figured i out, picky little application:
> Expression: =Fields!ProcessStage.Value
> Operator: >=> Value: =80
> Thanks...
> "Ryan" wrote:
> > Expression: Fields!ProcessStage.Value>=80
> >
> > I think i found part of my issue.
> > When you select a field from the drop down in Table
> > Properties->Filters/Expressions, it leaves a "=" at the beginning. By
> > removing that, i no longer get the error. But it does not seem to filter the
> > data either...
> >
> >
> > "Lev Semenets [MSFT]" wrote:
> >
> > > What is the filtering expression?
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > >
> > >
> > > "Ryan" <Ryan@.discussions.microsoft.com> wrote in message
> > > news:0CE890FC-4EAF-4BA6-8ADC-D4C6DBBE4B5F@.microsoft.com...
> > > >I am having issues applying a filter to my report.
> > > >
> > > > I have one dataset that i want to filter and display serveral times in one
> > > > report.
> > > > The query is complicated and somewhat slow(yes i am working on optimizing
> > > > it), so i want to pull it once, and then use it in 4 different tables in
> > > > my
> > > > report.
> > > > The first table, will be unfiltered, the others will be filtered by one of
> > > > the fields in my dataset that returns an int datatype, none of the values
> > > > are
> > > > null.
> > > >
> > > > I tried following the instructions here:
> > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_3diq.asp,
> > > > but it doesn't work.
> > > >
> > > > When i preview the report, it returns no build errors or warnings, but i
> > > > get
> > > > this dialog:
> > > > Processing Errors
> > > > An error has occurred during the report processing.
> > > > The processing of filter expression for the table 'table1' cannot be
> > > > performed. The comparison failed. Please check the data type returned by
> > > > the
> > > > filter expression.
> > > > ----
> > > > What am i doing wrong?
> > > > This is driving me nuts...
> > > > --
> > > > Ryan
> > >
> > >
> > >|||It is so helpful. Thanks!!!!!
"Ryan" wrote:
> Ok;
> I figured i out, picky little application:
> Expression: =Fields!ProcessStage.Value
> Operator: >=> Value: =80
> Thanks...
> "Ryan" wrote:
> > Expression: Fields!ProcessStage.Value>=80
> >
> > I think i found part of my issue.
> > When you select a field from the drop down in Table
> > Properties->Filters/Expressions, it leaves a "=" at the beginning. By
> > removing that, i no longer get the error. But it does not seem to filter the
> > data either...
> >
> >
> > "Lev Semenets [MSFT]" wrote:
> >
> > > What is the filtering expression?
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > >
> > >
> > > "Ryan" <Ryan@.discussions.microsoft.com> wrote in message
> > > news:0CE890FC-4EAF-4BA6-8ADC-D4C6DBBE4B5F@.microsoft.com...
> > > >I am having issues applying a filter to my report.
> > > >
> > > > I have one dataset that i want to filter and display serveral times in one
> > > > report.
> > > > The query is complicated and somewhat slow(yes i am working on optimizing
> > > > it), so i want to pull it once, and then use it in 4 different tables in
> > > > my
> > > > report.
> > > > The first table, will be unfiltered, the others will be filtered by one of
> > > > the fields in my dataset that returns an int datatype, none of the values
> > > > are
> > > > null.
> > > >
> > > > I tried following the instructions here:
> > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_3diq.asp,
> > > > but it doesn't work.
> > > >
> > > > When i preview the report, it returns no build errors or warnings, but i
> > > > get
> > > > this dialog:
> > > > Processing Errors
> > > > An error has occurred during the report processing.
> > > > The processing of filter expression for the table 'table1' cannot be
> > > > performed. The comparison failed. Please check the data type returned by
> > > > the
> > > > filter expression.
> > > > ----
> > > > What am i doing wrong?
> > > > This is driving me nuts...
> > > > --
> > > > Ryan
> > >
> > >
> > >|||Thanks Ryan!
I still have issue with the following combination
Expression: =Fields!X.Value
Operator: =Value: =0
(Operator: < or > worked fine)
I got around this problem by casting X in my sql statement to a varchar(1)
and setting -> Value: 0
"Ryan" wrote:
> Ok;
> I figured i out, picky little application:
> Expression: =Fields!ProcessStage.Value
> Operator: >=> Value: =80
> Thanks...
> "Ryan" wrote:
> > Expression: Fields!ProcessStage.Value>=80
> >
> > I think i found part of my issue.
> > When you select a field from the drop down in Table
> > Properties->Filters/Expressions, it leaves a "=" at the beginning. By
> > removing that, i no longer get the error. But it does not seem to filter the
> > data either...
> >
> >
> > "Lev Semenets [MSFT]" wrote:
> >
> > > What is the filtering expression?
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > >
> > >
> > > "Ryan" <Ryan@.discussions.microsoft.com> wrote in message
> > > news:0CE890FC-4EAF-4BA6-8ADC-D4C6DBBE4B5F@.microsoft.com...
> > > >I am having issues applying a filter to my report.
> > > >
> > > > I have one dataset that i want to filter and display serveral times in one
> > > > report.
> > > > The query is complicated and somewhat slow(yes i am working on optimizing
> > > > it), so i want to pull it once, and then use it in 4 different tables in
> > > > my
> > > > report.
> > > > The first table, will be unfiltered, the others will be filtered by one of
> > > > the fields in my dataset that returns an int datatype, none of the values
> > > > are
> > > > null.
> > > >
> > > > I tried following the instructions here:
> > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_3diq.asp,
> > > > but it doesn't work.
> > > >
> > > > When i preview the report, it returns no build errors or warnings, but i
> > > > get
> > > > this dialog:
> > > > Processing Errors
> > > > An error has occurred during the report processing.
> > > > The processing of filter expression for the table 'table1' cannot be
> > > > performed. The comparison failed. Please check the data type returned by
> > > > the
> > > > filter expression.
> > > > ----
> > > > What am i doing wrong?
> > > > This is driving me nuts...
> > > > --
> > > > Ryan
> > >
> > >
> > >
filters
does anyone know if it is possible to add a filter to a report that is
passed as a user-defined parameter? i know i can do this by modifying the
sql that generates the report, but i'd like to do this inside of the report
so as to not hit the database each time the user selects a different filter.On Sep 24, 2:18 pm, "JTL" <j...@.clickstreamtech.com> wrote:
> does anyone know if it is possible to add a filter to a report that is
> passed as a user-defined parameter? i know i can do this by modifying the
> sql that generates the report, but i'd like to do this inside of the report
> so as to not hit the database each time the user selects a different filter.
If I'm understanding you correctly, you should be able to pass a
report parameter value to a table/matrix control via: right-clicking
the table/matrix control -> select Properties -> select the Filters
tab -> below Expression, enter an expression similar to the following:
=Parameters!ParamName.Value
And then compare it to the Value accordingly.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant
passed as a user-defined parameter? i know i can do this by modifying the
sql that generates the report, but i'd like to do this inside of the report
so as to not hit the database each time the user selects a different filter.On Sep 24, 2:18 pm, "JTL" <j...@.clickstreamtech.com> wrote:
> does anyone know if it is possible to add a filter to a report that is
> passed as a user-defined parameter? i know i can do this by modifying the
> sql that generates the report, but i'd like to do this inside of the report
> so as to not hit the database each time the user selects a different filter.
If I'm understanding you correctly, you should be able to pass a
report parameter value to a table/matrix control via: right-clicking
the table/matrix control -> select Properties -> select the Filters
tab -> below Expression, enter an expression similar to the following:
=Parameters!ParamName.Value
And then compare it to the Value accordingly.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant
Filtering without a tuple-set ?
a normal Filter with a tuple-set:
Filter(
[Brand].members,
(([Measures].[Sale],
[Posting Period].[Year].&[2005]) > 0)
)
The brands where we sold something in the year 2005. This works fine but....
a Filter within a range of days ? :
Filter(
[Brand].members,
(([Measures].[Sale],
ClosingPeriod([Posting Period].[Day]).lag(365):ClosingPeriod([Posting Period].[Day]))) > 0)
The brands where we sold something in a range between currentday -365 days and currentday.
This won't work with the filter-function ! Is there an other way to do this ?
Kind regards 
If your Sales measure does not contain negatives you could simply aggregate the measure for the range of dates.
Filter(
[Brand].members,
Aggregate(ClosingPeriod([Posting Period].[Day]).lag(365)
:ClosingPeriod([Posting Period].[Day]
,[Measures].[Sale] ) > 0)
If your measure might contain negative figures and there is a chance that a date range could sum to 0 even though there are sales for the specified period, you might be better off getting a count of the non empty cells.
Filter(
[Brand].members,
NonEmpty(ClosingPeriod([Posting Period].[Day]).lag(365)
:ClosingPeriod([Posting Period].[Day]
,[Measures].[Sale] ).Count > 0)
Filtering with an "In" operator
Is it possible to filter with an "In" operator? "In" is not an option when
building a filter expression so I was hoping someone found a workaround. I
plan to pass a comma-seperated list of values within quotes. The report I'm
using runs each morning and I filter that report without having to re-query
the database.
Thanks, ChrisTo do an "In" just put two of the same Filter Expression next to each other.
The And/Or column will change to Or.
For example:
=Fields!State.Value Equals WA Or
=Fields!State.Value Equals CA Or
...etc...
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"Chris" <chrisf@.unr.edu> wrote in message
news:u0lGTFTaEHA.972@.TK2MSFTNGP12.phx.gbl...
> Is it possible to filter with an "In" operator? "In" is not an option
when
> building a filter expression so I was hoping someone found a workaround.
I
> plan to pass a comma-seperated list of values within quotes. The report
I'm
> using runs each morning and I filter that report without having to
re-query
> the database.
> Thanks, Chris
>|||Multi-valued parameters are not supported RS 2000. Here's a related post
with a solution that might work for you:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=ONnNRtsBEHA.3064%40tk2msftngp13.phx.gbl&rnum=2&prev=/groups%3Fq%3D%2522in%2Bclause%2522%2Bgroup:microsoft.public.sqlserver.reportingsvcs%26hl%3Den%26lr%3D%26ie%3DUTF-8%26selm%3DONnNRtsBEHA.3064%2540tk2msftngp13.phx.gbl%26rnum%3D2
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chris" <chrisf@.unr.edu> wrote in message
news:u0lGTFTaEHA.972@.TK2MSFTNGP12.phx.gbl...
> Is it possible to filter with an "In" operator? "In" is not an option
when
> building a filter expression so I was hoping someone found a workaround.
I
> plan to pass a comma-seperated list of values within quotes. The report
I'm
> using runs each morning and I filter that report without having to
re-query
> the database.
> Thanks, Chris
>
building a filter expression so I was hoping someone found a workaround. I
plan to pass a comma-seperated list of values within quotes. The report I'm
using runs each morning and I filter that report without having to re-query
the database.
Thanks, ChrisTo do an "In" just put two of the same Filter Expression next to each other.
The And/Or column will change to Or.
For example:
=Fields!State.Value Equals WA Or
=Fields!State.Value Equals CA Or
...etc...
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"Chris" <chrisf@.unr.edu> wrote in message
news:u0lGTFTaEHA.972@.TK2MSFTNGP12.phx.gbl...
> Is it possible to filter with an "In" operator? "In" is not an option
when
> building a filter expression so I was hoping someone found a workaround.
I
> plan to pass a comma-seperated list of values within quotes. The report
I'm
> using runs each morning and I filter that report without having to
re-query
> the database.
> Thanks, Chris
>|||Multi-valued parameters are not supported RS 2000. Here's a related post
with a solution that might work for you:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=ONnNRtsBEHA.3064%40tk2msftngp13.phx.gbl&rnum=2&prev=/groups%3Fq%3D%2522in%2Bclause%2522%2Bgroup:microsoft.public.sqlserver.reportingsvcs%26hl%3Den%26lr%3D%26ie%3DUTF-8%26selm%3DONnNRtsBEHA.3064%2540tk2msftngp13.phx.gbl%26rnum%3D2
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chris" <chrisf@.unr.edu> wrote in message
news:u0lGTFTaEHA.972@.TK2MSFTNGP12.phx.gbl...
> Is it possible to filter with an "In" operator? "In" is not an option
when
> building a filter expression so I was hoping someone found a workaround.
I
> plan to pass a comma-seperated list of values within quotes. The report
I'm
> using runs each morning and I filter that report without having to
re-query
> the database.
> Thanks, Chris
>
Filtering Top n on Group or Table
Hopefully someone can help.
I am trying to filter either my group or the whole table to return the top 5 lines of my data.
If I use Topn (Fieldname) = 5 in my filter for the group, it returns merely the total of the table and hides all the detail.
If I use the same filter in the table filter, only the table header is returned.
I have rather a complex formula based on parameters to calculate the field I want to show the top 5 of. e.g. If parameter equals 2 then add balance_period_1 to balance_period_2. This formula works perfectly if I remove the top5 filter. See detailed expression below
The expression is
Topn(iif(Parameters!Period.Value = "02", Fields!Year_Opening_Balance. Value+Fields!Balance_Period_01.Value +Fields!Balance_Period_02.Value,0))
The Operator is set to =
The Value is set to 5
Any ideas would be greatly appreciated.
Set the filter expression to:
=iif(Parameters!Period.Value = "02", Fields!Year_Opening_Balance. Value+Fields!Balance_Period_01.Value +Fields!Balance_Period_02.Value,0))
Set the filter operator to:
TopN
Set the filter value to:
=5
Note: if you set the filter value to just 5, it will be interpreted as string constant - which won't work. You have to set it to =5, which will evaluate to the integer value 5.
-- Robert
|||Thanks!!
So simple but blind to my eyes, now all is revealed!
Filtering through Matrix or groups in matrix
I have a report thats fully functional. I just want to add a filter so that my "Visits" field only displays the Visits per day that are less then 6. When i try to filter out the matrix or the group, it tells me the datatypes are different . Something about int32. Its in a matrix, but i have seen this happen in a table too, so i guessing thats not the problem. I just want to be able to display the information for Sales Reps with less then 6 Visits. Any help, will be greatlly appreciated.
Filter work fine with "=" sign so what u need to do is
fileter condition < =6 (In expression use =6 instead of plain 6)
or you may try using =cint(6)
Filtering the parameter content
Hello,
I have built a report using a Cube (and not a relational database).
I have a date as a parameter and I would like to filter its content: the parameter goes from 1900 to 2090 and I would like the user to see only from 2006 to 2090.
Can you help me by giving me tricks to do it ? There may have several ways of doing it but I can't find them.
Thank you in advance !
Alexis
If you always want it to start from 2006( not change to 2007 to 2090 when it is 2008) it′s easy in MDX:SELECT {[Time].[Year].&[2006] : NULL } on rows, bla bla bla.
This will make it take every member from the time dimension from 2006 up til the end, in your case 2090.|||
Thank you, it works perfectly !
It would be interesting to make dynamic date range according to the actual year but.... next time ! 
Filtering Single Recordset
Hello, I am a noob, so here goes. I would like to filter a resultset
returned from a stored procedure into 2 tables (e.g., summary section and
detail section). I would like to avoid 2 hits to the dB, so am returning a
summary and detailed resultset in 1 recordset. What I would like to do is
filter recordset 1 (say RectType = 1) into a summary table and filter
recordset 2 (say RectType = 2) into a detailed table. This has to be a
simple solution, but I must be missing something. Here's what I have tried
so far:
1) Adding filter on Details Grouping in the table - Nothing happens.
2) Adding filter in table Properties - Receive error: "An error has occurred
druing report processing. The processing of filter expression for table
'Summary' cannot be performed. The comparison failed. Please check the data
type returned by filter expression."
TIAI would think that you would need to do the filter on the table...but
that you are comparing two different data types. Try a convert to char
or a convert to int so that your data from the recordset and the data
in the filter are of the same type.|||OMG - I didn't even think of converting to char and that worked!!! Thanks a
bunch dude!
"Luke" wrote:
> I would think that you would need to do the filter on the table...but
> that you are comparing two different data types. Try a convert to char
> or a convert to int so that your data from the recordset and the data
> in the filter are of the same type.
>
returned from a stored procedure into 2 tables (e.g., summary section and
detail section). I would like to avoid 2 hits to the dB, so am returning a
summary and detailed resultset in 1 recordset. What I would like to do is
filter recordset 1 (say RectType = 1) into a summary table and filter
recordset 2 (say RectType = 2) into a detailed table. This has to be a
simple solution, but I must be missing something. Here's what I have tried
so far:
1) Adding filter on Details Grouping in the table - Nothing happens.
2) Adding filter in table Properties - Receive error: "An error has occurred
druing report processing. The processing of filter expression for table
'Summary' cannot be performed. The comparison failed. Please check the data
type returned by filter expression."
TIAI would think that you would need to do the filter on the table...but
that you are comparing two different data types. Try a convert to char
or a convert to int so that your data from the recordset and the data
in the filter are of the same type.|||OMG - I didn't even think of converting to char and that worked!!! Thanks a
bunch dude!
"Luke" wrote:
> I would think that you would need to do the filter on the table...but
> that you are comparing two different data types. Try a convert to char
> or a convert to int so that your data from the recordset and the data
> in the filter are of the same type.
>
Filtering results after execution and multivalue parameter questions
Hi. 2 questions:
1.
I'm trying to execute a report, then allow the user filter the results using a value list from the report dataset in a dropdown list. When I create a filtering parameter, i can't figure out how to make it work on results after the report is executed.
2.
How can I make a multivalue parameter default to (Select All)?
thanks!
#1: You can simulate the "user-driven filtering" by using drillthrough actions to the report itself and setting the filter parameter accordingly. In any case, it will require the report to be reprocessed because filtering data may impact many other things (such as pagination).
#2: Make the valid values list and the default value both query-based and use
the same dataset column.
-- Robert
Filtering Records Out in DSV. Performance Benefit?
In short, will it be advantageous for me to filter out records that are not associated with any facts within the DSV?
For example, there are records in our Products dimension that have no facts associated with them and I'm thinking of doing the following for the Products table in the cube's DSV:
select *
from products (nolock)
where product_key not in (
select distinct product_key from [sales fact] (nolock)
) or product_key not in (
select distinct product_key from [forecast fact] (nolock)
)
Doing this would filter out around 15,000 rows.This isn't a performance question - this is a functionality question. Do you want your dimensions to have members, which (currently) don't have any data associated with them ? If yes - then you need to do this filtering in DSV. If no - then not. As far as performance goes, I imagine that by doing this filtering you will make performance worse, since during processing of dimension it will have to do join with two fact tables.|||
Mosha Pasumansky wrote:
This isn't a performance question - this is a functionality question. Do you want your dimensions to have members, which (currently) don't have any data associated with them ? If yes - then you need to do this filtering in DSV. If no - then not. As far as performance goes, I imagine that by doing this filtering you will make performance worse, since during processing of dimension it will have to do join with two fact tables.
I'm not too concerned with the performance hit during the processing of the cube since we do it night.
So there's really no performance benefit to filtering out the "unnecessary" data?|||Again, if it is OK for you to have less dimension members in the dimension - you may see some performance benefits during qurying. Whether or not you will see this benefit depends greatly on the queries you are sending, on the calculations inside cube etc.|||
Hi,
I did this for AS2000 and do this for AS2005. It help me. I have only about 2 Millions dimension members instead of 5. I make a sence.
But take a look at you query
select *
from products (nolock)
where product_key not in (
select distinct product_key from [sales fact] (nolock)
) or product_key not in (
select distinct product_key from [forecast fact] (nolock)
)
You should rewrite it without "distinct" but should have an index on the product_key field.
Subscribe to:
Posts (Atom)