Solved

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

Posted on 2006-03-29
479 Views
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.

0
Question by:ebrandel

LVL 100

Accepted Solution

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

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

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

LVL 1

Author Comment

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.

0

LVL 1

Author Comment

oops...

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

LVL 100

Expert Comment

Good solution

mlmcc
0

## Featured Post

### Suggested Solutions

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…