ansaurus

Question

Sum on a group not including one of the rows of the detail in a grouping

Answer 1

A:

set your expression to = sum(iif(data = data4, 0, data))

Zaid Zawaideh 2009-10-23 16:56:58

I tried that but some of my fields are calculated in the report, then it errors out.

Jeff 2009-10-26 21:31:14

You could always replace the field with a calculation. Maybe you can be a bit more specific about the data itself?

Zaid Zawaideh 2009-10-27 03:01:52

=SUM(IIF(Fields!Letter.Value="Sales",0,IIF((Fields!Service_Revenue.Value) = 0, 0, (Fields!Service_Revenue.Value)/(Fields!ROs.Value)))) Here is one example. it errors out after being ran with the "#error"

Jeff 2009-10-27 13:36:00

ok try to break it down. Try this first and see if it works for you=SUM(IIF(Fields!Letter.Value="Sales",0,Fields!Service_Revenue.Value))

Zaid Zawaideh 2009-10-27 13:47:59

so it works on three of my columns, I did the calculations in my proc but it only works on the first three.

Jeff 2009-10-27 15:07:35

The columns that work are just numbers ie 3161 the columns that arent are dollar ammounts and such 176.74 and so on.

Jeff 2009-10-27 15:18:30

could be a datatype casting issue. Make sure you are comparing apples to apples (i.e. that data you are returning from your dataset are all numbers). The formatting into dollars can be done within the report itself

Zaid Zawaideh 2009-10-27 15:56:50

Right Im doing the actual format to a currency in the report, so my first three columsn are just whole numbers 1,2,3,4 I have some percentages and other numbers that display fine in the detail but when I add my conditional sum I get the #error

Jeff 2009-10-27 16:05:17

so is it this part of the conditional that fails: (Fields!Service_Revenue.Value)/(Fields!ROs.Value)?Does it fail on its own in the expression? You are allowed calculations in the sum, i am suspecting other problems here

Zaid Zawaideh 2009-10-27 17:47:33

it seems to be to the conditional stuff not like the data type. There numbers and they sum fine but when I add the condition to them it #errors out.

Jeff 2009-10-27 19:13:15

For the conditionals, you have to make sure that every part evaluates correctly. VB does not do short circuiting, so if there is a problem in one part the whole expression fails, even it that part is not used.I cannot pinpoint where the exact problem is without more information. But at least you are in the right direction.

Zaid Zawaideh 2009-10-28 14:05:44

Im not necessarily a novice to reporting but the VB isnt my stong point, any direction to start troubleshooting would be appreciated.

Jeff 2009-10-28 17:13:04

ansaurus

tags:

views:

answers:

Sum on a group not including one of the rows of the detail in a grouping

related questions