filter expression using measures

Hi guys,

I want to filter an MDX query based on a specific measure values, however, the MDX query designer allows you to filter only based on a dimension.

Is there any soulution to resolve this problem.

Sincerely,

--Amde

In the data tab for reporting services, select your dataset you want to filter by in the drop down list. Now, click the "..." to the right. In the filters tab, you can filter the dataset by an expression you provide.

For example, if you wanted to filter the results so that only customers with over 100 purchases were displayed, you could do this:

Expression: =Fields!NumberOfPurchases.value
Operator: >
Value: 100

You should be good to go. Good luck!!

|||thx!

Filter expression for searching "with all the words"

Using VWD I have created a search feature using the LIKE clause. The filter expression on my SQLDataSource allows the user to search the Description field of a database and yield a result that contains the exact word or phrase entered into a textbox. Assuming that the user enters more than one word, my understanding is that the search result is limited to database rows that contain the EXACT phrase (such as found in an advanced Google search using the “with the exact phrase” option). The current filter expression is:

Description LIKE '%{0}%'

For example, if “John Smith” is typed into the search textbox, the results will include a row with:

1. “John Smith is my neighbor”

but NOT a row with

2. “John is my neighbor. His last name is Smith”.

How does one modify the filter expression so that the search result is like the Google “with all the words” search option, where the search results are limited to records in which all the words typed into the textbox are present but not necessarily in the EXACT continuous order? In the example above, BOTH Descriptions would be returned in the search results when “John Smith” in typed into the search textbox.

Thanks for any help you can provide in helping me refine my search options.

Sounds like you're looking for a "full-text search".

read this:http://msdn2.microsoft.com/en-us/library/ms142571.aspx

|||

To search several words,like "John Smith" ,you have to split that into "john" and "smith " then use

Description LIKE '%John%' andDescription LIKE '%Smith%'

but that operation is quite expensive,I recommend you to use fulltext search. You can see the following like for details:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_fulltextsearch.asp

|||

Thanks for the suggestions. I will explore these options.

I do have a couple of general questions about doing searches:

1. Am I correct in assuming that when you use LIKE as I have done (Description LIKE '%{0}%') that the search results that are returned are for the EXACT word or phrase that was searched (i.e., comparable in accuracy - if not speed - to the Google exact phrase search)?

2. Since full-text searches rely on an index they do not necessarily return EXACT results because "noise" words are eliminated? Thus the best that can be hoped for is an exact word order, but not an exact phrase?

3. Can the combined filter expression cited above (Description LIKE '%John%' and Description LIKE '%Smith%) contain parameters instead of the hardcoded search terms (Description LIKE '%{0}%' andDescription LIKE '%{0}%) that allow any combination of terms to be entered into the search textbox?

I look forward to researching the full-text approach and getting it implemented into my search page. Thanks again.

sql

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 (Where?) in an Expression

I have an expression in SSRS that I need some help with. I am trying to just
filter off of a date that is passed from another expression and return all
the Db results where the date is = to the Date in the other expression.
Basically something like this
=Sum (Fields!TransactionAmount.Value) where (Fields!FiscalYear.Value = Parameters!FiscalYearMinus1.Value)
I know where isnt valid in an expression but this is the only way I know to
say it. So lets say the Date is 2006 it would give me the Transaction
amount for all of 2005.
ThanksHi,
have you tried to use the filter tab in the table/matrix properties ?
"rbyers01" wrote:
> I have an expression in SSRS that I need some help with. I am trying to just
> filter off of a date that is passed from another expression and return all
> the Db results where the date is = to the Date in the other expression.
> Basically something like this
> =Sum (Fields!TransactionAmount.Value) where (Fields!FiscalYear.Value => Parameters!FiscalYearMinus1.Value)
> I know where isnt valid in an expression but this is the only way I know to
> say it. So lets say the Date is 2006 it would give me the Transaction
> amount for all of 2005.
> Thanks|||Thanks for the reply
Ok I tried that but I got an error message about Data types not matching.
If I do the filter on the whole table will affect oter dates in the Table? i
just need the filter to run on one cell of the table.
"Cedric" wrote:
> Hi,
> have you tried to use the filter tab in the table/matrix properties ?
> "rbyers01" wrote:
> > I have an expression in SSRS that I need some help with. I am trying to just
> > filter off of a date that is passed from another expression and return all
> > the Db results where the date is = to the Date in the other expression.
> > Basically something like this
> >
> > =Sum (Fields!TransactionAmount.Value) where (Fields!FiscalYear.Value => > Parameters!FiscalYearMinus1.Value)
> >
> > I know where isnt valid in an expression but this is the only way I know to
> > say it. So lets say the Date is 2006 it would give me the Transaction
> > amount for all of 2005.
> >
> > Thanks|||Hi,
the filter will apply to all the data on the table. The only way I know is
to create another table with a unique cell containing your data filtered by
the parameter .
Cédric
"rbyers01" wrote:
> Thanks for the reply
> Ok I tried that but I got an error message about Data types not matching.
> If I do the filter on the whole table will affect oter dates in the Table? i
> just need the filter to run on one cell of the table.
> "Cedric" wrote:
> > Hi,
> >
> > have you tried to use the filter tab in the table/matrix properties ?
> >
> > "rbyers01" wrote:
> >
> > > I have an expression in SSRS that I need some help with. I am trying to just
> > > filter off of a date that is passed from another expression and return all
> > > the Db results where the date is = to the Date in the other expression.
> > > Basically something like this
> > >
> > > =Sum (Fields!TransactionAmount.Value) where (Fields!FiscalYear.Value => > > Parameters!FiscalYearMinus1.Value)
> > >
> > > I know where isnt valid in an expression but this is the only way I know to
> > > say it. So lets say the Date is 2006 it would give me the Transaction
> > > amount for all of 2005.
> > >
> > > Thanks|||Try something like this expression in the cell expression:
=Sum(IIF(Fields!FiscalYear.Value =Parameters!FiscalYear.Value.AddYears(-1),Fields!TransactionAmount.Value,0))
rbyers01 wrote:
> Thanks for the reply
> Ok I tried that but I got an error message about Data types not matching.
> If I do the filter on the whole table will affect oter dates in the Table? i
> just need the filter to run on one cell of the table.
> "Cedric" wrote:
>> Hi,
>> have you tried to use the filter tab in the table/matrix properties ?
>> "rbyers01" wrote:
>> I have an expression in SSRS that I need some help with. I am trying to just
>> filter off of a date that is passed from another expression and return all
>> the Db results where the date is = to the Date in the other expression.
>> Basically something like this
>> =Sum (Fields!TransactionAmount.Value) where (Fields!FiscalYear.Value =>> Parameters!FiscalYearMinus1.Value)
>> I know where isnt valid in an expression but this is the only way I know to
>> say it. So lets say the Date is 2006 it would give me the Transaction
>> amount for all of 2005.
>> Thanks