Thursday, March 29, 2012

filter expression "LIKE" together with report paramter in RS

Hi there
I have a text field that I would like to filter with a LIKE expression, i.e.:
WHERE (dbo.TBL_SchiBeriBeso.Bemerkung LIKE '%BSK%')
When I use this filter expression in the data query of Reporting Services
everything goes well.
But when I try to use this filter expression in the the report item (table)
I don't get this filter expression working. How do I write this filter
expression in the filter definition in the properties of my table? How
exactly is the right way to write the filter value (BSK) so that the filter
gets me every text field that has BSK somewhere in its contents?
Thanks a lot for your help!
Judith.If you are using a parameter in the Like expression with a wild card try it
like this:
WHERE (dbo.TBL_SchiBeriBeso.Bemerkung LIKE '%' + @.BSK + '%')
"j schuetz" <jschuetz@.discussions.microsoft.com> wrote in message
news:52BC887E-C77F-4FC7-9DDE-ED5B20826EAF@.microsoft.com...
> Hi there
> I have a text field that I would like to filter with a LIKE expression,
> i.e.:
> WHERE (dbo.TBL_SchiBeriBeso.Bemerkung LIKE '%BSK%')
> When I use this filter expression in the data query of Reporting Services
> everything goes well.
> But when I try to use this filter expression in the the report item
> (table)
> I don't get this filter expression working. How do I write this filter
> expression in the filter definition in the properties of my table? How
> exactly is the right way to write the filter value (BSK) so that the
> filter
> gets me every text field that has BSK somewhere in its contents?
> Thanks a lot for your help!
> Judith.|||Thank you for your input. I know now how to solve the problem. The thing is
that the syntax of the filter expression in the table properties window is
different from the syntax in the query.
The value field in the filter expression of the table properties has to have
the following syntax: [= "*" & Parameters!Stichwort.Value & "*"], i.e. the
wild card symbol is * instead of %.
With this syntax the LIKE operator in the table properties window works just
fine!
"Steve Dearman" wrote:
> If you are using a parameter in the Like expression with a wild card try it
> like this:
> WHERE (dbo.TBL_SchiBeriBeso.Bemerkung LIKE '%' + @.BSK + '%')
>
> "j schuetz" <jschuetz@.discussions.microsoft.com> wrote in message
> news:52BC887E-C77F-4FC7-9DDE-ED5B20826EAF@.microsoft.com...
> > Hi there
> >
> > I have a text field that I would like to filter with a LIKE expression,
> > i.e.:
> > WHERE (dbo.TBL_SchiBeriBeso.Bemerkung LIKE '%BSK%')
> >
> > When I use this filter expression in the data query of Reporting Services
> > everything goes well.
> >
> > But when I try to use this filter expression in the the report item
> > (table)
> > I don't get this filter expression working. How do I write this filter
> > expression in the filter definition in the properties of my table? How
> > exactly is the right way to write the filter value (BSK) so that the
> > filter
> > gets me every text field that has BSK somewhere in its contents?
> >
> > Thanks a lot for your help!
> >
> > Judith.
>
>

Filter Expression

Hi,

I'm trying to create a query where the results are restricted by the date selected. When the user selects two different dates, the query works. However, if the user wanted to see receivals for only one day, the query returns nulls even though there are receivals for that day. Below is the code:

SELECT {{[Measures].[Tonnes]}} on 0,
NonEmptyCrossJoin({{Filter([Date].[Day].members, [Date].currentmember.name = "2006-10-16")}}, {{[Transaction].[Closing CarryOver].[IN].[Receival tonnes]}}, {{[Measures].[Tonnes]}}, 2) DIMENSION PROPERTIES MEMBER_CAPTION on 1
FROM GrainMovements

The "2006-10-16" is the date selected by the user. Any ideas why the filter doesn't like just returned one date, would be extremely helpful! Thanks.

First, you should lose NonEmptyCrossJoin from the query, which is hurting here, and also may produce wrong results if you have more than one measure group.

The simplest query to do what you want is

SELECT {[Measures].[Tonnes]} on 0,
NON EMPTY {([Date].[Day].[2006-10-16], [Transaction].[Closing CarryOver].[IN].[Receival tonnes])} DIMENSION PROPERTIES MEMBER_CAPTION on 1
FROM GrainMovements

Filter Expression

Hi

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

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

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

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

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

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

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

Filter Error: "..processing of filter expression..cannot be perfor

