[Last Call] Learn how to a build a cloud-first strategyRegister Now


Show zero record in Cross-tab

Posted on 2014-08-22
Medium Priority
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

vasto earned 2000 total points
ID: 40279248
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 101

Expert Comment

ID: 40279382
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

ID: 40279965
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

825 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