Crystal Reports Bar Graph Questions

Creating a date table is a very creative idea.  I will try that.
Thank you for your help.
~Chopp

Sure, It does help...of course you'll need to configure and link according to your tables / needs....but I've done it before and it does work.....and in reality is the only way to report on "missing" or empty data.

Can you provide me with your SQL script to create that table which will contain a record for every day of the year? Thank you, Chopp

Well actually...something even easier...is to create an EXCEL sheet that has the Column Name of DATE in CELL A1.

Then in CELL A2 put the first date of the year you want to start with like this:  01/01/2000

Then use a formula in CELL A3 below it...like this:    =A2+1

Then just copy the formula in CELL A3 down however far you want to captuer as many dates as you want.

THEN... IMPORT the file into SQL.

Make sense?

I will try that.  In order to link the final table to my existing tables, the date will have to be a datetime data type.  Hopefully, this will work.  Thank you very much for your help.
~Chopp

Well when you import it ....you will assign it a data type....and ...again,...you may to play around with the linking of the table.....but I think you get the concept...

In case you want to see it, here is the script I ended up using to create the date place holder table.
Thank you for all your help.
~Chopp

Use UDT
Declare @DateToInsert Datetime
 
Set @DateToInsert = '1/1/06'
 
Create Table DateTable
      (
                  Dates DateTime NULL
            ,     JustADateString VarChar(30) NULL
      )
      
While @DateToInsert < '1/1/2020'
Begin
      Insert Into DateTable (Dates, JustADateString)
      Select @DateToInsert, Convert(VarChar(30), @DateToInsert, 101)
 
      Select @DateToInsert = DATEADD(dd,1,@DateToInsert)
 End

Select allOpen in new window

Nice..thanks

Well there's a lot better way to solve this than create a table or hardcode anything. You just have to create a subreport where you declare an array variable where you put all your data for you graph like this  (put the formula in your detail section):
shared numbervar array LastYearResult;
redim preserve LastYearResult[12];
// if i assume that each line of your detail section is a call, you can do this
LastYearResult[month({RecordDate})]  := LastYearResult[month({RecordDate})]  + 1
// if i assume that each line of your detail section is a sum of the calls
LastYearResult[month({RecordDate})]  := LastYearResult[month({RecordDate})]  + {NbOfCalls}

you can pass your date range to your subreport and do something to calculate the date range for last year.

Once this is done, you just have to do the same thing for this year result in your main report. And then you do formulas for you graph with a case like this (one forumal for this year, and one formula for last year)

declare your variables
select month({RecordDate})
case 1 : LastYearResult[1]
case 2 : LastYearResult[2]
case 3 : LastYearResult[3]
etc to [12]

This should work, this was a very short version of what you can do with subreports, but if someone need more explication, then look for topics about them.