Show zero record in Cross-tab

Posted on 2014-08-22
Last Modified: 2014-08-22
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.

Question by:spudmcc
    LVL 18

    Accepted Solution

    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:

    or you can just hardcode it in your database
    LVL 100

    Expert Comment

    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.


    Author Closing Comment

    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!


    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
    I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now