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 date for ToDay, some problem with time.

I all.

In a talbe I've a datatime field. for example it contain '16-4-2007 10:45'.

I like to write a SQL that return all record with the date field equals '16-4-2007' (it's not important the time). how to?

thank you.

Try this query to retrive data

select

*from clientswhere clientAddressbetween'2007-04-12 00:00:00.000'and'2007-04-12 23:59:59.999'

The alternative query is

select

*from clientswhere clientAddress>='2007-04-12'and clientAddress<'2007-04-13'

The time format is yyyy-MM-dd and time. Since you are saving time in DB the query should be framed as above

Hope this will help you

|||

try this syntax

print

convert(varchar(20),convert(datetime,'16-04-2006 16:45',105),105)

how it works: convert string to date time with correct format for date string and next convert date time back to string with format you need.

See help for CONVERT in T-SQL help for more format info.

Filter by "all" or actual value issue

I have a string parameter bound to a dataset that contains a "UNION Select
'(All)' " statement to provide the user a way to return all values instead of
just a single value.
I have other integer parameters that use this exact syntax for filtering
(ALL) and they work fine. The syntax looks like this and it doesn't work and
I have no idea why:
Expression: =Iif( Parameters!PropCode.Label = "All", True,
Fields!PropertyCode.Value = Parameters!PropCode.Label)
Operation: = Value: =True
Maybe it has to do with the "(" character? Maybe it has to do with it being
a string? Please advise how I might get past this issue.Did you try:
Iif( Parameters!PropCode.Label = "(All)", True,
Fields!PropertyCode.Value = Parameters!PropCode.Label)
'|||Yes. That was a typo. That is actually what the filter looks like. This
doesn't work.
"Jihong.Liu" wrote:
> Did you try:
> Iif( Parameters!PropCode.Label = "(All)", True,
> Fields!PropertyCode.Value = Parameters!PropCode.Label)
> '
>sql

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

file handling

Is there function in SQL to return just the filename. For eg.
if the filename is 'C:\test\job.txt' , i want to extract job from the
filename. Is is possible? Thanks in advance!There's nothing built-in but you can accomplish the desired result with some
Transact-SQL string functions:
DECLARE @.FilePath varchar(255)
SET @.FilePath = 'C:\test\job.txt'
SELECT REVERSE(LEFT(REVERSE(@.FilePath), CHARINDEX('',
REVERSE(@.FilePath))-1))
Hope this helps.
Dan Guzman
SQL Server MVP
"HP" <HP@.discussions.microsoft.com> wrote in message
news:9368C79B-35DF-4D68-9A56-E97613D74416@.microsoft.com...
> Is there function in SQL to return just the filename. For eg.
> if the filename is 'C:\test\job.txt' , i want to extract job from the
> filename. Is is possible? Thanks in advance!|||HP,
If the path is stored in a column, the T-SQL string functions (i.e.,
CHARINDEX, SUBSTRING, etc...) can be used to return just the name. Also,
there are a few undocumented extended stored procedures that can be used to
work with files using T-SQL.
HTH
Jerry
"HP" <HP@.discussions.microsoft.com> wrote in message
news:9368C79B-35DF-4D68-9A56-E97613D74416@.microsoft.com...
> Is there function in SQL to return just the filename. For eg.
> if the filename is 'C:\test\job.txt' , i want to extract job from the
> filename. Is is possible? Thanks in advance!|||Thanks!
"Jerry Spivey" wrote:

> HP,
> If the path is stored in a column, the T-SQL string functions (i.e.,
> CHARINDEX, SUBSTRING, etc...) can be used to return just the name. Also,
> there are a few undocumented extended stored procedures that can be used t
o
> work with files using T-SQL.
> HTH
> Jerry
> "HP" <HP@.discussions.microsoft.com> wrote in message
> news:9368C79B-35DF-4D68-9A56-E97613D74416@.microsoft.com...
>
>