Charting Zero Values 1

[ProgressiveJL]

I need my table and chart to display a zero value for hours that don't have any records.

I've tried 2 methods of grouping my data using IF-Then-Else and Case controls.

------
select {@hour} \\where time is stored as HH:MM:SS
case 00 : "12 AM - 1 AM"
case 01 : "1 AM - 2 AM"...
------
if {TranClosed01.TTIME} > "00000000" and {TranClosed01.TTIME} < "01000000" then "12 AM - 1 AM"
else
if {TranClosed01.TTIME} > "01000000" and {TranClosed01.TTIME} < "02000000" then "1 AM - 2 AM"
else
------



Looking forward to any suggestions!
Thanks.

 

[IanWaterman]

If you have no data ie no record then you can not chart it.

You can create a dummy table with hours loaded and then use that to drive your report.

Use the dummy hour table and then join your other tables as a left outer from it. YOu will then get a record for each hour whther you have data or not. YOu may have to create a view of your data to include a compatible hour field to provide your link from the dummy hour table.

This assumes you have rights to use SQL on your database and create the necessary tables and views.

Ian

 

[ProgressiveJL]

The DBs I'm working with are xBase and I don't believe there's an option for me to "add command". Is there somewhere else I can add SQL commands?

 

[IanWaterman]

Not that I know of. Creating a table can not be done in a Crystal command. YOu need access to the database.

Ian

 

[lbass]

Another approach would be to create a separate formula for each hour, like:

//{@12AM-1AM}:
if {TranClosed01.TTIME} > "00000000" and {TranClosed01.TTIME} < "01000000" then {table.amt}//or "then 1" if you are doing a count

You would then add each formula as a summary field (using sum as the summary (not count)) in the show value area of the chart expert.

-LB

 

[ProgressiveJL]

Is there a way to code my report in such a way so that for each hour...

if records for the hour = 0\null\empty... then returns a 0 to the table\chart..

Thanks

 

[lbass]

This approach will result in zeros when no records meet the criteria.

-LB

 

[ProgressiveJL]

Great so the algorithm may work, but i'm not sure how to put it into code..

Here's what I have so far:

@hour is -
numberVar hour := ToNumber (Left (ToText ({Trans.TTIME}), 2));

I group on @hour and it only displays values for hours containing records otherwise the hour is omitted.

How and where do I go abouts adding the if IsNull conditions?
Do I do it in "Use a Formula as Group Name"? In @hour?

Thanks!

 

[ProgressiveJL]

Thanks for the help LBass!!!! I got your solution to work!!!

Created a single formula for each hour along with running totals... No grouping required!

Cheers!

 

[ProgressiveJL]

Ok.. so now that I have my table of value... how do i chart my x-axis values?

I've selected all the summary formulas for the "Show Values" but how do I setup the "on change of..."

 

[lbass]

On change of what? You are adding each summary and these will appear as a separate bar per hour. Change the "on change of" to "for all records".

-LB

 

[ProgressiveJL]

Alrighty, so I was charting the summary values rather than formulas... but now I don't have any x-axis labels...

so close...

 

[lbass]

I think you'll have to rely on the legend or add the x values manually in text boxes.

-LB