Crystal Reports , Grouping on multiple values

I have this report that I'm developing and need to explore a different approach to grouping on multiple values, I can resort to Sub-Reports but would rather seek the opinion of the experts at EE first. for the sake of simplicity here's the report.

SalesPerson  SalesDate      ItemNumber      SalesAuditor      SalesManager
    JJ           20120101      ABC123                              AA                              BB
   AA           20120102      ABC124                              AA                              AA
   BB           20120103      ABC125                              BB                                JJ
    JJ           20120104      ABC126                               JJ                                JJ
   AA           20120105      ABC127                              BB                               BB
   BB           20120106      ABC128                              BB                               BB

I need to be able to group by "SalesPerson" or "SalesAuditor" or "SalesManager" and show their sales activities, the catch is, any of these sales people can play more than one role on a given sale transaction, a sales person can be an auditor or a manager on a given transaction, how do I go about writing this.
Regards
Roberto Madro R.Programmer AnalystAsked:
Who is Participating?
 
mlmccConnect With a Mentor Commented:
My question is do you need to see all person's in all groups even if the count is 0?

I added SP as a salesperson, SA as Sales Auditor, and SM as Sales Manager.  They do not appear in the other categories.  the result is

SalesAuditor
      AA       1
      BB       1
      JJ       1
      MM       1
      SA       2

Salesmanager
      AA       1
      BB       2
      JJ       1
      SM       2

Salesperson
      AA       2
      BB       1
      JJ       1
      SP       2

If that is good for you then the report as written will work.  If you need to see

SalesAuditor
      AA       1
      BB       1
      JJ       1
      MM       1
      SA       2
       SM       0
       SP       0
 
Salesmanager
      AA       1
      BB       2
      JJ       1
      SM       2
      SA       2
       SP       0
       MM       0
 
Salesperson
      AA       2
      BB       1
      JJ       1
      SP       2
      SM       0
      SA       0
       MM       0
 
Then another method will be needed.

mlmcc
0
 
c0feeCommented:
I assume you will be selecting which type of employee to group by via a parameter?  If so, you can create a formula and group by the formula:

@EmployeeType:
IF ?EmployeeTypeParameter = "Sales Person"
     THEN SalesPerson
ELSE IF ?EmployeeTypeParameter = "Sales Auditor"
     THEN "SalesAuditor"
ELSE "SalesManager"

Then create a group on @EmployeeType
0
 
Roberto Madro R.Programmer AnalystAuthor Commented:
The request behind the report is as follows;

* How many instances of "SalesAudit" did JJ have, how many instances of "SalesManager" did he have in the same period and so on.

* How many instances of "SalesManager" did BB have in the same period, etc.

I don't have parameters on this report and they wouldn't work for what we are trying to do.

Thx
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mlmccCommented:
A record can only be in one group so you will have to
1.  Use a subreport for each grouping
2.  Generate records so that each record appears 3 times.

You could use a COMMAND like

SELECT  SalesPerson,  SalesDate,      ItemNumber,  "SalesPerson" as groupfield
FROM YourTable
UNION ALL
SELECT  SalesAuditor,  SalesDate,      ItemNumber,  "SalesAuditor" as groupfield
FROM YourTable
UNION ALL
SELECT  SalesManager,  SalesDate,      ItemNumber,  "SalesManager" as groupfield
FROM YourTable

You can now group on
   GroupFIeld then SalesPerson to get the appropriate totals

mlmcc
0
 
Roberto Madro R.Programmer AnalystAuthor Commented:
I tried this approach and I'm not seeing it, then I created 3 backend veiws to separate the data into logical groups (SalesPerson, SalesAuditor, SalesManager), and was able to get the data out that way I wanted, one thing remains however, I know have 3 reports but I need to combine them into one, I'm trying SubReports as I write you this reply but I may have to resort to Shared Variables to pass the data cleanly from the sub to the main, Your thoughts and couple of examples will be helpful.

Thanks
0
 
mlmccCommented:
WHy do you need to pass the data to the main report?

Just have the subreport display the results

If you are trying to do this in a single table or cros tab like structure then use a command like I provided or a similar view in the database that unions your 3 views.

mlmcc
0
 
Roberto Madro R.Programmer AnalystAuthor Commented:
Could you throw me couple of examples on this "groupfield", it's bit unclear still.

Thx
0
 
mlmccCommented:
I'll put together a database and report to show you what I mean

mlmcc
0
 
mlmccCommented:
Look t this report

mlmcc
Q-27640926-Command.rpt
Q-27640926.mdb
0
 
Roberto Madro R.Programmer AnalystAuthor Commented:
I'm getting  "FROM keyword not found where expected", I checked my code up and down the road and saw nothing that will prompt that, I'll check further but do you have any thoughts on this error.

Thx
0
 
mlmccCommented:
Here is the command I am using

 SELECT `Q_27640926`.`SalesDate`, `Q_27640926`.`ItemNumber`, `Q_27640926`.`SalesPerson`, "Salesperson" as groupfield
 FROM   `Q_27640926` `Q_27640926`
UNION ALL
 SELECT `Q_27640926`.`SalesDate`, `Q_27640926`.`ItemNumber`, `Q_27640926`.`SalesAuditor`, "SalesAuditor" as groupfield
 FROM   `Q_27640926` `Q_27640926`
UNION ALL
 SELECT `Q_27640926`.`SalesDate`, `Q_27640926`.`ItemNumber`, `Q_27640926`.`Salesmanager`, "Salesmanager" as groupfield
 FROM   `Q_27640926` `Q_27640926`


Did you save the database to your machine?
Did you then set the report data source location to that database?

mlmcc
Q-27640926-Command.rpt
0
 
Roberto Madro R.Programmer AnalystAuthor Commented:
I'm not sure what version of Crystal your wrote this on, because the moment I switched the single quote to double quotes I was up and running, I feel really silly now but it is what it is, give me another day before I call this one done.

Many Thanks
0
 
mlmccCommented:
I used CR XI R2.  The single quotes were added by Crystal.

mlmcc
0
 
Roberto Madro R.Programmer AnalystAuthor Commented:
Please allow me to add one more wrinkle to this request, as I began to process the output of the report I noticed some missing values, and that turned out to be the following:
Not all "SalesPerson" can be "SalesManager" or "SalesAuditor" and visa versa, so the data looks something like this;

SalesPerson  SalesDate      ItemNumber      SalesAuditor      SalesManager
    JJ           20120101      ABC123                         AA                  NN
   AA           20120102      ABC124                       DD                  ZZ                                     BB           20120103      ABC125                          BB                   MM
    JJ           20120104      ABC126                         JJ                     NN
   AA           20120105      ABC127                       XX                    ZZ
   BB           20120106      ABC128                       BB                   BB

Your thoughts please.

Thx
0
 
Roberto Madro R.Programmer AnalystAuthor Commented:
One more clarification as well, "SalesManager" and "SalesAuditor" are in two separate tables but all 3 tables are linked on the "ItemNumber" field.
0
 
mlmccCommented:
The separate tables shouldn't matter so long as the data is treated as a single record.

It shouldn't matter who is a manager/person/auditor unless you are trying to get the fact that MM has 0 in a category

mlmcc
0
 
Roberto Madro R.Programmer AnalystAuthor Commented:
Sorry, I should have explained better earlier, Yes, "MM" a "SalesManager" may not exist as a "SalesPerson"
0
 
Roberto Madro R.Programmer AnalystAuthor Commented:
After some tweaking, the solution you provided worked, other issues I'm seeing are attributed to data quality and has nothing to do with the code.

As usual mlmcc, you're a pro and I thank you.

Regards
0
 
Roberto Madro R.Programmer AnalystAuthor Commented:
Great work.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.