Time Format 1

[cestonina]

Hi,

I'm not sure if this is the right forum, anyway here goes:

I have created reports in crystal reports. I use a line graph to show the Time vs. Usage report.

Ideally, the database should look something like this,

TimeStamp (x-axis) Usage (y-axis)
09-01-03 00:00 10329887
09-01-03 01:00 90097777
09-01-03 02:00 83893444
09-01-03 03:00 12334445
09-01-03 04:00 23234994
..

The data is collected through an agent. Sometimes the agent does not collect certain time periods. Observe the TimeStamp field.

TimeStamp Bytes
09-01-03 00:00 10329887
09-01-03 02:00 90097777
09-01-03 03:00 83893444
09-01-03 05:00 10323222
09-01-03 08:00 13243445
09-01-03 09:00 83893232
..

Now when I graph these data for a specific time frame (lets say last 12 hours), crystal reports would just skip the missed time periods and connect to the next entry. Which at first glance at the chart, would show a smooth trend (quite deceiving).

From exmaple above, I would also want my chart to automatically show a time period for 1AM data (or other missing entries like 4AM, 6AM, etc.) even though there was no entry in database, basically put a zero value in Usage field (y-axis).

By doing this, I can see which time periods that have no data/entry in database and manually force the agent to upload the data.

How can I format the chart to show a consistent time period even though there were no entries for certain time periods? Or can anyone show a sample formula for a consistent time period? Is there a command in Crystal that would automatically do this?

Feel free to email me for more details. I can send a sample report for a more clear picture. Thanks in advance.

cestonina@yahoo.com

Regards,
Topy

 

[synapsevampire]

Keep in mind that this isn't Crystal skipping time periods, your data skips time periods.

I would resolve this type of thing permanently by creating a periods table which has a a datetime for every hour, and then doing a left outer from that table to your table(s).

You'll then find that this table resolves lots of date and time concerns, not just in this example. The theory is a standard Data Warehousing dimension, and should be a part of every reporting system, data warehouse or not.

-k

 

[cestonina]

Thanks for the tip.

I was just hoping that there is already a built-in function (running fields, crosstab, etc.)in crystal that would automatically do this for me

Well anyways, I'll just create a new table like what you suggested. Thanks again for this tip.

Regards,
Topy

 

[gandapanda]

i am in a very similar situation, my situation calls for a odd reporting intervals. For instance, 9:30:06 instead of 9:30:00; to compensate for this, the XY scattergraph works great, but i need trendlines or some way to distinguish the time;
so that is my question, is there any way to connect the xy scattergraph (dots) w/ lines or trendlines?

sonny
singh4160@hotmail.com

 

[cestonina]

Hi Sonny,

I'm not quite sure what you're asking. If you want to have trendlines in your report, there is an option in Crystal reports to set it up. See steps below.

1. Right click the graph (line chart for example),
2. Click Format Chart->Grid. Go to Group Axis tab.
3. In Labels tab, you can specify the number of interval for your time in "Manual Number of Groups".
4. Go to Grid tab to specify minor trendlines. You can also manually set the number of trendlines in this tab. Just play around with it.

Note: If your time format is on seconds (like 9:30:06, 9:30:08, etc.), you should change the order of your time variable/field to "per second".

1. Right click graph then go to Chart Expert.
2. Click the Data tab then highlight your time variable/field. Click Order button.
3. Choose "for each second" in Chart values shown.

Hope this helps.

Regards,
Topy

 

[gandapanda]

hey topy,
thanks for responding...
xy-scatter (CR9 w/ win2000) does have a option within chart-expert-> template called "connect data points with lines"; thats what i am looking for, but when selecting this option, the xy-scattergraph does not "connect the dots", so to speak;

basically, i am in the same situation as yourself where i need the graph to address time scale variations; the line-graph will not do this for me, ive tried everything, the data is skipping time periods like synapse said;

xy-scatter works great, but the dots need to be connected to make things more meaningful... by trendlines i mean lines connecting the data points; sorry if i am using the word in the wrong context =)
sonny

 

[cestonina]

Well I have resolved my problem by creating another "time period" table like what synapsevampire suggested. I have created two tables, "per hour" and "per minute" tables.

I have joined my original tables with this new ones. I am now using the time fields in these new tables in my reports. This gives a more accurate data since it shows a complete time period, with/without data.

I have not yet really used the xy-scatter. I still suggest using line graph then do what synapsevampire suggested.

Regards,
Topy

 

[gandapanda]

yes,
he seems to know his stuff better than anybody; i'll try that
thanks
sonny