Line chart - chart formatted in hour intervals

[SKYplus]

Hi,

I'm working on a report with CR8 dev that should display a chart showing the total records in a table during a day in hourly intervals (i.e. in the y-axis there should be the count of records of the day and in the x-axis there should be the day in hour intervals from 0 to 23) for each user.

I have made formulas to return the total records for each hour i.e. a formula that counts records that were entered the first hour of the day (00:00:00 to 00:59:59) and so on for the other hours.

this is the code for the formula:

if (time({MyTable.Record_TIME}) >= time(00,00,00)) and
(time({MyTable.Record_TIME}) <= time(00,59,59)) then 1 else 0

this formula is than summerized to bring the total sum of events in the specified hour.

Now what i'm finding difficulty to do is to implement all this in a line chart...

How can i make it in one line series to show the daily number of records of a user? When i tried using the time field and group it on the hour the line showed ok except for the fact that the chart did not display a 0 for the hours in which no records where created.

Please let me know if you can help!

P.S. If you find that my explanation is not very clear please ask for more details...

 

[Andie]

I believe you may have actually answered your own question. If there is no data (0 records for the hour), then Crystal essentially skips over that interval. It will only recognize '0' if that is listed as an actual value in your database. From what I gathered in other threads, if there are no records in your database, Crystal will not put in 0 as a value. I'm not sure if there is a work-around. Perhaps someone else can answer that.

 

[SKYplus]

Hi, thanks for the reply!

Yeah i can understand why crystal reports does not show a 0 when there are no records, the whole purpose of the formulas i mentioned is to be able to display a 0, in fact when i display the formulas on the group header of the users group the correct amount of records are displayed and the formula will give a zero when there are no records.

What i'm finding difficulty in is how i can use these 24 formulas (i.e. one for each hour) to make a line on the chart so that the grouping is on the formulas.

I tried making an advanced chart and i've set it like this:

the place chart field -> is set to for each User
the onchange of field -> i didn't know how i should set this so i made it for all records
and in the show values list i placed all of the 24 formulas using a sum operation... i know this configuration is not good - BUT I'M DESPERATE !!

So, if anyone who ever tried to do this and succeeded, I would greatly appreciate if he/she/it would let me know.

Thanks!

 

[smcnulty2000]

You are dealing with one of my pet peeves. Crystal doesn't do anything with giving you the ability to assume non-existent data. Line graphs interpolate by drawing a line between points, but that's about it.

I'd like to see anyone come up with a great solution to this. I could use it daily.

In the meanwhile- The reason, if I can explain this rather fuzzy concept, why your formula isn't working is that when it would report a zero it has no record to report it on. It never reports on non-records.

Crystal doesn't have any way to know what you want done with the areas where records do not exist.

So if you have 2 records in the 10:00 hour, and 2 records in the 12:00 hour it won't make or be able to group anything in the 11:00 hour. It can't know that it exists.

The closest I've come to a solution is to:

1. build a main report based on a table with the desired intervals available.
2. Then build a subreport with my real data inside.
3. Then build one formula in the main, and an equal formula in the sub that give the interval type (i.e., if I need it to link on a date, I use date(xx)).
4. Then I link the two.
5. Then, I go into the selection formula in the subreport and hand-alter the way it interprets it.
6. Build a shared variable or variables to pull data out of the subreport.

So, here's an example of the tail end of my selection criteria for a subreport:

and
{@subLinkToMainReport} = {?Pm-@LinkToSubreportsField}
and
datepart(&quot;m&quot;,{@subLinkToMainReportDate}) <= datepart(&quot;m&quot;,{?Pm-@LinkToSubreportDate})
and
datepart(&quot;yyyy&quot;,{@subLinkToMainReportDate}) <= datepart(&quot;yyyy&quot;,{?Pm-@LinkToSubreportDate})

This has worked. I hate doing it every time, but I don't know a better way. This is always custom, rarely transferrable.

The entire method is predicated on getting the data from somewhere that you key and group off of. In other words, you need a table with the hours laid out, one record for each hour. I don't know of a way to 'invent' the data that the zeros will key off of.

Good Luck.