Welcome To...

1. Welcome To... ReportSmith & ADP PC/Payroll For Windows

2. Welcome To... SQL Derived Fields Tips And Tricks

3. Presented By CharlesCook.com Specializing In ReportSmith Training & Consulting Charles@CharlesCook.com

4. Overview • If Then Else Logic • String Functions • Functions in Functions • Checking Your Syntax • Debugging Derived Fields

5. Overview • If Then Else Logic • @DECODE • @CHOOSE • @IF

6. @DECODE @DECODE(expr,search1,return1,search2,return2,…,[default]) • Equal Comparison IF expr = search1 THEN return1

7. @DECODE @DECODE(code,’A’,amount,0) IF code = ‘A’ THEN amount ELSE 0

8. @DECODE @DECODE(expr,search1,return1,search2,return2,…,[default]) • Looks For Pairs Of Parameters • search1,return1,search2,return2 IF expr = search1 THEN return1 ELSE IF expr = search2 THEN return2 ELSE IF expr = searchn THEN returnn

9. @DECODE @DECODE(sex,’M’,’Male’,’F’,”Female’,’?’) IF sex = ‘M’ THEN ‘Male’ ELSE IF sex = ‘F’ THEN ‘Female’ ELSE ‘?’

10. @DECODE @DECODE(expr,search1,return1,search2,return2,…,[default]) • Unpaired Parameter Is Default [Optional] • If Default is Omitted and There Is No Match, NULL Is Returned

11. @DECODE @DECODE(sex,’M’,’Male’,’F’,”Female’) IF sex = ‘M’ THEN ‘Male’ ELSE IF sex = ‘F’ THEN ‘Female’ ELSE NULL

12. @DECODE @DECODE(expr,search1,return1,search2,return2,…,[default]) • expr May Be Any Data Type • searchn Must Be The Same Data Type As expr • returnn Will Be Forced To The Same Data Type As return1

13. @CHOOSE @CHOOSE(index,value0,value1,…,valuen) • Will Return A Value Based On The index IF index <= 0 THEN value0 IF index = 1 THEN value1 IF index = 2 THEN value2

14. @CHOOSE @CHOOSE((date - startdate),amount,0) IF (date - startdate) <= 0 THEN amount ELSE 0

15. @CHOOSE @CHOOSE((date - startdate),amount,0) IF startdate <= date THEN amount ELSE 0

16. @CHOOSE @CHOOSE(index,value0,value1,…,valuen) • If The index Exceeds The Number Of Values, The Last Value Is Returned

17. @IF @IF(number,value1,value2) • Return value1 If number Is Not Zero • Return value2 If number Is Zero Or NULL

18. @IF gross_pay / @IF(hours,hours,1) IF hours <> 0 THEN hours ELSE 1

19. @IF @IF(test,’YES’,’NO’) IF test <> 0 THEN ‘YES’ ELSE ‘NO’

20. Overview • String Functions • @SUBSTRING • @TRIM • @LENGTH • @FIND

21. @SUBSTRING @SUBSTRING(string,start-pos,length) • Returns The Desired Portion Of string • The Substring Starts At start-pos • The First Character Of string Has A start-pos Of Zero • For A Length Of length

22. @SUBSTRING @SUBSTRING(dept,0,3) dept = 101554 Results = 101 @SUBSTRING(dept,3,3) dept = 101554 Results = 554

23. @SUBSTRING @SUBSTRING(string,start-pos,length) • If start-pos Is Greater Then The Length Of string NULL Is Returned

24. @SUBSTRING @SUBSTRING(dept,6,3) dept = 101554 Results = NULL

25. @TRIM @TRIM(string) • Strips Leading And Trailing Spaces From string • Compresses Multiple Spaces Within string Into Single Spaces

26. @TRIM @TRIM(‘ Charles Cook ‘) Returns ‘Charles Cook’

27. @LENGTH @LENGTH(string) • Returns The Length Of string

28. @LENGTH @LENGTH(‘Charles Cook‘) Returns 12

29. @FIND @FIND(string1,string2,start-pos) • Returns The Starting Position Of string2 Within string1 • The Search Begins At start-pos • The First Character Of string1 Has A start-pos Of Zero

30. @FIND @FIND(‘Charles F. Cook’,’.’,0) Returns 9

31. @FIND @FIND(‘Charles F. Cook’,’oo’,0) Returns 12

32. Functions in Functions • Execute From The Inside Out • The Innermost Functions Are Resolved First

33. Functions in Functions • name = ‘ Charles F. Cook ‘ @SUBSTRING(@TRIM(name),0,(@FIND(@TRIM(name),’.’,0) - 2))

34. Functions in Functions • name = ‘ Charles F. Cook ‘ @SUBSTRING(@TRIM(name),0,(@FIND(@TRIM(name),’.’,0) - 2)) @SUBSTRING(@TRIM(name),0,(@FIND(‘Charles F. Cook’,’.’,0) - 2))

35. Functions in Functions • name = ‘ Charles F. Cook ‘ @SUBSTRING(@TRIM(name),0,(@FIND(@TRIM(name),’.’,0) - 2)) @SUBSTRING(@TRIM(name),0,(@FIND(‘Charles F. Cook’,’.’,0) - 2)) @SUBSTRING(@TRIM(name),0,(9 - 2))

36. Functions in Functions • name = ‘ Charles F. Cook ‘ @SUBSTRING(@TRIM(name),0,(@FIND(@TRIM(name),’.’,0) - 2)) @SUBSTRING(@TRIM(name),0,(@FIND(‘Charles F. Cook’,’.’,0) - 2)) @SUBSTRING(@TRIM(name),0,(9 - 2)) @SUBSTRING(‘Charles F. Cook’,0,7)

37. Functions in Functions • name = ‘ Charles F. Cook ‘ @SUBSTRING(@TRIM(name),0,(@FIND(@TRIM(name),’.’,0) - 2)) @SUBSTRING(@TRIM(name),0,(@FIND(‘Charles F. Cook’,’.’,0) - 2)) @SUBSTRING(@TRIM(name),0,(9 - 2)) @SUBSTRING(‘Charles F. Cook’,0,7) ‘Charles’

38. Checking Your Syntax • Break Your Formula Down @SUBSTRING(@TRIM(name),0,(@FIND(@TRIM(name),’.’,0) - 2))

39. Checking Your Syntax • Break Your Formula Down @SUBSTRING(,,) @TRIM(name) 0 (- 2) @FIND(,,) @TRIM(name) ’.’ 0

40. Debugging Derived Fields • Build It One Step At A Time • Hard Code Parts Until You Understand What Is Going Wrong @TRIM(name)

41. Debugging Derived Fields • Build It One Step At A Time • Hard Code Parts Until You Understand What Is Going Wrong @SUBSTRING(@TRIM(name),0,5)

42. Debugging Derived Fields • Build It One Step At A Time • Hard Code Parts Until You Understand What Is Going Wrong @FIND(@TRIM(name),’.’,0)

43. Debugging Derived Fields • Build It One Step At A Time • Hard Code Parts Until You Understand What Is Going Wrong @SUBSTRING(@TRIM(name),0,@FIND(@TRIM(name),’.’,0))

44. Debugging Derived Fields • Build It One Step At A Time • Hard Code Parts Until You Understand What Is Going Wrong @SUBSTRING(@TRIM(name),0,(@FIND(@TRIM(name),’.’,0) - 2))

45. Presented By CharlesCook.com Specializing In ReportSmith Training & Consulting Charles@CharlesCook.com