• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 491
  • Last Modified:

Custom Crystal Reports 30 minute groups - offset by -10 minutes

I have a report that displays some average, minimum, and maximum data for a number of different sensors. The report is supposed to display a days worth of data, grouped by 30 minutes. I'm having two issues with it:

First, the data needs to be grouped in an abnormal fashion. Specifically, the groups are for 30 minutes, but offset by 10 minutes. So, if the day to be displayed was 03/28/2006, the groups would be:

03/27/2006 23:50 - 03/28/2006 00:19
03/28/2006 00:20 - 03/28/2006 00:49
03/28/2006 00:50 - 03/28/2006 01:19
...
03/28/2006 22:20 - 03/28/2006 22:49
03/28/2006 22:50 - 03/28/2006 23:19
03/28/2006 23:20 - 03/28/2006 23:49


Second, the layout. The layout that the customers would like is as follows:

Group 1 | Group 2
Group 3 | Group 4

So, four groups per page with a total of 12 pages per report. This is much less important than the grouping and if it's difficult or can't be done then the layout would just have to be:

Group 1
Group 2
Group 3
Group 4

for each page.

Thanks in advance.
0
ebrandel
Asked:
ebrandel
  • 2
  • 2
1 Solution
 
mlmccCommented:
There is no easy way to do the grouping as shown.  Crystal does support Multiple column reports but you either go down with the recods then across or accros then down but it is by record niot by groups.  I assume each group will have multiple records.

Will the groups always fill the page?  If so you might be able to get

Group 1 | Group 3
Group 2 | Group 4

Group - How is the date passed in?
I am not very familiar with grouping as you desire.  Is there a Crystal group that allows you to automatically group on 30 minute intervals?  If so create a formula like

DateAdd("n",10,{DateTimeField})

If that doesn't work
Create a formula that returns a value that can be grouped on.
Try something like this

DateTimeVar  StartRange := DateTime(DateAdd('d',-1,{?YourDateParm}),Time(23,50,00));

If {DateField} in StartRange to DateTime({?YourDateParm},Time(00,19,00))  then
   1
else If {DateField} in DateTime({?YourDateParm},Time(00,19,00) to DateTime({?YourDateParm},Time(00,49,00))  then
   2
else If {DateField} in DateTime({?YourDateParm},Time(00,49,00) to DateTime({?YourDateParm},Time(01,19,00))  then
  3
else If {DateField} in DateTime({?YourDateParm},Time(01,19,00) to DateTime({?YourDateParm},Time(01,49,00))  then
   4
etc

You can then group on the formula

mlmcc



0
 
ebrandelAuthor Commented:
Well, I've taken a slightly different approach now: I'm using subreports.

Basically the subreport will have one group in it, and then I'll use formulas to separate the data out into two sections. On the main report I'll simply have it group a timestamp by hour and then have the subreport in the detail section (so it will print out twenty four of the subreports).

So the page will be like so:

Subreport 1: "Group" 1 | Subreport 1: "Group" 2
Subreport 2: "Group" 3 | Subreport 1: "Group" 4

I put "s around Group because they're not real groups. I'm using a setup similar to the one shown at http://support.businessobjects.com/library/kbase/articles/c2006365.asp to gather the data and using a formula like your grouping one to separate the date out.

My code to grab data from the db is something like this:

if minute({TABLE.SENSOR_TIME}) >= 50 OR minute({TABLE.SENSOR_TIME}) <= 19 THEN
    rhtotal_left = rhtotal_left + {TABLE.RH_INS}

    if {TABLE.RH_INS} > rhmax_left then
        rhmax_left = {TABLE.RH_INS}
    end if

    if {TABLE.RH_INS} < rhmin_left then
        rhmin_left = {TABLE.RH_INS}
    end if
else
    rhtotal_right = rhtotal_right + {TABLE.RH_INS}

    if {TABLE.RH_INS} > rhmax_right then
        rhmax_right = {TABLE.RH_INS}
    end if

    if {TABLE.RH_INS} < rhmin_right then
        rhmin_right = {TABLE.RH_INS}
    end if
end if


Not that it would have helped me, but I'm really suprised their isn't a 30 minute grouping available in Crystal Reports.

Anyways, thanks for your help. Your formula really helped me.
0
 
ebrandelAuthor Commented:
oops...

"Subreport 2: "Group" 3 | Subreport 1: "Group" 4" should read
"Subreport 2: "Group" 3 | Subreport 2: "Group" 4"
0
 
mlmccCommented:
Good solution

Glad i could help

mlmcc
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now