Bar Graph - add column where no data exists

[shelby55]

Hi

I'm using Crystal 9.0 reporting off an Access 2003 database.

I am creating a bar graph for discharge hour from a patient inpatient visit table. How can I ensure there is a bar for each hour of the day (or a space for an hour that has no data)?

Thanks.

 

[lbass]

You would need to use a table that contains a datetime field with every hour represented. Then link the table containing your visit datetime to this datetime table using a left join. Then use the datetime table for the chart axis.

-LB

 

[shelby55]

Hi LB

Thanks for the quick reply.

Currently the data table doesn't have "hour" in it, just date/time but I've created the field of hour for the graph. So you're saying I'll need to create that field in the data to link to the new table because I can't just link table to created field, correct?

 

[lbass]

I was suggesting that you need to create a table that contains all hours to which you then link your current table on the datetime field, but that implies a table that contains all possible times in the day which would be onerous.

Instead you could create one running total per hour by summing or counting some field, evaluate using a formula:

hour({table.datetime}) = 18 //for 6pm

Reset never.

Repeat for each possible hour. Then create a formula {@all} like this:

whilereadingrecords;
""

Add this as your "on change of" field in the advanced chart expert and then add each running total (in order) as a summary field.

-LB

 

[shelby55]

HI LB

Sorry but I'm still not sure how this will work for when there isn't any cases with a certain hour.

Right now it goes like: 00 08 09 11 12 13 15

but I want it to be 00 01 02 03 etc. regardless that the bar for 01 to 07 will be zero.

 

[lbass]

If you are creating one running total per hour, there will ALWAYS be a result when you add the running total as a summary field.

-LB

 

[shelby55]

Hi LB

Sorry for being stupid but I want to clarify how to do this. I create one running total per hour so there will be 24 running totals.

Then in the @all created field it will be whileprintingrecords but what will the code be just selecting all of the running totals? Connected by ?

Thanks.

 

[lbass]

No, {@all} is:

while[red]reading[/red]records;
[red]""[/red]

The only reason to use this is to allow you to activate the OK button on the chart--basically it's a work around.

-LB

 

[shelby55]

HI LB

Okay I did as you instructed and then in the "show values" portion of the Chart Expert I put all 24 of the time groups.

The only problem is that I don't have data labels on that axis - how can I add some?

Will this be different if my graph is located in a header/footer of a group?

Thanks.

 

[lbass]

You cannot use running totals in a header section--the results would be inaccurate. If you want the chart in a group footer, then the running totals should be reset on change of that group. For labels, you could choose to add data labels (chart options->data labels->show data labels->choose location), but the labels would have the "#" before the hour. Instead you could just add text labels below each bar. Since there will always be a bar and it will be in the same order all the time, this should work fine.

-LB

 

[shelby55]

Hi LB

Sorry but I just can't get this to work. I've been trying to get to work in a report footer first before trying to replicate in a group footer and it's just not working.

I am comparing it to the original which was summing on change of discharge hour with missing hours due to the data. When doing it as you suggested, the bars are all off and there aren't 24 of them as there should be.

What am I missing? Thanks.

 

[lbass]

Please explain how you are setting up one of the running totals.

-LB