Grouping based on formula

[pblazeppd]

Hi, Need help on grouping. Running CR9 with SQL. I have a formula (@Shift) that determines if an event occurred on a particular shift based on a datediff of a from datetime to datetime. Basically (@Shift) for each record the formula will produce one of the following possiblities:

Record# @Shift
1 1/2/3
2 1/2
3 1
4 2
5 2/3
6 3
7 1/3

If possible I would like to duplicate records amongst the different groups. I have tried grouping with a specified order, where Possible Shift 1 is like *1*, Possible Shift 2 is like *2*, and Possible Shift 3 is like *3*, etc.

What I would like is to get is something like...
Possible Shift 1:
Record # 1
Record # 2
Record # 3
Record # 7

Possible Shift 2:
Record # 1
Record # 2
Record # 4
Record # 5

Possible Shift 3:
Record # 1
Record # 5
Record # 6
Record # 7

Thanks for any and all help.

Phil

 

[Madawc]

Crystal won't let you show one record in more than one group.

Take a look at Crosstabs, Insert > Crosstab.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[synapsevampire]

Consider a Union query as the data source, since you're using CR 9 and I assume SQL means SQL Server (SQL is a programming language).

So you'dessentially do the same thing with the datetimelogic in the where clauses:

select 1 as source, field2 from table
where
<your formula converted to SQL for shift 1>
UNION
select 2 as source, field2 from table
where
<your formula converted to SQL for shift 2>
UNION
select 3 as source, field2 from table
where
<your formula converted to SQL for shift 3>

Now you've duped the data and in the appropriate location.

You could lso just create a Union in a View.

One option would be a main report which pulls shift 1, a subereport in the report footer 1 of shift 2, and shift 3 subreport in report footer 2, each returning only that shifts data.

Another alternative that comes to mind would be arrays in Crystal, and I think the limit is 1000 in CR 9, so it may not suffice.

-k