Avatar of Chopp
Flag for United States of America asked on

Crystal Reports Bar Graph Questions

Hello Experts,
I have 2 questions regarding a bar graph using Crystal Reports 11. The graph shows how many calls came in to a Dispatch Center grouped by 10 different priority values.  I am attempting to show all ten priority values in the legend for the bar graph in a specified order. Also, I want to show all 10 values, even if there is no record for that value.  Question 1 - Is there a way to show zero records for a Group value when there are no records in that group? Question 2 - How do I display the legend values in a specified order?
Thank you in advance for your help, Chopp
Crystal ReportsDatabases

Avatar of undefined
Last Comment

8/22/2022 - Mon
Marcus Aurelius

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.

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

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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
Marcus Aurelius

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.
Marcus Aurelius

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...

Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.

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.
Declare @DateToInsert Datetime
Set @DateToInsert = '1/1/06'
Create Table DateTable
                  Dates DateTime NULL
            ,     JustADateString VarChar(30) NULL
While @DateToInsert < '1/1/2020'
      Insert Into DateTable (Dates, JustADateString)
      Select @DateToInsert, Convert(VarChar(30), @DateToInsert, 101)
      Select @DateToInsert = DATEADD(dd,1,@DateToInsert)

Open in new window

Marcus Aurelius


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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes