Filter Help Needed

Good Day to All,
Need some help with Filters in SQL Reporting 2000.
Have a table report setup with a DS going to a Store Proc. need to add a
filter that is a bit complex. Don't know which filter section I should be
putting this in but in all cases I've run into errors.
Basically when the data is brought back from my Store Proc, I need to filter
out the data according to the following:
IF CBalance < 0
THEN ((-1*TTLBalance) > ((-1*CBAlance)*0.25))
ELSE (TTLBalance > (CBalance*0.25))
I keep getting this error that the filter comparison fails and I should
check the data Types returned by the filter expression.
Please help...you might want to cast your fields as varchar in your query. This has
been the answer to many filter comparison problems I have had in the
past.
Eric wrote:
> Good Day to All,
> Need some help with Filters in SQL Reporting 2000.
> Have a table report setup with a DS going to a Store Proc. need to add a
> filter that is a bit complex. Don't know which filter section I should be
> putting this in but in all cases I've run into errors.
> Basically when the data is brought back from my Store Proc, I need to filter
> out the data according to the following:
> IF CBalance < 0
> THEN ((-1*TTLBalance) > ((-1*CBAlance)*0.25))
> ELSE (TTLBalance > (CBalance*0.25))
> I keep getting this error that the filter comparison fails and I should
> check the data Types returned by the filter expression.
> Please help...|||Thanks Topher that was it. Odd the filter section can't handle comparisons
other then varchar / string like values. I wonder if this carried over into
the newer version.
Thanks again!!!
"Topher" wrote:
> you might want to cast your fields as varchar in your query. This has
> been the answer to many filter comparison problems I have had in the
> past.
>
> Eric wrote:
> > Good Day to All,
> > Need some help with Filters in SQL Reporting 2000.
> > Have a table report setup with a DS going to a Store Proc. need to add a
> > filter that is a bit complex. Don't know which filter section I should be
> > putting this in but in all cases I've run into errors.
> >
> > Basically when the data is brought back from my Store Proc, I need to filter
> > out the data according to the following:
> > IF CBalance < 0
> > THEN ((-1*TTLBalance) > ((-1*CBAlance)*0.25))
> > ELSE (TTLBalance > (CBalance*0.25))
> >
> > I keep getting this error that the filter comparison fails and I should
> > check the data Types returned by the filter expression.
> >
> > Please help...
>

Filter Error

I have No Filters in Place but I Keep getting this error on a couple reports.

An error has occurred during report processing.

The processing of SortExpression for the table ‘table2’ cannot be performed. The comparison failed. Please check the data type returned by the SortExpression.

What is table2

Have you configured interactive sorting to text field

sql

Filter clause

