Show all months in a crosstab, even without data?

Posted on 2009-04-29
Last Modified: 2012-05-06
So I have a crosstab Crystal Reports 10 that shows sales by months, but I can have a customer who say only has an order in January and June, so the crosstab only shows January and June.  Is there a way to get it to show Jan, Feb, ....Nov, Dec, have the correct valus for January and June, and have $0 for the other months?
Question by:mtuepker
    1 Comment
    LVL 77

    Accepted Solution

    CR won't report on non-existent data.

    You can do this by creatin a 'months' table containing the month numbers and doing a left join from this table to the current datasource - you will have to use the Add Command option and build the full sql statement in order to join on the month numbers.

    Select monthnum, originaltable.*
    from months left join originaltable
    on months.monthnum = month({originaltable.datefield})

    Featured Post

    Highfive Gives IT Their Time Back

    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

    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…
    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 …
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    729 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

    20 Experts available now in Live!

    Get 1:1 Help Now