Grouping based on multiple fields

[crystaldev1]

Hello, I'm using CR 11 and sql server 2008. I am trying to group using multiple fields. I have the fields as follows:

table.field1: Bob
table.field2: Steve
table.field3: Mark
table.field4: Bob

As you can see, there may be fields with same values.

 

[kray4660]

So what is the issue?

Thanks.

 

[crystaldev1]

I would like to create one group for all of these fields that will show the distinct names from these fields. So the group for this example would show "Bob", "Steve", "Mark".

 

[briangriffin]

This is giving me some unexpected results in one of the records, but I'm hoping it's a problem with my sample data. Group on this formula:

whilereadingrecords;
stringvar array v_names := [{Data.Field1},{Data.Field2},{Data.Field3},{Data.Field4}];
stringvar array v_filtered := {Data.Field1};
numbervar x := 2;

while x <= 4 
do(
if v_names[x] in v_filtered
then
v_filtered
else
redim preserve v_filtered[ubound(v_filtered) + 1];
v_filtered[ubound(v_filtered)] := v_names[x];
x := x + 1);


join(v_filtered,", ")

Does the sort matter? In other words, is Mark, Bob, Steve to be considered the same as Bob, Mark, Steve? If so, we'll have to sort the array which is another problem of its own.

 

[lbass]

I thought you were looking for groups based on one name. If that is the case, you have to use a command, since one record can only appear in one group, and I'm guessing you would want your sample record: Bob, Steve, Mark, Bob, to appear in each of three groups Bob, Steve, and Mark. If this is true, then you use "add command" and enter:

select table.field1 as Name, table.otherfield1, table.otherfield2
from table
union
select table.field2,table.otherfield1, table.otherfield2
from table
union
select table.field3, table.otherfield1, table.otherfield2
from table
union
select table.field4,table.otherfield1, table.otherfield2
from table

Then you can group on {command.Name}, since all four fields will appear in the same field "Name". You should use the command as your sole datasource for the report, if you take this route, and that is why I showed "otherfields".

-LB

 

[crystaldev1]

Here is the sample set of records:
-------------------------------------------------------
Amount Date Name1 Name2 Name3 Ref1 Ref2
$5 1/1 Bob a
$10 2/1 Steve b-2
$20 3/1 Paul b-2
$1 5/10 Mark c
$8 4/5 Mark c
$3 6/2 Bob a
-------------------------------------------------------

There will be only 1 name and 1 reference number for each record.

I would like to group by Reference Number (Ref1 and Ref2) and by Name (Name 1, Name 2, Name3).

So the resulting set should look like this:

Group a
Bob
1/1 $5
6/2 $3
Group b-2
Steve
2/1 $10
Paul
3/1 $20
Group c
Mark
4/5 $8
5/10 $1

Also, keep in mind that there are large number of records. Thanks.

 

[lbass]

Which suggestions have you tried, and what were the results?

-LB

 

[briangriffin]

If you use a command such as this, or even create a view, then the report grouping is pretty straightforward. I'm including the sample data if you want a quick sanity check:

declare @v_table table
(amount money,
recorddate smalldatetime,
name1 varchar(12),
name2 varchar(12),
name3 varchar(12),
ref1 varchar(5),
ref2 varchar(5))

insert into @v_table
select 5,'1/1/2011','Bob',null,null,'a',null
union
select 10,'2/1/2011','Steve',null,null,null,'b2'
union
select 20,'3/1/2011',null,null,'Paul',null,'b2'
union
select 1,'4/1/2011',null,'Mark',null,'c',null
union
select 8,'5/1/2011','Mark',null,null,'a',null
union
select 3,'6/1/2011',null,'Bob',null,'a',null



-- Actual command starts here --

select 
amount,
recorddate,
case when name1 is null then
	case when name2 is null then name3 else name2 end
		else name1 end as Name,
case when ref1 is  null then ref2 else ref1 end as Ref
		
from @v_table

 

[crystaldev1]

I haven't tried it yet but I believe your solution would work best. Thanks.