Help with Grouping

[tjw2006]

Hi, I've got a report that includes the fields 'Updated','Added' and 'Deleted'. These fields are populated with either a 1 or a 0 depending on they're true or not. I'm trying to group the data together in Crystal Reports so that all the rows that have the 'Updated' field set to 1 together and likewise for the 'Added' and 'Deleted', so that effectively the data is showing sections of data for each state. However, when I'm doing a group on these fields, the report is showing the groups separated - for example I'm getting the 'Updated' group showing up in various places in the report, so all the 'Updated' rows are not together. Can anyone give me a clue on what I'm doing wrong?
Thanks

 

[Madawc]

I think you need to create a formula field that groups as you wish. First create it as a data item, something like

if {Added} = 1 then "Added"...

Then use it to group.

You've not allowed for cases when more than one value is set. Is that possible?

It helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options. In this case, it probably makes no difference.


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

 

[tjw2006]

Hi, thanks for the quick response. I'm using XI R2. I'm actually basing the group on a formula now, but I think it's down to the ordering where I'm having trouble.

 

[Madawc]

DO something like "a) Added" to get the order. There are also options for ordering, you find them by right-clicking and choosing 'Change Group Options'

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

 

[lbass]

The problem is that these are separate fields, instead of one field. You could either handle this by using the main report to deal with one field, and adding the other two fields in separate subreports in separate report footers, or you could use a command as your datasource, where you union the three fields like this:

select 'Added' as currstatus, `table`.`added` as value, `table`.`otherfield`
from `table`
where `table`.`added` = 1
union all
select 'Deleted' as currstatus, `table`.`deleted` as value
from `table`, `table`.`otherfield`
where `table`.`deleted` = 1
union all
select 'Updated' as currstatus, `table`.`updated` as status
from `table`, `table`.`otherfield`
where `table`.`updated` = 1

Then you could group on {command.currstatus} and you would have the desired layout. The syntax of the above depends upon your database.

-LB

 

[tjw2006]

Yes, having the statuses in different fields is causing a problem, maybe I'll change the backend table to have just one field and base the group on that field. I'll give this a go - thanks for your help.

 

[tjw2006]

Yep, that worked a treat! Based the group on a formula which held either 'U','A' or 'D' for updated, added or deleted and depending on the value used an if statement to set the group name to "Updated","Added" or "Deleted" and then chose a specified order for the grouping - job done!

Thanks again.