Chopp

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

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

ASKER CERTIFIED SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**
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.

ASKER

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?

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?

ASKER

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

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

ASKER

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

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

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.

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({Reco

// if i assume that each line of your detail section is a sum of the calls

LastYearResult[month({Reco

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.

ASKER

Thank you for your help.

~Chopp