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
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_
The PDC is a money field, the New_Old_CC is a bit field
ASKER
=IIf(IIf(Not(Isnothing(Fie lds!New_Ol d_CC.Value ) AND Fields!New_Old_CC.Value = 1), Fields!PDC.Value,0)
not sure how to form this
not sure how to form this
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.Val
Ok, the report rendered...some were summed but in some of the fields for that column I got an error:
[rsRuntimeErrorInExpressio
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?