Show the same record in multiple groups

[crystaldev1]

Hello. I'm using CR 9 and Sql Server 2005. I have a question regarding grouping. If I have a record with 3 different fields which are True, then I would like to display that record in all 3 groups. For example:

I have

Table.bField1 = True
Table.bField2 = True
Table.bField3 = False.

I am using formula in a group as follows:

if Table.bField1 = True then "Group1"
else if Table.bField2 = True then "Group2"
else if Table.bField3 = True then "Group3".

As you can see in the formula, a record will not show up in more than 1 group if more than 1 condition applies. The example above should show that same record in "Group1" and "Group2" since the fields are set to True. I'm sure this is an easy fix. Thanks.

 

[Madawc]

You can add the same record more than once, as an Alias. Or you can write an SQL Command that will extract details as many times as necessary.


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

 

[crystaldev1]

ok, so how would you do it without the use of SQL command? Could you show me the formula for the grouping?

 

[lbass]

Can you explain what you are doing using actual fields and data, instead of using abstract references? This is hard to follow, and it would be better if we could understand the underlying logic--why do you want to do this?

-LB

 

[crystaldev1]

Ok sure.

I have a table called Customer. There are 3 fields for Customer that has to be marked yes or no. Hence, we have 3 fields in the Customer table called

"Existing" for existing customers, "Local" if a customer lives in the area, "VIP" if a customer is a regular.

I would like to group by these fields and a customer could actually show up in more than 1 of these groups as long as the fields are marked "Yes". So I have the report laid out as follows:

Existing Group
Mike
Local Group
Mike
VIP Group
Lisa

As you can see from the above example, Mike needs to be in both groups. But from the formula I am using, I am not able to show a customer in more than 1 group:

if Customer.Existing = Yes then "Existing Group"
else if Customer.Local = Yes then "Local Group"
else if Customer.VIP = Yes then "VIP Group".

Hope this clears it up. Thanks.

 

[lbass]

These should be in one field, not three. You can do this by using a command as your datasource:

select 'Existing' "Status", "table"."existing" "TorF", "table"."customer"
from "table"
union all
select 'Local' "Status", "table"."local" "TorF", "table"."customer"
from "table"
union all
select 'VIP' "Status", "table"."VIP" "TorF", "table"."customer"
from "table"

You could then group on {command.Status} and display {command.TorF} for the values.

I'm not sure of the punctuation/syntax for SQL Server, but you can adjust this as necessary.

-LB

 

[Madawc]

To add the same record more than once, just select it again. It gets given an alias name automatically.

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

 

[crystaldev1]

Thanks but is there any way to do this without the use of sql command? I have so much going on in the record selection as well as complicated formulas that I would like to avoid using sql command. I could use sql command just to pull those information but it would dramatically slow the performance when linked to other tables.

 

[lbass]

You should be using the command as your sole datasource, not linked to other tables. This is the best way to approach this, I think.

I guess you could add the same table multiple times, but this would hugely increase your overall record number. Bottom line is that a record cannot exist in more than one group at once, so you have to create additional records by one of these methods. However, rather than adding multiple tables, it probably would be more efficient to use a main report for one field, and then insert subreports for each of the other two.

-LB