INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Report Development

Date Tables and Mock-Crosstabs by Madawc
Posted: 21 Nov 11

This topic keeps coming up: someone wants an entire ranged of dates to show even when there is no data for it.  Or occasionally some other variable, but the solution is similar.

Stage One
If groups A, B and C cover all of the possibilities between them, then you can do a crosstab.  These are quick and easy ways to group data in ways that can be different from the main group.  
It's worth learning about crosstabs as a general quick solution, even if they do not help with your immediate problem.
Note that crosstabs can be edited to look quite different from the tabular entity that you get as a default

Stage Two
Are you able to get tables / datasets added to the database?  If you got a table of DATE set up, this could be linked to the activity using LEFT OUTER and you'd still get a slot when there was no activity.  (But note that any selection on the second table must do an IsNull test first, otherwise you'll not get the dates with no records, null value for the second record.)

Stage Three
If you can't add a table, you'll be stuck with creating a 'Mock Crosstab'.  Crosstabs are fine if you don't mind the absence of rows or columns with no data, but you said you wanted empty slots to show.

A 'Mock Crosstab' is something that looks like a Crosstab, but in fact you define each column yourself, normally as a running total.  This would need to go in the report footer, because running totals count as the reports 'run' and they will not be complete until then.  Crystal should have included an example along with the Crosstabs.  

You can save a little time by doing a paste to a dummy report, changing the name and then pasting back.  In Crystal 11.5, you can also duplicate formula fields using the Field Explorer.

 

Back to Business Objects: Crystal Reports 4 Other topics FAQ Index
Back to Business Objects: Crystal Reports 4 Other topics Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close