Report Builder Question

Kinetic ERP
,
1

Hi, Has anyone encountered this issue? When I sum a field that has a 0 value, it results in an error.
image

the field is calculated

image
image1081×255 11 KB

image

2

Null handling with comparison operator “<=”? Just a shot in the dark. Have you tried handling nulls with an OR?

2 Likes
3

What @Conley.TUC said.

You do not have anything in your expression to handle NULL dates.

2 Likes
4

image
image902×368 24.6 KB

I tried adding it, but it still doesn’t work.

5

Try this.

Iif((IsNothing(Fields!OrderDtl_NeedByDate.Value)) OR (Fields!OrderDtl_NeedByDate.Value > DateSerial(Year(Now()), Month(Now()), "1").AddMonths(1).AddDays(-1)), 0, Fields!OrderDtl_DocExtPriceDtl.Value)
2 Likes
6

image

Thanks @jkane, but I’m still encountering the error.

7

Try changing the 0 to 0.00

8

still having an error

9

Try this. Create a new calculated field and put your expression in there, except make the then/else boolean. Add the field to your report and see if it is returning true/false correctly.

10

yes its giving me true or false

image

=IIf(IsNothing(Fields!OrderDtl_NeedByDate.Value) Or Fields!OrderDtl_NeedByDate.Value > DateSerial(Year(Now()), Month(Now()), “1”).AddMonths(1).AddDays(-1), True, False)

11

Awesome!

Now change the DateAmt formula to Iif(Try = false, docamount, 0.00)

12

Field : Try
=IIf(IsNothing(Fields!OrderDtl_NeedByDate.Value) Or Fields!OrderDtl_NeedByDate.Value > DateSerial(Year(Now()), Month(Now()), “1”).AddMonths(1).AddDays(-1), True, False)

Field: DocAmt1
=Iif(Fields!Try.Value= false, Fields!OrderDtl_DocExtPriceDtl.Value, 0.00)

Sum(DocAmt1)

image

Still having error Output

13

Why are you showing the 0 values as lines? I’m wondering if that may be it.

14

you mean sir this
image

Even with it unchecked, I’m still encountering the issue.

15

Something that can narrow down the offending issue is to temporarily drop an un-aggregated table including all of the values in that field on the report. If other fields are necessary to drill into a specific record, include those too. Add a field to the table that’s doing the math you want to do, scoped to each row.

If some but not all rows return an error, you have more detail about the cause. If all rows universally return the error, there’s something going on with datatypes or the math or something else in general.

1 Like
16

Thank you, @jkane , I got it now.

I moved the Calculation field to the BAQ instead of the Report Builder, and everything is good now.

Thank you again for your help!

17

Nice solution. Happy to help.

1 Like