We are moving to SQL 2005 Standard Edition are use numerouse filters
for replication. In SQL 2000, we had a lot of the filters setup using
an OR statement in the filterclause (ie. a.company = b.company or
a.subcompany = b.company), and had no problems with adding the filters.
In SQL 2005, creating these filters takes forever, if created at all.
Has anyone out there seen a problem like this?
Any help would be appreciated.
Thanks,
Amy Marshall
Can you define "takes forever" as well as "if created at all"? Are they not
being created? Are you receiving errors? What is happening?
As far as creating these en mass. if you are using the GUI and are stuck in
that world, then plan on spending a few days clicking through and setting
this stuff up. Instead, you can very easily setup the base replication
configuration, generate a script, and then add all of the filters into the
script, in a fraction of the time it takes to use the GUI.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
<marshallae@.bowater.com> wrote in message
news:1135868828.946674.289840@.g14g2000cwa.googlegr oups.com...
> We are moving to SQL 2005 Standard Edition are use numerouse filters
> for replication. In SQL 2000, we had a lot of the filters setup using
> an OR statement in the filterclause (ie. a.company = b.company or
> a.subcompany = b.company), and had no problems with adding the filters.
> In SQL 2005, creating these filters takes forever, if created at all.
> Has anyone out there seen a problem like this?
> Any help would be appreciated.
> Thanks,
> Amy Marshall
>
|||How many tables? Did you select the option to automatically generate
filters? This is very lengthy for large related tables in both SQL 2000 and
SQL 2005.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<marshallae@.bowater.com> wrote in message
news:1135868828.946674.289840@.g14g2000cwa.googlegr oups.com...
> We are moving to SQL 2005 Standard Edition are use numerouse filters
> for replication. In SQL 2000, we had a lot of the filters setup using
> an OR statement in the filterclause (ie. a.company = b.company or
> a.subcompany = b.company), and had no problems with adding the filters.
> In SQL 2005, creating these filters takes forever, if created at all.
> Has anyone out there seen a problem like this?
> Any help would be appreciated.
> Thanks,
> Amy Marshall
>
|||I have about 10 tables that use the 'OR' filter that links to one
table. What I did was script the package from SQL 2000 and ran it in
Query Analyzer on SQL 2005. If I take out the 'OR' statement, then the
filter will be created in less than a second. With the 'OR' statement,
I usually end up cancelling it after 5-10 minutes for each table with
that filter. (We were testing with the CTP Sept. version, and did not
have this issue...Could it be Standard vs. Enterprise?)
Thanks,
Amy
|||No, the edition doesn't matter. I can't reproduce this on the RTM bits.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
<marshallae@.bowater.com> wrote in message
news:1136292153.538075.219420@.g14g2000cwa.googlegr oups.com...
>I have about 10 tables that use the 'OR' filter that links to one
> table. What I did was script the package from SQL 2000 and ran it in
> Query Analyzer on SQL 2005. If I take out the 'OR' statement, then the
> filter will be created in less than a second. With the 'OR' statement,
> I usually end up cancelling it after 5-10 minutes for each table with
> that filter. (We were testing with the CTP Sept. version, and did not
> have this issue...Could it be Standard vs. Enterprise?)
> Thanks,
> Amy
>

Filter and Sort Priority

I have a report with a category that filters for "top N" categories, but it is preventing the entire data set from being evaluated so that the series subtotals are incorrect. Is there a way to change the precedence, so that the subtotals are computed across the entire data set, and the "top N" is evaluated afterwards?

I attempted to solve this problem, or work around it, by having all relevant computation performed in the data source (which required dynamic sql with nested selects and window functions). Even with the data perfectly arranged and sorted in advance, the "TopN" feature of reporting services STILL managed to screw up the results. I've concluded that "TopN" is broken.

However, I found a useable work around. I added still another 'select' layer on my datasource with a dense_rank() function, and then used its result in the filter expressions of the relevant 'category.' Problem solved.

|||

I ran into the same problem and I found that this could be resolved though Reporting Services by adding the following to the group's visibility expression:

=IIF(RUNNINGVALUE(Fields!User.Value,COUNTDISTINCT,"table1_Domain")<11,False,True)

This report is counting top users of a web site that stores hits in a database table which is group by domain, then user.

In the report, I created a field: COUNT(Fields!User.Value). The inner group is then is sorted by this field (descending).

