Crystal Reports Breaking up data in the same row

Report Design
Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design

Topic: Breaking up data in the same row

Author

Message

Troy S
Newbie
Newbie

Joined: 22 May 2007
Location: United States
Online Status: Offline
Posts: 4

Topic: Breaking up data in the same row
Posted: 22 May 2007 at 2:41pm

I am trying to create a report using multiple time intervals of a day. Example - 12:00:00 & 12:55:00, 1:00:00 & 1:55:00.... All the data I need to work with is in the same row, and I need to be able to a diff on the data based on the time stamp. I would start with the data at 12:55:00 time and subtract the data 12:00:00 to get the hour total and then I need to use that total for a formula.

Is this at all possible, or do I need to have the individual time intervals of data split into different columns for what I need to do with them?

I am trying to save time from using Excel.

Please help,

Thanks,

Troy

IP Logged

BrianBischof
Admin Group

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458

Posted: 22 May 2007 at 2:46pm

You can create a formula that just returns the hour of the record (using military time in 24 units preferably) and then do a cross-tab on that formula. Or you can group on that formula if you don't like cross-tabs.

Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>

IP Logged

Troy S
Newbie
Newbie

Joined: 22 May 2007
Location: United States
Online Status: Offline
Posts: 4

Posted: 23 May 2007 at 6:08am

Do you have an example of how that is done?

IP Logged

Troy S
Newbie
Newbie

Joined: 22 May 2007
Location: United States
Online Status: Offline
Posts: 4

Posted: 30 May 2007 at 11:02am

Have you had a chance to get an example of how to set up the formula properly?

Thanks.

IP Logged

BrianBischof
Admin Group

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458

Posted: 30 May 2007 at 2:20pm

I'm confused by one statement. You say that all the data you want to work with is in the same row. So that means that you have a separate column for each time period? Or is the time period stored in one of the fields as well. I'm having trouble visualizing the table structure and how the data/times are stored in it.

Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>

IP Logged

Troy S
Newbie
Newbie

Joined: 22 May 2007
Location: United States
Online Status: Offline
Posts: 4

Posted: 31 May 2007 at 9:59am

All the data for all time periods is located in the same column, not row.

The data I need to work with is in the ID, Date & Time and Var2 columns.

The data reports in 14 different ID#'s every 5 minutes.

I need to be able to sudtract the value on Var2 & ID#1 & time 0:00:55 by the value on Var2 ID#1 & time 0:00:00 to get the 12:00am total. Then I need to multiply each of the Var2 values by a number to get a total. Once I have all the ID#'s totals for each time range I need to then add them all together to get a complete total of all ID's for that time(At 12:00am - add ID #1.1, 1.2, 1.3,1.4....etc)

I have to do that same calculation for every hour or everyday for every ID#. That is just 1 report, I have over 100 that have to be done daily.

Example -

ID # Date & Time Var1 Var2 Var3 Var4

1.1	4/19/2007 0:00	2122250	16978	80414	1559457
1.2	4/19/2007 0:00	3566875	28535	75349	2788825
1.3	4/19/2007 0:00	3937000	31496	60839	3024292
1.4	4/19/2007 0:00	3016375	24131	31982	1917679
1.5	4/19/2007 0:00	6871125	30799	65872	2055366
1.6	4/19/2007 0:00	7034125	56273	56273	1218811

I hope this clears it up a bit.

Thanks,

Troy S.

IP Logged

BrianBischof
Admin Group

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458

Posted: 31 May 2007 at 1:07pm

OK, I think I have a better idea of what is going on now. First sort by ID# and then by time. Use the Prev function in CR to get the previous record's value of Var2. Then you can subtract it from the current Var2 and multiplty them by whatever you want. Since you need to total everything by ID # the I would group on the ID # and do subtotals and grand totals with it.

Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>

IP Logged

Printable version

Forum Jump

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

This page was generated in 0.016 seconds.