Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

iif Statement Help Expand / Collapse
Author
Message
Posted Monday, May 6, 2013 8:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 30, 2013 7:52 AM
Points: 17, Visits: 102
I'm attempting to add field DrAmt based on field TranType. So when TranType = "INT" then add the value in DrAmt. Here is my statement:
=iif(Fields!TrantypeID.Value = "INT",SUM(Fields!DrAmt),0)

When I run the report I receive this error:
[rsAggregateOfInvalidExpressionDataType] The Value expression for the textrun ‘textbox76.Paragraphs[0].TextRuns[0]’ uses an aggregate function with an expression that returned a data type not valid for the aggregate function

Any help would be appreciated.

Thanks.
Post #1449737
Posted Monday, May 6, 2013 10:31 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Monday, July 7, 2014 1:06 PM
Points: 1,478, Visits: 1,025
I think there's an error in your expression. Are you missing the .Value? (Bolded below)
=iif(Fields!TrantypeID.Value = "INT",SUM(Fields!DrAmt.Value),0)
Post #1449791
Posted Monday, May 6, 2013 10:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 30, 2013 7:52 AM
Points: 17, Visits: 102
Oh yeah that would help. Thanks, but the expression does not return the correct value just zero. I know there there data it should be calculating. Any ideas??
Post #1449796
Posted Monday, May 6, 2013 10:49 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Monday, July 7, 2014 1:06 PM
Points: 1,478, Visits: 1,025
Looking at your dataset, what values are in the tranTypeID?
Is there actually a value called 'INT'?


Post #1449799
Posted Monday, May 6, 2013 10:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 30, 2013 7:52 AM
Points: 17, Visits: 102
Yes the vaules are INT, DEP, WHT
Post #1449803
Posted Monday, May 6, 2013 11:00 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Monday, July 7, 2014 1:06 PM
Points: 1,478, Visits: 1,025
I think this will help.
Try wrapping the sum outside the iif
=SUM(iif(Fields!TrantypeID.Value = "INT",Fields!DrAmt,0))
Post #1449804
Posted Monday, May 6, 2013 11:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 30, 2013 7:52 AM
Points: 17, Visits: 102
Still zero amount.
Post #1449805
Posted Monday, May 6, 2013 11:28 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Monday, July 7, 2014 1:06 PM
Points: 1,478, Visits: 1,025
Weird, its gotta be something silly.

Can you post a small sample of your dataset.
It seems fishy that the field your looking is named TranTypeID.Value, but your comparing it to Text value of 'INT'
Post #1449813
Posted Monday, May 6, 2013 12:21 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 30, 2013 7:52 AM
Points: 17, Visits: 102
Here is an example of the data:

TranTypeID DrAmt CrAmt
CHK 0.00 13.37
CHK 0.00 22.50
ACHO 0.00 10.12
WD 0.00 35.00
CHK 0.00 125.00
CHK 0.00 60.23
DEP 25.00 0.00
INT 42.30 0.00
CHK 0.00 85.30
CHK 0.00 100.00
INT 200.00 0.00
Post #1449836
Posted Monday, May 6, 2013 1:04 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Monday, July 7, 2014 1:06 PM
Points: 1,478, Visits: 1,025
Are you grouping by Something else? Feels like something is missing.
Check the attached test Report1, on a row by row basis, the expression is reflecting the amt correctly, so Not sure what the issue is.


  Post Attachments 
Report1.zip (2 views, 1.67 KB)
Post #1449846
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse