grouping on two fields

[samilam]

I REALLY Need help. I have a report due tomorrow and am really stuck. I have a table that in it has two fields, team member 1 and team member 2. The group of people who can be team members is the same, meaning, each person can be team member 1 OR 2. So then i need a total of projects (records) that "john" has, weather he is member 1 or 2.

I keep thinking I need to show each record twice, but then how can a create two simultaneous groups, not a group within a group.

Any help would be SOOOO great!

 

[dgillz]

Please post sample data and desired results. Your crystal version would be nice too.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[Madawc]

You can add a table twice, the second time as an alias. But I don't think that fixes it.

If you need something quickly, do a pair of crosstabs and then manually add the totals. This might be done by exporting as an Excel file, giving something that looks nice even if it's not a long-term solution.

Another method is a 'mock crosstab'. This is something that looks like a Crosstab, but in fact you define each column yourself, normally as a running total. This would need to go in the report footer, because running totals count as the reports 'run' and they will not be complete until then.
Crystal should have included an example along with the Crosstabs. You can save a little time by doing a paste to a dummy report, changing the name and then pasting back.

The problem is, you'd need to do a total for each 'team member' for both fields.


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

 

[samilam]

I am using CRXi. Here is another description:

I have a report (see attached) where a group of users can be in one of two fields, user 7 or user 8. I need to group on the user, whether they are in either field and then get a total on them. Which means each record should show twice, so that I can see the total for each user. Does that make sense?

Here is the data:

RT GW C & M Fine Pack 14,650
RV WT Cd Video Manufacturing 4,314
WT AV Advanced Cardiovascular System 41,021
WT FM 3 M Company 1 344
WT RB 3 M Company 2 123
WT RB 3 M Company 3 2257
WT RV Advanced Cardiovascular System 34,566

So, WT is user 7 on a few records, but then user 8 on another. I need to see a list of all his project whethe he is user 7 or 8 and then get a total, and then again the other way... Group on user 8, etc.

 

[dgillz]

So do you want record 2 "RV WT" grouped with record 7 "WT RV"?

Or do you want all records that contain "RV" grouped together (1,3 and 7)?

This is the big sticking point.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[samilam]

Yes, I want all records that have RV, no matter which field they are in, user 7 or 8.

 

[dgillz]

In regards to records 2 thru 7, do you want another grouping, this time of the "WT" value?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[samilam]

Yes. And then for each user (initials) also.

 

[dgillz]

Do you have a table with all the users in it?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[samilam]

no, they are all in the same table. it's only one table, so no links.

 

[dgillz]

I don't know how to do this with one table.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[lbass]

You could accomplish this in a command. Start a new report->your datasource->choose "add command" as your datasource. Then set up the command something like:

Select 'team1' as whichteam, table.`teammember1` as team, table.`company`, table.`amt`
from table
union
Select 'team2' as whichteam, table.`teammember2` as team, table.`company`, table.`amt`
from table

You can then insert a group on {command.team}. The field {command.whichteam} will allow you to differentiate which team is represented. The syntax and punctuation used in a command varies by datasource and connectivity. If you're unsure, check another report->database->show SQL Query to see examples of how it should be written.

-LB