Solved

Crystal Reports , Grouping on multiple values

Posted on 2012-03-20
19
275 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:codedigger
  • 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:codedigger
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
 

Author Comment

by:codedigger
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:codedigger
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:codedigger
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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:codedigger
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:codedigger
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:codedigger
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:codedigger
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:codedigger
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:codedigger
ID: 37767783
Great work.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now