Print Page | Close Window

Need to report zero amounts too

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=9863
Printed Date: 07 May 2024 at 1:17am


Topic: Need to report zero amounts too
Posted By: dee-jo
Subject: Need to report zero amounts too
Date Posted: 29 Apr 2010 at 9:42am

I am using @BalanceAccum to sum the transaction total for costs and billings to display on a single line item per cost code.  However, I am not getting anything to display when the amount is zero.  I still need zero amounts to display.  The detail is supressed, with the amounts displayed in the GF2.  Subtotals by division in GF1.


Replies:
Posted By: DBlank
Date Posted: 29 Apr 2010 at 11:46am
try changing your formula to 'Use default values for Nulls' instead of 'exceptions for Nulls'

Posted By: dee-jo
Date Posted: 03 May 2010 at 3:30am

I found out how to do this, but now it is giving an error for a field with a ToNumber conversion.  When I print preview after marking "use default values for nulls", then it says The String is Non-numeric, and opens up my ToNumber formula.

Posted By: DBlank
Date Posted: 03 May 2010 at 3:55am
What is your formula that spits the error?

Posted By: dee-jo
Date Posted: 03 May 2010 at 4:25am
I did fix the errors for the ToNumber.

However, my report is still not returning a value when there are no transactions.  I need it to report all cost codes, even if there were no actual costs.

Posted By: dee-jo
Date Posted: 03 May 2010 at 4:30am
Here is what I'm trying to do:
 
If IsNull({JC_TRANSACTIONS.AMOUNT}) then "0" else ({JC_TRANSACTIONS.AMOUNT})
 
However it tells me a string is required and points to the second ({JC_TRANSACTIONS.AMOUNT}).
 
Every cost code that has had no activity is being skipped on the report, but I need every cost code to print, because it would have revenue and budget associated with it.

Posted By: DBlank
Date Posted: 03 May 2010 at 4:36am
Don't use " " around the zero. that is making it a string not a numeric value. 
Note: setting any formula that uses this field to "Use default values for Nulls" already does this function.
 
If IsNull({JC_TRANSACTIONS.AMOUNT}) then 0 else ({JC_TRANSACTIONS.AMOUNT})

Posted By: dee-jo
Date Posted: 03 May 2010 at 4:49am
I'm using the Display NumberVar.  It's still not printing zeros.
 
This is my display:
WhilePrintingRecords;
NumberVar BalanceAccumulator
 
This is my accumulator:
WhilePrintingRecords;
NumberVar BalanceAccumulator := NumberVar
mailto:BalanceAccumulator+%7b@ACTUAL2 - BalanceAccumulator+{@ACTUAL2 } - mailto:%7b@BILLED - {@BILLED } + 0
 
This is my initializer:
WhilePrintingRecords;
NumberVar BalanceAccumulator := 0;
 
This is my ACTUAL2 formula:
If IsNull({JC_TRANSACTIONS.AMOUNT}) then 0 else ({JC_TRANSACTIONS.AMOUNT})
 
I'm not sure what I'm still doing wrong. 

Posted By: DBlank
Date Posted: 03 May 2010 at 5:17am
What is @billed ?
if that has NULLS that are not addressed it will display nothing as well

Posted By: dee-jo
Date Posted: 03 May 2010 at 6:14am
This is the formula for the @BILLED:
 
if{JC_TRANSACTIONS.CLASS}="16" then {JC_TRANSACTIONS.AMOUNT} else 1
 
I still cannot get the zeros to return.  In this case, I even asked for 1 if there were no class 16 transactions.
 
When I do the initial link of tables I am linking Cost Code Tables (holds budget, contract) and Transactions (holds actual and billed).  I have the Cost Code table on the Left (I want all of those reported) and then the Transaction table on the right with arrows between.  Now I'm questioning if this is correct.

Posted By: DBlank
Date Posted: 03 May 2010 at 6:31am
If your @billed formula is not using "Default values for NULLS" it will not evaluate (return NULL) when either CLASS or AMOUNT is Null.
 
Showing row level data what do you want to calculate and in what manner?

Posted By: dee-jo
Date Posted: 03 May 2010 at 6:48am
Now that I changed the join, everything is working fine, except one last item.  It now doesn't let my @CAC subtotal fields work.

They were set up as running totals, and the @CAC formula was:
 
ToNumber ({JC_CONTROLESTIM.USER2})
 
However, now it says "The String is Non Numeric" when you print.  If I eliminate the ToNumber from the formula, then I can't get the Subtotal to work. 

The @CAC represents the Cost At Completion field and is a custom field on the Cost code.  It is not specifically a numeric field which is why I was trying to convert it to one.

Posted By: DBlank
Date Posted: 03 May 2010 at 6:52am
YOu have to decide what to do if it the value is not numeric. Do you make that 0?
 
if isnumeric({JC_CONTROLESTIM.USER2}) then ToNumber ({JC_CONTROLESTIM.USER2}) else 0

Posted By: dee-jo
Date Posted: 03 May 2010 at 8:25am
DBlank,
Thanks so much for all your time!  You've solved my report issue!  I've learned so much!

Thanks again,
 
dee-jo


Print Page | Close Window