Show zero record in Cross-tab

Hi Experts

I have a Crystal cross-tab report that has the date in the column and type in the row and the "count of the incident" in the summarized field.  My issue is that some days there are no incidents but the cross-tab is just not showing records for that date.  I need it to show me that date even if it has a zero count.  I've unchecked everything that I can find that says suppress but still no luck.

Any help would be greatly appreciated.

Andy
spudmccAsked:
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.

vastoCommented:
You need to add a record showing the dates without incidents
Your current structure probably looks like
Date           Incidents
1/1/2000         5
1/3/2000         1
1/4/2000         2

it should be
Date           Incidents
1/1/2000         5
1/2/2000         0
1/3/2000         1
1/4/2000         2

The easiest way to add records with 0s is by creating a separate table with all the dates in the period and Left Join this table with your data
You can create the dates table on the fly using a technic similar to the one described here:
http://www.r-tag.com/Pages/Sample_OvertimeCalculation.aspx

or you can just hardcode it in your database
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
mlmccCommented:
Agree.  The cross tab can't display the data that isn't there.

The only issue you will have is you can't do a selection formula that goes against your current data.  If you have that then you need to build a view or stored procedure in the database or use a command for the data source.

The SQL you build has to do the filtering in the JOIN not in the WHERE clause.

mlmcc
0
spudmccAuthor Commented:
Thanks for your answer.  It was one of those things that is so obvious it just hits you in the face.  Of course if there are no records how can it display.  Duhhh!  I appreciate the link and I am working on using this solution.  

Much thanks!

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.