So, the logic in this expression is: Every time the user name changes it keeps that in the running total. The running total is reset when the group above it (the user's domain) changes. IIF the running total is less than 11, then Hidden = False.

So, while all values may still be processed, it only shows the top 10.

Hope this helps others

BTW, there is one drawback - since I'm using an expression for the visibility, I can't make this a drill-down field (since drill-down is also a function of visibility). If I select the "Visibility can be toggled by another report item", the report still displays as expected when first rendered, but if it's collapsed then expanded, ALL values for the group will appear, not just the top 10. I guess the visibility expression is only process at initial report rendering time, and not each time the group is collapsed/expanded.

|||

Okay, I'm a but dense today - there is another way to do this. The above will guarantee that ONLY 10 values are returned, but if you remove the sort, and use only the filter for TopN, you'll get the top 10 VALUES (some may duplicate). So, the above solution may return

DOMAIN Logon Count

~~~~~~~~~~~~~~~~~~~~~~~~

Domain1

UserA 10

UserB 10

UserC 9

........etc...upto

UserJ 2

Domain2

etc

However, using only TopN (no sort), may return

Domain1

UserA 10

....

UserJ 2

UserK 2

UserL 2

Domain2

etc

So, TopN by itself (without Sort) can return more than 10 values because there are multiples of the last entry with the same value. My solution in the previous posting depends on Sort, and simply hides everything after the 10th entry.

If someone else finds a way to list just the first 10 entries in a sorted list, please post. I don't like my solution too much because I can't further drill-down into the entries.

sql

Filter and Sort Priority

I have a report with a category that filters for "top N" categories, but it is preventing the entire data set from being evaluated so that the series subtotals are incorrect. Is there a way to change the precedence, so that the subtotals are computed across the entire data set, and the "top N" is evaluated afterwards?

I attempted to solve this problem, or work around it, by having all relevant computation performed in the data source (which required dynamic sql with nested selects and window functions). Even with the data perfectly arranged and sorted in advance, the "TopN" feature of reporting services STILL managed to screw up the results. I've concluded that "TopN" is broken.

However, I found a useable work around. I added still another 'select' layer on my datasource with a dense_rank() function, and then used its result in the filter expressions of the relevant 'category.' Problem solved.

|||

I ran into the same problem and I found that this could be resolved though Reporting Services by adding the following to the group's visibility expression:

=IIF(RUNNINGVALUE(Fields!User.Value,COUNTDISTINCT,"table1_Domain")<11,False,True)

This report is counting top users of a web site that stores hits in a database table which is group by domain, then user.

In the report, I created a field: COUNT(Fields!User.Value). The inner group is then is sorted by this field (descending).

So, the logic in this expression is: Every time the user name changes it keeps that in the running total. The running total is reset when the group above it (the user's domain) changes. IIF the running total is less than 11, then Hidden = False.

So, while all values may still be processed, it only shows the top 10.

Hope this helps others

BTW, there is one drawback - since I'm using an expression for the visibility, I can't make this a drill-down field (since drill-down is also a function of visibility). If I select the "Visibility can be toggled by another report item", the report still displays as expected when first rendered, but if it's collapsed then expanded, ALL values for the group will appear, not just the top 10. I guess the visibility expression is only process at initial report rendering time, and not each time the group is collapsed/expanded.

|||

Okay, I'm a but dense today - there is another way to do this. The above will guarantee that ONLY 10 values are returned, but if you remove the sort, and use only the filter for TopN, you'll get the top 10 VALUES (some may duplicate). So, the above solution may return

DOMAIN Logon Count

~~~~~~~~~~~~~~~~~~~~~~~~

Domain1

UserA 10

UserB 10

UserC 9

........etc...upto

UserJ 2

Domain2

etc

However, using only TopN (no sort), may return

Domain1

UserA 10

....

UserJ 2

UserK 2

UserL 2

Domain2

etc

So, TopN by itself (without Sort) can return more than 10 values because there are multiples of the last entry with the same value. My solution in the previous posting depends on Sort, and simply hides everything after the 10th entry.

If someone else finds a way to list just the first 10 entries in a sorted list, please post. I don't like my solution too much because I can't further drill-down into the entries.

Filter and parameter

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

Filter

Hello

I Was wondering if anybody is using the filters to filter data? I've tried and have varying success with the results.
I wanted to create filters on several of the fields. The user clicks the filters to see the corresponding results..the user can also click nothing in the filters and view report..how do we do both using parameters?.
Can somebody help me please...

Thanks for your valuable time.
Sankar

What you are describing is inline filters, which are not available in the current versions of Reporting Services. You can use parameters for this purpose. If you want an 'all' value for the parameters, you will have to do a bit of work. This blog has a technique: http://blogs.msdn.com/chrishays/archive/2004/07/27/AllParameterValue.aspx.