Using Graphs in Crystal Reports - show all dates even those without data

I'm trying to display some data in a graph format for a report.  The vertical axis has a $ value the horizontal access lists dates.  The report is showing the value of shipments by date for the current month and is meant to be sent daily to sales reps.  The goal is to show them how much they are selling each day.  Based on that sales data a trend line would show them if they are on target for the month.  Right now this is working great when the report runs near the end of the month, but on the first days of the month it can only show the data for the day it runs and any previous days.  So, I have one day reported with one bar, then two bars, then three bars ect.

I would prefer, if all business days were shown on the horizontal axis regardless if those days have data or not.  They can show as 0 until something ships that day.  This way the trend line would be more realistic of what is possible for the month.  With only two days in on the whole graph the trend line is very flat.  If more days were listed (even as 0) the trend line should more sharply rise.
-Darvin-Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
One option is to add a Dates table to your database with one record for each date.  
Then, do an outer join to your transactions data and group by the Dates.Date column.

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
mlmccCommented:
To get the data into the chart you need data for each day.

The method above is one that will work.

Unfortunately without data you can't have anything to display.  

If this were not in a chart but in a text report with a group on the person then list each days sales it would be possible without using the date table.

If you can't change the database it might be possible to use an Excel sheet or a temp table generated by a Command.

mlmcc
-Darvin-Author Commented:
I've never added a excel spreadsheet as a data source before.  I'll give that a shot but not sure what I am doing there or how to tie it into what I am trying to accomplish.
mlmccCommented:
You add it in just like any other data source.

After adding it you link it to your existing data source using a left outer join

Excel.Dates  ---->  (LOJ)   Existing Data Source

mlmcc
-Darvin-Author Commented:
I haven't had any luck with this.  Crystal just won't let me add in that data source.  Even if it did I'm not sure how to list every date in excel for the next several years to accommodate this as it changes.  The business unit with this request as accepted the report as it is and are no longer concerned with this.  The report looks a bit odd for the first couple of days on the graph but after a few days of data starts tog get recorded it looks a lot better.

So, this has become a non-issue.
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.