Solved

Crystal Reports , Grouping on multiple values

Posted on 2012-03-20
19
280 Views
Last Modified: 2012-03-26
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
0
Comment
Question by:Roberto Madro R.
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 8
19 Comments
 
LVL 2

Expert Comment

by:c0fee
ID: 37744405
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
 

Author Comment

by:Roberto Madro R.
ID: 37744433
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 37744602
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 

Author Comment

by:Roberto Madro R.
ID: 37749515
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 37749609
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
 

Author Comment

by:Roberto Madro R.
ID: 37750016
Could you throw me couple of examples on this "groupfield", it's bit unclear still.

Thx
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 37750087
I'll put together a database and report to show you what I mean

mlmcc
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 37750501
Look t this report

mlmcc
Q-27640926-Command.rpt
Q-27640926.mdb
0
 

Author Comment

by:Roberto Madro R.
ID: 37754089
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 37754237
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
 

Author Comment

by:Roberto Madro R.
ID: 37754664
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 37754851
I used CR XI R2.  The single quotes were added by Crystal.

mlmcc
0
 

Author Comment

by:Roberto Madro R.
ID: 37754908
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
 

Author Comment

by:Roberto Madro R.
ID: 37754953
One more clarification as well, "SalesManager" and "SalesAuditor" are in two separate tables but all 3 tables are linked on the "ItemNumber" field.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 37754963
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
 

Author Comment

by:Roberto Madro R.
ID: 37755095
Sorry, I should have explained better earlier, Yes, "MM" a "SalesManager" may not exist as a "SalesPerson"
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 37755692
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
 

Author Comment

by:Roberto Madro R.
ID: 37767775
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
 

Author Closing Comment

by:Roberto Madro R.
ID: 37767783
Great work.
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question