Skip to Content
0

Crystal report does not return the value as shown in SQL query

Aug 22, 2017 at 03:45 PM

74

avatar image

Hello,

I am struggling to show the BreakTime value if it meets the condition but, Crystal report shows the zero value. If I execute the SQL report which is generated by Crystal report it has non-null values. Here is the formula created in Crystal report:

If {Events.SCHID}=Previous({Events.SCHID}) and {EventStrings.EVSTRNAME} = Previous({EventStrings.EVSTRNAME}) and {Events.EVSTRID}={EventStrings.EVSTRID} and {Events.ACTIVITY} = 8 then ({Events.DWELLTIME})/3600

Please let me know if you have any other questions. I am very new to Crystal report. Hopefully was able to explain my concern. Thanks

GG

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Brian Dong Aug 22, 2017 at 09:49 PM
0

Hi Gurpreet,

Your formula doesn't have an Else. If the conditions aren't met then Crystal assumes you want a 0 because of the expected return value. If you want something else to show, add an Else statement.

Brian

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Thank you Brian for the reply. As you have advised I added "Else 0" at the end, still I am not getting null value. As you see in the provided screenshot 701 meets the condition and it should show DwellTime 1.33. I am not sure what else to do. I would be very thankful to you if you help me on this. Thanks

If {Events.SCHID}=Previous({Events.SCHID}) and {EventStrings.EVSTRNAME} = Previous({EventStrings.EVSTRNAME}) and {Events.EVSTRID}={EventStrings.EVSTRID} and {Events.ACTIVITY} = 8 then ({Events.DWELLTIME})/3600

Else 0

0

Hi Gurpreet,

When comparing current and previous records, you need to also check to see if the current record is new or not. Previous returns a null if there is no previous record. To do this, include the PreviousIsNull function at the beginning like:

If (PreviousIsNull ({Events.SCHID})) or 
	({Events.SCHID}=Previous({Events.SCHID}) 
	and {EventStrings.EVSTRNAME} = Previous({EventStrings.EVSTRNAME}) 
	and {Events.EVSTRID}={EventStrings.EVSTRID} 
	and {Events.ACTIVITY} = 8)
then ({Events.DWELLTIME})/3600
Else 0;

Make sure you have the parentheses correct.

Brian

0
avatar image
Former Member Aug 23, 2017 at 11:47 AM
0

Hi

Also, open the formula and on top of formula editor, you will see 'Exception for Nulls', click on the drop down and select 'Default Values for Nulls' and save the formula and see.

Thanks,

sastry

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thank you Sastry, I tried as you said but no luck.

Gurpreet

0