My results return a tinyint column which is either 0,1,2.
When adding a filter to the matrix it seems ok
blah.value = 2
but when running the report i get the following error:
--error--
An error has occured during report processing.
The processing of filter expression for the matrix 'matrix1' cannot be
performed. The comparison failed. Please check the data type returned by the
filter expression.
--enderror--
i have also tried
blah.value = "2"
with no success
anyone know why?a-ha!
this works
Expression
=CInt(Fields!salesGroup.Value)
Operator
=
Value
=2
frankly this is crap
i am returning a number and comparing to a number
- why should i have to convert a number to er a er number?
- why do i have to put an equals in front of the number?
"adolf garlic" wrote:
> My results return a tinyint column which is either 0,1,2.
> When adding a filter to the matrix it seems ok
> blah.value = 2
> but when running the report i get the following error:
> --error--
> An error has occured during report processing.
> The processing of filter expression for the matrix 'matrix1' cannot be
> performed. The comparison failed. Please check the data type returned by the
> filter expression.
> --enderror--
>
> i have also tried
> blah.value = "2"
> with no success
> anyone know why?
>

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 duplicate records

    Hi

    Have been given the task of trying to write reports in Crystal after someone found out I new one of Access from the other.

    The report is taking data from SAGE database with custom written tables. The problem I have is that one of the custom table has not been well written allowing multiple identical entries.

    The table in question holds data for each delivery note posted on SAGE. In this table are field I need relating to Product Number, Quantity and Nett Weight. Due to the way data has been stored I can have up to 16 records (effectively duplicates - same part number, quantity, nett weight) when I only want one record displayed.

    Is it possible to write a filter that basically says that if for a given delivery number there is more than 1 record with the same Product Number, Quantity then filter the number of records down to one? If this is not possible, would it be possible to do the same but specifying to pick the record with the highest nett weight? assuming that if there were two identical records for all of the aforementioned fields that it could still bring this down to one?You can do it in your Query:

    SELECT ProductNumber, Quantity, 'NettWeight' = MAX(NettWeight)
    FROM TableName
    GROUP BY ProductNumber, Quantity

    (I use SQL Server 7, so you may need to adjust the syntax a bit to work in your database.)

    - or -

    You can do it in Crystal Reports. There should be a property called SuppressDuplicates (or something similar, I don't have CR in front of me). If you set it to True, Crystal should display only unique records. I think that property is available for fields as well as sections, but I'm not sure. Also, I use CR 8.5, so if you're using a different version, SuppressDuplicates may not exist at all or it may be called something different.

    Filter double results from query

    We are working on an application which aggregates news articles from various sources. The problem is, when querying the database, is that there are a lot of double results because some of the sources get their news from the same press agency. To clarify things a bit here's a link to an example:

    http://84.244.146.27/Zoekresultaten.aspx?SearchQuery=Amsterdam

    As you can see, there are a lot of the same results. What we want is to filter out the double results irrespective of the source. In other words; when the query encounters the first result, display that and leave out the other same results.

    I've tried filtering them out by using DISTINCT on the 'news header' / results, but that does not seem to work. Any ideas on how to get this done?

    Any help is greatly appreciated!

    Providing the aggregator select is an infrequent action, you can use a CURSOR within it to select just the first record to a buffer table which the page will read.

    |||

    Thanks Tatworth for your answer! I'm afraid that it sounds a bit like Chinese to me though...

    The aggregator is run every couple of minutes and of course it would be best to filter the results in that step, then filtering on querying results. There's a slight problem though with the aggregator script. It's in PHP :( and PHP and MSSQL don't play along that nicely with eachother as far as I have experienced.

    Is there a way to filter within the select query?

    |||

    Yes, as an example:

    14-11-2007

    10:52

    Marokkaanse vaders in actie in Amsterdam-West

    BNdestem BNdestem

    |||

    If your answer is 2005 or Express, and you want the earliest record, this syntax will do it for you:

    SELECT *

    FROM (

    SELECT {the fields from your original query},row_number() OVER (PARTITION BY {the list of key fields} ORDER BY {Your datetime field} ASC) AS TheRank

    {the rest of your original query}

    ) t1

    WHERE TheRank=1

    Example:

    SELECT *FROM (SELECT NewsDate,NewsTitle,NewsLink,row_number()OVER (PARTITIONBY NewsTitleORDER BY NewsDateASC)AS TheRankFROM NewsTable) t1WHERE TheRank=1
    |||

    Thanks very much Motley! It works like a charm!