Link to home
Start Free TrialLog in
Avatar of dba123
dba123

asked on

Help with SSRS 2005 Expression (If statement)

This is the first time I'm trying the rdl language for SQL Server Reporting Services 2005.
I have taken a look at this page (http://msdn2.microsoft.com/ms157328.aspx) but it hasn't solved my error due to my malformed IIf statement below so I'm not sure what I'm malforming here.

I simply want to sum up the PDC values if the field New_Old_CC has a value of 1, else return 0 if no 1s are found.  I'm not sure what is malformed here:

=IIf(First(Fields!New_Old_CC.Value, "DataSet1") = 1, Sum(Fields!PDC.Value, "DataSet1"),0)

 The PDC is a money field, the New_Old_CC is a bit field
Avatar of dba123
dba123

ASKER

Ok, this is weird.  When you are in the expressions editor, if your report has more than one dataset defined like mine (because I'm doing some other calcs), then you see this in the left pain:
Fields(DataSet1)

Datasets

What I was doing was using the field values in my DataSets area...in it it had an additional "DataSet1" option for some reason, even though it was already showing Fields(DataSet1) outside the Datasets option on the left.  

So when using the DataSet1 fields inside Datasets, I was in turn adding fields to my IIf statement that were actually in the report already...they were preceeded by SUM (because they're in a group) so this is not going to work obviously.

So I realized, I had to use the Fields(DataSet1) fields which were my original fields and then changed it to this:

=IIf(Fields!New_Old_CC.Value = 1, SUM(Fields!PDC.Value),0)

Ok, the report rendered...some were summed but in some of the fields for that column I got an error:

[rsRuntimeErrorInExpression] The Value expression for the textbox ‘textbox86’ contains an error: Input string was not in a correct format.

So I realized, that there are cases where New_Old_CC is null so I need to somehow check this...maybe do an IIf inside my IIf to check IsNothing I guess?
Avatar of dba123

ASKER

=IIf(IIf(Not(Isnothing(Fields!New_Old_CC.Value) AND Fields!New_Old_CC.Value = 1), Fields!PDC.Value,0)

not sure how to form this
ASKER CERTIFIED SOLUTION
Avatar of simon_kirk
simon_kirk
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial