Expressions not working if Matrix cell is not populated by dataset

Hi
I'm using the following expression for the BackGroundColor in a matrix
detail cell:
=IIF(Fields!ID_Quarter.Value = "Cumulative", "Gainsboro","Transparent")
This is checking the value of another cell before setting the
BackGroundColor value.
I'm also using an expression for the bottom border style:
=IIF(Fields!ID_Quarter.Value = "Cumulative", "Solid","Dotted")
This is checking the value of the same cell that the BackGroundColor
expression checks.
These expression ONLY work if the cell has been populated from the dataset
(in my matrix some cells don't have a value - this is expected).
My Cell has three expressions all up, the remaining expression checks it's
own value for nothing and then put's a "0" in the cell if true.
=IIF(Fields!Deep_SSI.Value = Nothing,"0",Sum(Fields!Deep_SSI.Value))
This expression always works.
Is there a problem with using more than one expression on a cell' I think
not because they all work fine if the cell is populated.
Any thoughts?
SimonBHello...
RE: Expressions not working if Matrix cell is not populated by dataset!
Has anyone got any clues on this problem?
Simon
"simonb" wrote:
> Hi
> I'm using the following expression for the BackGroundColor in a matrix
> detail cell:
> =IIF(Fields!ID_Quarter.Value = "Cumulative", "Gainsboro","Transparent")
> This is checking the value of another cell before setting the
> BackGroundColor value.
> I'm also using an expression for the bottom border style:
> =IIF(Fields!ID_Quarter.Value = "Cumulative", "Solid","Dotted")
> This is checking the value of the same cell that the BackGroundColor
> expression checks.
> These expression ONLY work if the cell has been populated from the dataset
> (in my matrix some cells don't have a value - this is expected).
> My Cell has three expressions all up, the remaining expression checks it's
> own value for nothing and then put's a "0" in the cell if true.
> =IIF(Fields!Deep_SSI.Value = Nothing,"0",Sum(Fields!Deep_SSI.Value))
> This expression always works.
> Is there a problem with using more than one expression on a cell' I think
> not because they all work fine if the cell is populated.
> Any thoughts?
> SimonB

Expressions in RS

Hi,
Im using the following expression to calculate the percentage
=iif(Fields!BudgetedRevenue.Value = 0,0,(Fields!BudgetedRevenue.Value-
Fields!BudgetedCostStan.Value- Fields!BudgetedCostOT.Value-
Fields!BudgetedCostExp.Value)/ Fields!BudgetedRevenue.Value)
if the Fields!BudgetedRevenue.Value = 0 it is returning the #error,instead
of 0.
Fields!BudgetedRevenue.Value is of datatype money.
Thanks in advance.Try using this:
=iif(isnothing(Fields!BudgetedRevenue.Value),0,(Fields!BudgetedRevenue.Value-
Fields!BudgetedCostStan.Value- Fields!BudgetedCostOT.Value-
Fields!BudgetedCostExp.Value)/ Fields!BudgetedRevenue.Value)
I belive the isnothing() should take the place of the = 0 and might help
your formula calculate correctly.
Let me know if that didn't work.
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"CCP" wrote:
> Hi,
> Im using the following expression to calculate the percentage
> =iif(Fields!BudgetedRevenue.Value = 0,0,(Fields!BudgetedRevenue.Value-
> Fields!BudgetedCostStan.Value- Fields!BudgetedCostOT.Value-
> Fields!BudgetedCostExp.Value)/ Fields!BudgetedRevenue.Value)
> if the Fields!BudgetedRevenue.Value = 0 it is returning the #error,instead
> of 0.
> Fields!BudgetedRevenue.Value is of datatype money.
> Thanks in advance.

Expression to alter the font size

I'm trying to use an expressions like these to alter the font size...
iif(Len(Fields!MyField.Value) > 12, "7pt","8pt")
iif(Len(Fields!MyField.Value) > 12, 7pt,8pt")
iif(Len(Fields!MyField.Value) > 12, 7,8)
Regardless of which expression I use, I get this error:
"<my expression> cannot be parsed as a unit because it does not contain
numeric values. Examples of valid unit strings are "1pt" and ".5in".
Any suggestions on how i can check the length of a string and alter the
font size in the table?
Thanks,
James
--This worked for me, =iif(Len(Fields!MyField.Value)>12,"7pt","8pt"). Make
sure that you are putting the expression in the FontSize property with the =.
daw
"James" wrote:
> I'm trying to use an expressions like these to alter the font size...
> iif(Len(Fields!MyField.Value) > 12, "7pt","8pt")
> iif(Len(Fields!MyField.Value) > 12, 7pt,8pt")
> iif(Len(Fields!MyField.Value) > 12, 7,8)
> Regardless of which expression I use, I get this error:
> "<my expression> cannot be parsed as a unit because it does not contain
> numeric values. Examples of valid unit strings are "1pt" and ".5in".
> Any suggestions on how i can check the length of a string and alter the
> font size in the table?
> Thanks,
> James
> --
>

expression sum and iif help?

Hi All,

SUM(IIF(Fields!NO.Value >= 1, Fields!tsHours.Value,0))

i write this expression inside the group footer row, it give a #error when generating report.

please help

Cheers

Nick

Hi Nick,

The possible reason for this error is that the Sum function can only add fields of the same data type. It is quite possible in this case that the data types of the fields in the TRUE and FALSe part of the If statement is different. Try making the data types same of both the fields by usinf the Conversion functions available.

Hope this helps.

Regards,

Pradeep

|||

Hi,

I think ure iif() is returning varying datatypes. 0 being a integer and Fields!tsHours.Value returning some other datatype.

Use a conversion function such as CDbl() on both fields.For eg: CDbl(Fields!tsHours.Value) and CDbl(0).This makes either of the values returned as Double.

eg:

Sum(IIf(Fields!NO.value>=1,CDbl(Fields!tsHours.Value),Cdbl(0)))

Somiya

|||

Thanks Chaubey and Somiya,

You guys are right, after i explicitly convert to same type , then it works.

Cheers

Nick

Expression for visibility of a chart

Hi,

I wrote the following expression in the visibility(Hidden) property of a chart.

=Iif(DateDiff("Hour",Parameters!StartDate.Value ,Parameters!EndDate.Value)< 24,False,True)

And it is giving me the following error.

An error occured during local report processing.

The Hidden expression for the chart 'chart1' contains an error: Argument 'Interval' is not a valid value.

What is the problem?

Sincerely,

Amde

Is the report parameters' data type defined as DateTime?

Try this:
=Iif(DateDiff("h", CDate(Parameters!StartDate.Value), CDate(Parameters!EndDate.Value)) < 24,False,True)

-- Robert

|||

Yes! Now it is working. The thing is I tried by adding the CDate function, but the problem is with the interval value.

In the books oline it is documented to use the following interval: Year, Quarter, Month, Day, Hour, Minute, Second, or Week for the DateAdd and DateDiff functions. Why is this not working?

Amde

|||

The interval value must be either an enum value or a string value.

You have to use either DateInterval.Hour (enum value) or "h" (string value) for the first argument of the DateDiff function.

-- Robert

Expression Error: Attempted to divide by zero.

i'm trying to calculate a percent field with the following expression and
getting the error message above:
=IIF(Fields!valorPrevisto.Value = 0, 0, Fields!valorRealizado.Value /
Fields!valorPrevisto.Value)
what am i doing wrong ?
thanks.Hi levogiro -
Check out this thread. It describes the problem and offers a couple of
solutions.
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/481dcece9e84c707/d34656d64a7f39a9?q=divide+by+zero+expression&rnum=4&hl=en#d34656d64a7f39a9
HTH...
--
Joe Webb
SQL Server MVP
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811
I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)
On Tue, 13 Sep 2005 07:08:05 -0700, "levogiro"
<levogiro@.discussions.microsoft.com> wrote:
>i'm trying to calculate a percent field with the following expression and
>getting the error message above:
>=IIF(Fields!valorPrevisto.Value = 0, 0, Fields!valorRealizado.Value /
>Fields!valorPrevisto.Value)
>what am i doing wrong ?
>thanks.

expression

How to call a custom function from an expression under iif.can anybody please
help.=iif(code.FunctionName(...)=...,TrueResult,FalseResult)
Charles Kangai, MCT, MCDBA
"Sandy" wrote:
> How to call a custom function from an expression under iif.can anybody please
> help.|||It is not working.It is given an error expression expected.Please help
"Charles Kangai" wrote:
> =iif(code.FunctionName(...)=...,TrueResult,FalseResult)
> Charles Kangai, MCT, MCDBA
> "Sandy" wrote:
> > How to call a custom function from an expression under iif.can anybody please
> > help.