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
Carlo VasquezAsked:
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.

mlmccCommented:
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
Carlo VasquezAuthor Commented:
I am grouping by state.
0
mlmccCommented:
How are you getting the total by month?

mlmcc
0
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Carlo VasquezAuthor 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
mlmccCommented:
Are there records for the month?
0
Carlo VasquezAuthor Commented:
No sale records...so it shows nothing. I want to show zero instead of nothing.
0
mlmccCommented:
Try this

Add a formula

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
Carlo VasquezAuthor 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
mlmccCommented:
WHere you are expecting to see a 0 are there any records that meet the criteria for the state group?

mlmcc
0
James0628Commented:
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
Carlo VasquezAuthor 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
mlmccCommented:
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
Carlo VasquezAuthor 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
Carlo VasquezAuthor Commented:
See attached.
RPT.png
0
mlmccCommented:
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
Carlo VasquezAuthor 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
James0628Commented:
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
mlmccCommented:
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

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Carlo VasquezAuthor Commented:
Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.