I have web-based reports using SQL server 2005 with filters called location
and suppliers and the number of locations and suppliers are more than 200
each. When the report is rendered with this filters, selection of locations
and suppliers is taking long time on the browsers and it is not easy to
search for the required options in the drop down with checkboxes,
1. Can we modify the parameters display area to show a file-open image
clicking on which opens a popup window for users to select the filter
values?
2. Can we improve the performance while user selects the filter values?
3. Is there any other way to handle this type of large filters (more than
200 filter options) for the reports?
Thanks and Regards,
SridharOn Jun 6, 12:13 am, "Sridhar" <dsrid...@.translogicsys.com> wrote:
> I have web-based reports using SQL server 2005 with filters called location
> and suppliers and the number of locations and suppliers are more than 200
> each. When the report is rendered with this filters, selection of locations
> and suppliers is taking long time on the browsers and it is not easy to
> search for the required options in the drop down with checkboxes,
> 1. Can we modify the parameters display area to show a file-open image
> clicking on which opens a popup window for users to select the filter
> values?
> 2. Can we improve the performance while user selects the filter values?
> 3. Is there any other way to handle this type of large filters (more than
> 200 filter options) for the reports?
> Thanks and Regards,
> Sridhar
In response to number 1, I'm afraid not. In response to number 2, if
I'm understanding you correctly, the more filters you have the more
the performance will be affected. To improve performance, I would
suggest running the report query/stored procedure through the Database
Engine Tuning Advisor to see if there are any indexes that can be
implemented/removed that would improve the performance of the query.
Then, make sure that you are doing as much of the processing in the
query/stored procedure as possible and not in the report. After that,
I would say to limit the number of filters you have on the report.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant
Showing posts with label filters. Show all posts
Showing posts with label filters. Show all posts
Sunday, February 19, 2012
Filters using an 'OR' condition
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
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
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 on the data
Hi guys,
(snip)
I wanted to ask you for help as I am struggling with it second evening
already...
I have got tables DEVICES and PARTS.
One device can consist of multiple parts.
But...
I have also another table - FILTERS (id int, type int, is_not int,
phrase varchar(40))
where:id - just id,
type - filter type - can be 1 - for devices and 2 for parts,
is_not - says if the phrase has to be in a description (0) or must not
be there (1)
phrase - word to found in the description
My trouble is when I want to apply three filters at once:
1. Find devices with description containing PHRASE
2. Find parts with description containing PHRASE
3. Find devices with description NOT containing PHRASE
Query selecting parts and devices is like:
SELECT device.id, part.id
FROM DEVICE JOIN PARTS
WHERE ...
What I did is:
SELECT device_id, part_id FROM (
SELECT device_id, part_id FROM (
SELECT device_id, part_id FROM (
QUERY
) a
WHERE NOT EXISTS (SELECT 1 FROM FILTERS WHERE is_not=0 AND type=1)
OR EXISTS (SELECT 1 FROM FILTERS WHERE is_not=0 AND type=1 AND
device_desc LIKE '%' + phrase + '%')
) b
WHERE NOT EXISTS (SELECT 1 FROM FILTERS WHERE is_not=0 AND type=2)
OR EXISTS (SELECT 1 FROM FILTERS WHERE is_not=0 AND type=2 AND
part_desc LIKE '%' + phrase + '%')
) c
WHERE NOT EXISTS (SELECT 1 FROM FILTERS WHERE is_not>0 AND type=1)
OR NOT EXISTS (SELECT 1 FROM FILTERS WHERE is_not>0 AND type=1 AND
device_desc LIKE '%' + phrase + '%')
It works, but very slow. In DEVICES tables is 2 milion rows and in
PARTS is 3 millions.
I turned SET STATISTICS IO ON, and they show that FILTERS are being
asked veeery often.
It must be more efficient way to acheve this but I must be blind.
Thanks fo any advices,
KucolOn 27 Oct 2006 14:47:17 -0700, kucol wrote:
Quote:
Originally Posted by
>Hi guys,
>
>I wanted to ask you for help as I am struggling with it second evening
>already...
(snip)
Hi Kucol,
I noticed that you have posted the same message to
microsoft.public.sqlserver.programming as well. Please do not multipost
in the future - had I not checked the other groups first, I might now
have spent time to duplicate an answer you've already gotten elsewhere.
I prefer to spend my time answering questions that have not been
answered yet.
I'll keep an eye on the discussion in .programming and chime in if I
feel I have anything new to add.
--
Hugo Kornelis, SQL Server MVP
Filters in rdl.
Hi,
I've a big trouble. According the documentation, the <Filters> section of a
rdl file contains a collection of <Filter>. But there is no information how
this collection is handled. It seem that the datasource is filtered based on
every <Filter> with AND.
If you are in the Filters tab of a graph, you can add several <Filter>. The
"and/or" column is greyed and a default AND is used.
My question is how to change this to OR.
My filters have to be like this
City="New York" or
City="New Jersey"
Instead of
City="New York" and
City="New Jersey"
who will never return any rows.
Any idea ?
Thanks.
Regardsm
--
Stéphaneif you select the same column name from the drop down it assumes "OR" if you
select diferent column name it assumes "and"
For e.g
contact id = 1 and when you go to the second line and select contact id you
can see it turns to "or".
if you select AccountName then it turns to "and"
Amarnath
"Suedois" wrote:
> Hi,
> I've a big trouble. According the documentation, the <Filters> section of a
> rdl file contains a collection of <Filter>. But there is no information how
> this collection is handled. It seem that the datasource is filtered based on
> every <Filter> with AND.
> If you are in the Filters tab of a graph, you can add several <Filter>. The
> "and/or" column is greyed and a default AND is used.
> My question is how to change this to OR.
> My filters have to be like this
> City="New York" or
> City="New Jersey"
> Instead of
> City="New York" and
> City="New Jersey"
> who will never return any rows.
> Any idea ?
> Thanks.
> Regardsm
>
> --
> Stéphane|||Thanks for you reply.
Didi you know if it's possible to do an 'or' on different columns ?
I'll try you tips and see what is done in XML file.
Regards,
--
Stéphane
"Amarnath" wrote:
> if you select the same column name from the drop down it assumes "OR" if you
> select diferent column name it assumes "and"
> For e.g
> contact id = 1 and when you go to the second line and select contact id you
> can see it turns to "or".
> if you select AccountName then it turns to "and"
> Amarnath
>
> "Suedois" wrote:
> > Hi,
> >
> > I've a big trouble. According the documentation, the <Filters> section of a
> > rdl file contains a collection of <Filter>. But there is no information how
> > this collection is handled. It seem that the datasource is filtered based on
> > every <Filter> with AND.
> >
> > If you are in the Filters tab of a graph, you can add several <Filter>. The
> > "and/or" column is greyed and a default AND is used.
> >
> > My question is how to change this to OR.
> >
> > My filters have to be like this
> > City="New York" or
> > City="New Jersey"
> > Instead of
> > City="New York" and
> > City="New Jersey"
> > who will never return any rows.
> >
> > Any idea ?
> >
> > Thanks.
> >
> > Regardsm
> >
> >
> >
> > --
> > Stéphane
I've a big trouble. According the documentation, the <Filters> section of a
rdl file contains a collection of <Filter>. But there is no information how
this collection is handled. It seem that the datasource is filtered based on
every <Filter> with AND.
If you are in the Filters tab of a graph, you can add several <Filter>. The
"and/or" column is greyed and a default AND is used.
My question is how to change this to OR.
My filters have to be like this
City="New York" or
City="New Jersey"
Instead of
City="New York" and
City="New Jersey"
who will never return any rows.
Any idea ?
Thanks.
Regardsm
--
Stéphaneif you select the same column name from the drop down it assumes "OR" if you
select diferent column name it assumes "and"
For e.g
contact id = 1 and when you go to the second line and select contact id you
can see it turns to "or".
if you select AccountName then it turns to "and"
Amarnath
"Suedois" wrote:
> Hi,
> I've a big trouble. According the documentation, the <Filters> section of a
> rdl file contains a collection of <Filter>. But there is no information how
> this collection is handled. It seem that the datasource is filtered based on
> every <Filter> with AND.
> If you are in the Filters tab of a graph, you can add several <Filter>. The
> "and/or" column is greyed and a default AND is used.
> My question is how to change this to OR.
> My filters have to be like this
> City="New York" or
> City="New Jersey"
> Instead of
> City="New York" and
> City="New Jersey"
> who will never return any rows.
> Any idea ?
> Thanks.
> Regardsm
>
> --
> Stéphane|||Thanks for you reply.
Didi you know if it's possible to do an 'or' on different columns ?
I'll try you tips and see what is done in XML file.
Regards,
--
Stéphane
"Amarnath" wrote:
> if you select the same column name from the drop down it assumes "OR" if you
> select diferent column name it assumes "and"
> For e.g
> contact id = 1 and when you go to the second line and select contact id you
> can see it turns to "or".
> if you select AccountName then it turns to "and"
> Amarnath
>
> "Suedois" wrote:
> > Hi,
> >
> > I've a big trouble. According the documentation, the <Filters> section of a
> > rdl file contains a collection of <Filter>. But there is no information how
> > this collection is handled. It seem that the datasource is filtered based on
> > every <Filter> with AND.
> >
> > If you are in the Filters tab of a graph, you can add several <Filter>. The
> > "and/or" column is greyed and a default AND is used.
> >
> > My question is how to change this to OR.
> >
> > My filters have to be like this
> > City="New York" or
> > City="New Jersey"
> > Instead of
> > City="New York" and
> > City="New Jersey"
> > who will never return any rows.
> >
> > Any idea ?
> >
> > Thanks.
> >
> > Regardsm
> >
> >
> >
> > --
> > Stéphane
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 in Aggregation Expressions
Pretend I have a table called Employees.
I want to know how many mend and women work for me.
Something like this:
=Count(IIF(Fields!EmployeeGender.Value = "M", 1, 0), Nothing)
=Count(IIF(Fields!EmployeeGender.Value = "F", 1, 0), Nothing)
Similarly I could get today's and yesterday's orders.
Something like this:
=Sum(IIF(Fields!OrderDate = Now, Fields!OrderTotal, 0), Nothing)
=Sum(IIF(Fields!OrderDate = Now-1, Fields!OrderTotal, 0), Nothing)
Is this possible and if not - what could I do to accomplish this?
I would prefer these two items to appear in the same TableRow.
Thanks in advance, JerryAs mentioned in the other thread "Does Sum not support Nothing scope?", you
may want to omit the scope argument of the aggregate function. Depending
where (header of groupings vs. detail) you move the textbox with the
aggregate function it will get different scopes if it is omitted.
If you always want to perform the aggregate calculations within a certain
containing scope (i.e. parent groups, parent data region scopes, data set
scope) you should explicitly specify them as scope argument.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jerry Nixon" <jerrynixon@.gmail.com> wrote in message
news:36f558cf.0410121238.73be231d@.posting.google.com...
> Pretend I have a table called Employees.
> I want to know how many mend and women work for me.
> Something like this:
> =Count(IIF(Fields!EmployeeGender.Value = "M", 1, 0), Nothing)
> =Count(IIF(Fields!EmployeeGender.Value = "F", 1, 0), Nothing)
> Similarly I could get today's and yesterday's orders.
> Something like this:
> =Sum(IIF(Fields!OrderDate = Now, Fields!OrderTotal, 0), Nothing)
> =Sum(IIF(Fields!OrderDate = Now-1, Fields!OrderTotal, 0), Nothing)
> Is this possible and if not - what could I do to accomplish this?
> I would prefer these two items to appear in the same TableRow.
> Thanks in advance, Jerry|||Thank you, that is good advice and it makes sense.
However, it does not answer why the following conditional aggregate fails:
=Count(IIF(Fields!EmployeeGender.Value = "M", 1, 0), Nothing)
=Count(IIF(Fields!EmployeeGender.Value = "M", 1, 0))
Thanks in advance, Jerry
I want to know how many mend and women work for me.
Something like this:
=Count(IIF(Fields!EmployeeGender.Value = "M", 1, 0), Nothing)
=Count(IIF(Fields!EmployeeGender.Value = "F", 1, 0), Nothing)
Similarly I could get today's and yesterday's orders.
Something like this:
=Sum(IIF(Fields!OrderDate = Now, Fields!OrderTotal, 0), Nothing)
=Sum(IIF(Fields!OrderDate = Now-1, Fields!OrderTotal, 0), Nothing)
Is this possible and if not - what could I do to accomplish this?
I would prefer these two items to appear in the same TableRow.
Thanks in advance, JerryAs mentioned in the other thread "Does Sum not support Nothing scope?", you
may want to omit the scope argument of the aggregate function. Depending
where (header of groupings vs. detail) you move the textbox with the
aggregate function it will get different scopes if it is omitted.
If you always want to perform the aggregate calculations within a certain
containing scope (i.e. parent groups, parent data region scopes, data set
scope) you should explicitly specify them as scope argument.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jerry Nixon" <jerrynixon@.gmail.com> wrote in message
news:36f558cf.0410121238.73be231d@.posting.google.com...
> Pretend I have a table called Employees.
> I want to know how many mend and women work for me.
> Something like this:
> =Count(IIF(Fields!EmployeeGender.Value = "M", 1, 0), Nothing)
> =Count(IIF(Fields!EmployeeGender.Value = "F", 1, 0), Nothing)
> Similarly I could get today's and yesterday's orders.
> Something like this:
> =Sum(IIF(Fields!OrderDate = Now, Fields!OrderTotal, 0), Nothing)
> =Sum(IIF(Fields!OrderDate = Now-1, Fields!OrderTotal, 0), Nothing)
> Is this possible and if not - what could I do to accomplish this?
> I would prefer these two items to appear in the same TableRow.
> Thanks in advance, Jerry|||Thank you, that is good advice and it makes sense.
However, it does not answer why the following conditional aggregate fails:
=Count(IIF(Fields!EmployeeGender.Value = "M", 1, 0), Nothing)
=Count(IIF(Fields!EmployeeGender.Value = "M", 1, 0))
Thanks in advance, Jerry
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 & Parameters pane missing in MDX Query Designer
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?
I may have made a discovery why I am not seeing the filter and parameter pane in the MDX Query Designer in SQL Server Business Intelligence Development Studio.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?
Filters & Parameters pane missing in MDX Query Designer
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?
I may have made a discovery why I am not seeing the filter and parameter pane in the MDX Query Designer in SQL Server Business Intelligence Development Studio.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?
Filters ! Filters ! Filters - Cascading parameters
Hi,
I have a table with 100 rows in the following format
---
country name state name city name
---
USA NY NY
USA NJ Newark
USA FL Orlando
USA FL Miami
USA CA LA
USA CA SF
China XX XX
India XX XX
Australia XX XX
China XX XX
India XX XX
----
I have 3 dropdown cascading parameters (all of them have default values
when the report loads) - say USA - CA - LA
Country
State
City
So here is my problem. The report loads first time once i click on the
report name. (because it has the default values). But when the user
clicks on the country drop down to select another country say China,
the report down goes blank (white page) UNITL THE USER CLICKS THE "VIEW
REPORT" BUTTON (WHICH IS VERY VERY ANNOYIG). Is there anyway i can
leave the table as it is without making it blank.
Please help
Thanks
Prashhi
I don't think there is a work around to this (but I would be interested in
finding out one). This is just the default behavior which appears to be a
postback on change.
Also, coming from the other direction AND just as annoying (my defaults, by
request of the customer, are 'ALL','ALL','ALL' - which is a waste of computer
resources). Fortunately, we have an ASP.NET interface which 90% of the end
users use.
Rob
"Prash" wrote:
> Hi,
> I have a table with 100 rows in the following format
> ---
> country name state name city name
> ---
> USA NY NY
> USA NJ Newark
> USA FL Orlando
> USA FL Miami
> USA CA LA
> USA CA SF
> China XX XX
> India XX XX
> Australia XX XX
> China XX XX
> India XX XX
> ----
>
> I have 3 dropdown cascading parameters (all of them have default values
> when the report loads) - say USA - CA - LA
> Country
> State
> City
>
> So here is my problem. The report loads first time once i click on the
> report name. (because it has the default values). But when the user
> clicks on the country drop down to select another country say China,
> the report down goes blank (white page) UNITL THE USER CLICKS THE "VIEW
> REPORT" BUTTON (WHICH IS VERY VERY ANNOYIG). Is there anyway i can
> leave the table as it is without making it blank.
>
> Please help
> Thanks
> Prash
>
I have a table with 100 rows in the following format
---
country name state name city name
---
USA NY NY
USA NJ Newark
USA FL Orlando
USA FL Miami
USA CA LA
USA CA SF
China XX XX
India XX XX
Australia XX XX
China XX XX
India XX XX
----
I have 3 dropdown cascading parameters (all of them have default values
when the report loads) - say USA - CA - LA
Country
State
City
So here is my problem. The report loads first time once i click on the
report name. (because it has the default values). But when the user
clicks on the country drop down to select another country say China,
the report down goes blank (white page) UNITL THE USER CLICKS THE "VIEW
REPORT" BUTTON (WHICH IS VERY VERY ANNOYIG). Is there anyway i can
leave the table as it is without making it blank.
Please help
Thanks
Prashhi
I don't think there is a work around to this (but I would be interested in
finding out one). This is just the default behavior which appears to be a
postback on change.
Also, coming from the other direction AND just as annoying (my defaults, by
request of the customer, are 'ALL','ALL','ALL' - which is a waste of computer
resources). Fortunately, we have an ASP.NET interface which 90% of the end
users use.
Rob
"Prash" wrote:
> Hi,
> I have a table with 100 rows in the following format
> ---
> country name state name city name
> ---
> USA NY NY
> USA NJ Newark
> USA FL Orlando
> USA FL Miami
> USA CA LA
> USA CA SF
> China XX XX
> India XX XX
> Australia XX XX
> China XX XX
> India XX XX
> ----
>
> I have 3 dropdown cascading parameters (all of them have default values
> when the report loads) - say USA - CA - LA
> Country
> State
> City
>
> So here is my problem. The report loads first time once i click on the
> report name. (because it has the default values). But when the user
> clicks on the country drop down to select another country say China,
> the report down goes blank (white page) UNITL THE USER CLICKS THE "VIEW
> REPORT" BUTTON (WHICH IS VERY VERY ANNOYIG). Is there anyway i can
> leave the table as it is without making it blank.
>
> Please help
> Thanks
> Prash
>
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 - A good way to limit data at subscribers?
We use merge replication where data created at a control centre, then
modified in a filtered publication at the subscribers. We currently have
about 100 subscribers.
Publication is filter is WHERE LocationRef=XXX
We need to retain data at the control centre but not at subscribers, so does
it make sense to alter the filter to somthing like WHERE LocationRef=XXX AND
VisitDate>YYYY to achieve this, thereby reducing the data stored at each
subscriber?
Tony Toker
Data Identic Ltd.
This is not the correct way to do this.
What will happen is that data which is modified at the publisher or
subscriber will be merged if it meets the filtering criteria. However as the
data ages at the publisher or subscriber it will remain there, unless there
is a job which deletes/archives these rows so the merge replication process
will identify them as changed and then delete them from the publisher and
susbcriber. You would probably want to initiate the delete on the subscriber
to remove them there, but retail them on the publisher.
"Tony Toker" <xyzzy@.identic.co.uk> wrote in message
news:cqbl9o$dkv$1$8300dec7@.news.demon.co.uk...
> We use merge replication where data created at a control centre, then
> modified in a filtered publication at the subscribers. We currently have
> about 100 subscribers.
> Publication is filter is WHERE LocationRef=XXX
> We need to retain data at the control centre but not at subscribers, so
does
> it make sense to alter the filter to somthing like WHERE LocationRef=XXX
AND
> VisitDate>YYYY to achieve this, thereby reducing the data stored at each
> subscriber?
> Tony Toker
> Data Identic Ltd.
>
modified in a filtered publication at the subscribers. We currently have
about 100 subscribers.
Publication is filter is WHERE LocationRef=XXX
We need to retain data at the control centre but not at subscribers, so does
it make sense to alter the filter to somthing like WHERE LocationRef=XXX AND
VisitDate>YYYY to achieve this, thereby reducing the data stored at each
subscriber?
Tony Toker
Data Identic Ltd.
This is not the correct way to do this.
What will happen is that data which is modified at the publisher or
subscriber will be merged if it meets the filtering criteria. However as the
data ages at the publisher or subscriber it will remain there, unless there
is a job which deletes/archives these rows so the merge replication process
will identify them as changed and then delete them from the publisher and
susbcriber. You would probably want to initiate the delete on the subscriber
to remove them there, but retail them on the publisher.
"Tony Toker" <xyzzy@.identic.co.uk> wrote in message
news:cqbl9o$dkv$1$8300dec7@.news.demon.co.uk...
> We use merge replication where data created at a control centre, then
> modified in a filtered publication at the subscribers. We currently have
> about 100 subscribers.
> Publication is filter is WHERE LocationRef=XXX
> We need to retain data at the control centre but not at subscribers, so
does
> it make sense to alter the filter to somthing like WHERE LocationRef=XXX
AND
> VisitDate>YYYY to achieve this, thereby reducing the data stored at each
> subscriber?
> Tony Toker
> Data Identic Ltd.
>
Filters
A common question is on reports with parameters - is there some way to also
display what the parameter values were for this given report?I did find some ways to display the paramters - but man are they ugly:
Expression:
="Type: "&Join(Parameters!AssessmentTypeStatusATSStatus.Value)
The value selected is "Current" - but what is display on report with above
expression is:
Type: [Assessment Type Status].[ATS Status].&[Current]
"Joe" <hortoristic@.gmail dot com> wrote in message
news:eRTuvaQrGHA.3564@.TK2MSFTNGP03.phx.gbl...
>A common question is on reports with parameters - is there some way to also
>display what the parameter values were for this given report?
>
>
display what the parameter values were for this given report?I did find some ways to display the paramters - but man are they ugly:
Expression:
="Type: "&Join(Parameters!AssessmentTypeStatusATSStatus.Value)
The value selected is "Current" - but what is display on report with above
expression is:
Type: [Assessment Type Status].[ATS Status].&[Current]
"Joe" <hortoristic@.gmail dot com> wrote in message
news:eRTuvaQrGHA.3564@.TK2MSFTNGP03.phx.gbl...
>A common question is on reports with parameters - is there some way to also
>display what the parameter values were for this given report?
>
>
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
Subscribe to:
Posts (Atom)