# Crystal Reports: this should be simple but apparently it isn't **URL:** \ **Category:** Factual Questions\ **Created:** [December 15, 2010, 11:41pm UTC](https://boards.straightdope.com/t/crystal-reports-this-should-be-simple-but-apparently-it-isnt/564299 "2010-12-15T23:41:42Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![Roderick\_Femm](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/roderick_femm/32/14875_2.png) [@Roderick\_Femm](https://boards.straightdope.com/u/Roderick_Femm)\ **Post date:** [December 15, 2010, 11:41pm UTC](https://boards.straightdope.com/t/crystal-reports-this-should-be-simple-but-apparently-it-isnt/564299/1 "2010-12-15T23:41:42Z")
I am trying to construct a simple formula for percent of variance, comparing revenue this year vs. last year. A is this year’s revenue (a sum of revenue from work orders for each account). B is last year’s revenue (as above). The basic formula would be (A-B)/B However, you need to account for cases where B = 0, or you will get Division By 0 errors. I want to say that if this year is greater than $0 and last year = $0, then variance is +100%, or if this year is less than or = $0 and last year = $0 then variance is 0%, for all other cases use the basic formula. So this is my formula syntax: if A \> 0 and B = 0 then 1 else if A \<= 0 and B = 0 then 0 else (A-B)/B This does not work, especially in the first case; I don’t get 100% for any combinations. I only get 0% for all cases where B=0, or else I get the correct calculation when B \<\> 0. What am I doing wrong? Roddy ---
**Author:** ![aceplace57](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/aceplace57/32/3500_2.png) [@aceplace57](https://boards.straightdope.com/u/aceplace57)\ **Post date:** [December 16, 2010, 12:02am UTC](https://boards.straightdope.com/t/crystal-reports-this-should-be-simple-but-apparently-it-isnt/564299/2 "2010-12-16T00:02:03Z")
Var = 0; if A \> 0 and B = 0 then Var = 1; If B \> 0 then Var = (A-B)/B; you don’t need to nest the if’s. the 2nd if will Never be true when the first if is true. set var = 0 as a default. it kicks in when neither if is true ---
**Author:** ![aceplace57](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/aceplace57/32/3500_2.png) [@aceplace57](https://boards.straightdope.com/u/aceplace57)\ **Post date:** [December 16, 2010, 12:14am UTC](https://boards.straightdope.com/t/crystal-reports-this-should-be-simple-but-apparently-it-isnt/564299/3 "2010-12-16T00:14:58Z")
last statement Var; that tells crystal to return the value of the variable Var ---
**Author:** ![aceplace57](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/aceplace57/32/3500_2.png) [@aceplace57](https://boards.straightdope.com/u/aceplace57)\ **Post date:** [December 16, 2010, 12:37am UTC](https://boards.straightdope.com/t/crystal-reports-this-should-be-simple-but-apparently-it-isnt/564299/4 "2010-12-16T00:37:30Z")
crap, forgot the : Assignment needs := var := 0; but you knew that I bet. ---
**Author:** ![Roderick\_Femm](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/roderick_femm/32/14875_2.png) [@Roderick\_Femm](https://boards.straightdope.com/u/Roderick_Femm)\ **Post date:** [December 16, 2010, 12:40am UTC](https://boards.straightdope.com/t/crystal-reports-this-should-be-simple-but-apparently-it-isnt/564299/5 "2010-12-16T00:40:23Z")
Heh, no, I’m pretty simple-minded and self-taught where formula construction is concerned. Anyway, thanks and I will try that. Roddy ---
**Author:** ![aceplace57](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/aceplace57/32/3500_2.png) [@aceplace57](https://boards.straightdope.com/u/aceplace57)\ **Post date:** [December 16, 2010, 1:22am UTC](https://boards.straightdope.com/t/crystal-reports-this-should-be-simple-but-apparently-it-isnt/564299/6 "2010-12-16T01:22:12Z")
also you need to declare the variable Var. I do these in the first lines of the formula. Local NumberVar Var; Strings are declared Local StringVar EmpName; I’m guessing your A and B are in your database? So those don’t require declaring. ---
**Author:** ![Roderick\_Femm](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/roderick_femm/32/14875_2.png) [@Roderick\_Femm](https://boards.straightdope.com/u/Roderick_Femm)\ **Post date:** [January 5, 2011, 10:46pm UTC](https://boards.straightdope.com/t/crystal-reports-this-should-be-simple-but-apparently-it-isnt/564299/7 "2011-01-05T22:46:52Z")
Thanks for your help, folks, but I am still having a problem; now I am getting “True” or “False” for the result instead of a numeric value. Here is the actual formula: Local NumberVar Var; Var := 0; if Sum ({@CY YTD}, {@CATEGORIES}) \> 0 and Sum ({@PY YTD}, {@CATEGORIES}) = 0 then Var = 100; if Sum ({@PY YTD}, {@CATEGORIES}) \<\> 0 then Var = (Sum ({@CY YTD}, {@CATEGORIES}) - Sum ({@PY YTD}, {@CATEGORIES}))/Sum ({@PY YTD}, {@CATEGORIES})\*100 The two fields I am trying to compare are subtotals for the current year (CY) and prior year (PY). Can someone wiser than I am please take pity and tell me why I am getting values of “True” or “False” for this formula? What I want to get is the value of (Sum ({@CY YTD}, {@CATEGORIES}) - Sum ({@PY YTD}, {@CATEGORIES}))/Sum ({@PY YTD}, {@CATEGORIES})\*100 Stupid lack of programming training! Roddy ---
**Author:** ![Roderick\_Femm](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/roderick_femm/32/14875_2.png) [@Roderick\_Femm](https://boards.straightdope.com/u/Roderick_Femm)\ **Post date:** [January 5, 2011, 10:49pm UTC](https://boards.straightdope.com/t/crystal-reports-this-should-be-simple-but-apparently-it-isnt/564299/8 "2011-01-05T22:49:07Z")
I just read through the thread again and discovered that I had left off the Var; at the end. Now it works. ::slinking off in embarrassment:: Roddy