# Set Running total from Blank to Zero

I have a running total that shows blank if there are no no values for that month and I want to show the value of zero. So this is the scenario:
Summarize Sales for that month if no sales then set value zero.

Field To Summarize: Order_Tot then for type of Summary I chose Sum
Evaluate: Use Formula; from begin of month to end of month
Reset: On change of Group
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
WHat are you grouping on?
If it is the date (month) then are there records for the month?

Try this

If (IsNull({YourNumberField}) then
0
Else
{YourNumberField}

Base the running total on that formula rather than YourNumberField

mlmcc
0
Author Commented:
I am grouping by state.
0
Commented:
How are you getting the total by month?

mlmcc
0
Author Commented:
Field To Summarize: Order_Tot then for type of Summary I chose Sum
Evaluate: Use Formula; from begin of month to end of month
Reset: On change of Group

I am getting the totals by month by the second option above....EVALUATE:  I chose there to use formula. I am selecting From and Two month there as a formula inside the summarize options.

This is the formula:
{SYSOENT.ORDER_DATE} >= CDateTime (2012, 10, 1, 00, 00, 00) and
{SYSOENT.ORDER_DATE} <= CDateTime (2012, 10, 31, 00, 00, 00)
0
Commented:
Are there records for the month?
0
Author Commented:
No sale records...so it shows nothing. I want to show zero instead of nothing.
0
Commented:
Try this

If {SYSOENT.ORDER_DATE} >= CDateTime (2012, 10, 1, 00, 00, 00) and
{SYSOENT.ORDER_DATE} <= CDateTime (2012, 10, 31, 00, 00, 00)    then
{YourAmountFIeld}
Else
0

Use a summary function on the formula

Why do expect to see a 0 if there are no records?
There was nothing to add so there is nothing to show

mlmcc
0
Author Commented:
If there are no records than show a value of zero for that state for that month.

As per your formula this is how I have it.
I get no errors but still showing blank for that date range.

If {SYSOENT.ORDER_DATE} >= CDateTime (2012, 10, 1, 00, 00, 00) and
{SYSOENT.ORDER_DATE} <= CDateTime (2012, 10, 31, 00, 00, 00)    then
{SYSOENT.ORDER_TOTAL}
Else
0
0
Commented:
WHere you are expecting to see a 0 are there any records that meet the criteria for the state group?

mlmcc
0
Commented:
Your use of the word "blank" implies that there is a place for the month total, but the field is blank, instead of 0.  Is that what's happening, or does the month actually not show up at all (IOW, the total field isn't really blank.  There is no total field for that month) ?

Assuming that there actually is a total field for the month and the field is blank, where is the field?  If it's a group footer, which group?  Or is it in a detail section or somewhere else?

James
0
Author Commented:
There are no records for that month so therefore it does not show anything. I want to put a value of Zero. There is no total field for that month.

The field is in the Group Footer.
0
Commented:
Can you show what the report looks like or upload the rpt file?

There are tricks that can be used but the method depends on the structure of the report.

Basic trick is to keep track of the month range and before printing a month see if you are missing any months.  WHere and how this is done depends on the report structure and where you are displaying the values.

mlmcc
0
Author Commented:
That is how the output looks like.
Not sure if I should upload the RPT here in public but if u give me your email I could send it to you.

Thanks.
0
Author Commented:
See attached.
RPT.png
0
Commented:
Are you concerned about the report design or the data?

You can save it without data if it is the data that concerns you.

mlmcc
0
Author Commented:
True that. The report is working fine and its doing its job.
I just want it to show zero but if this is a problem then I will leave it the way it is now.
0
Commented:
I'm not sure if you understood this, but after you expressed concerns about posting the report file in public, mlmcc was asking if it was the data in the report that you were concerned about, or just the report design itself (maybe it includes some proprietary information).  If you're just worried about the data, you could post the report file without any data.

James
0
Commented:
What are you showing in the columns?

Is it a field or a summary of a field?

Can you try using a formula like
If IsNull({Field You Are Showing}) then
0
Else
{Field You Are Showing}

mlmcc
0

Experts Exchange Solution brought to you by