SUM IIF condition int not working on special number

[Alexander]

Hello all,

i have a problem which i cannot solve. It´s a mistery to me.

I have a table where i want to sum up the weight if a second field has a special number in it.

The follwing should sum up every field where the category transporcat is 1,2 or 3. The categoryfield is defined as string but in the case here there are only numbers in it. In this case the transportcat 1 exists one time, 2 exists one time, 3 does not exist. The weight vor cat 1 and 2 is identical with 5. So there are no differences between the data exept the transportcat.

= SUM(IIF(Fields.transportcat=3,Fields.weight,'' )) or = SUM(IIF(Fields.transportcat="3",Fields.weight,'' )) -> Result: '' which is correct

= SUM(IIF(Fields.transportcat=2,Fields.weight,'' )) or = SUM(IIF(Fields.transportcat="2",Fields.weight,'' )) -> Error - Should be '5'

Error: the input format has the wrong format?

= SUM(IIF(Fields.transportcat=1,Fields.weight,'' )) or = SUM(IIF(Fields.transportcat="1",Fields.weight,'' )) -> Result: '5' which is correct

The test data for the IIF comparison is identical except that the value of the transportcat field is different. I can also use other (4,5,6) and it is still working. Only when i use the number 2 it doesn´t ?

Regards Alex

[Mads]

Hi Alexander,

I am sorry to hear you are having issues making the expression return data as expected. By using the details in your post I tried testing the expression in my own report, but was not able to produce an error like described. I will go through a few suggestions to hopefully help you solve this.

Moving the IIF and changing it to conditional operator

If I understand the description correctly, you want to sum the weight field if 'transportcat' is a certain number. To ensure that the SUM( ) function is not triggering an error when it is not supposed to actually sum anything, I suggest changing the order of functions. First check what the field 'transportcat' is, and then use the SUM function.

In addition, the IIF( ) function will trigger both the true and false expression no matter the condition, but will determine what to display based on the condition. This means that if one of the true/false expressions result in an error, it will be triggered. By using the conditional operator ' ?: ', only one of the expressions is triggered.

Based on these suggestions, you could try writing the expression like the following:

= Fields.transportcat=3 ? SUM(Fields.weight) : ''

Converting field to integer

Another suggestion is to convert the field to an integer. You could try this for both the conditional statement and the SUM function to ensure the correct format is used. I suggest updating the expression to the following:

= CInt(Fields.transportcat)=2 ? SUM(CInt(Fields.weight)) : ''

Wrapping Up

I hope the suggestion above removes the error and helps you find the cause of the issue. It is difficult pointing out the exact issue as I was not able to reproduce the behavior. If the suggestions above do not solve this, could you explain a few more details about your scenario and report-setup? Maybe you could provide some sample data for me to use. Screenshots would also be helpful. Is the error message "Error: the input format has the wrong format?"? 

Let me know how it works out, if you run into challenges, or if you have any other questions.

Regards, Mads Progress Telerik