Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Divide data into two groups Post Reply Post New Topic
Author Message
eluke
Newbie
Newbie


Joined: 20 Jan 2009
Location: United States
Online Status: Offline
Posts: 5
Quote eluke Replybullet Topic: Divide data into two groups
     Posted: 20 Jan 2009 at 6:41am

Hey,

I am writing a sales report based off of invoices.  We have technician codes that are the techs initials.  I am grouping the invoices by technicians, which is simple.  However, some invoices have codes that are a combination of the techs initials since they did the work together.  I am dividing the amount of the invoice if it is a combination so I can get individual results. I am able to do this and add the amount to each of their groups.  The problem comes when I need to display a technician in his group if he only did combined work.
 
This is part of my code.  It runs through all of the technicians names.
 
if {Invoice.SalespersonID} = "cc"
then "Camden Craig"
else if {Invoice.SalespersonID} = "fc"
then "Frank Crocker"
else if {Invoice.SalespersonID} = "jrm"
then "J R Millison"
 
This displays all of the names I want unless they only did work as a group for the time frame.  I cannot do the following code because I can only add it to one person and not the other.  Is there a way around this?
 
if {Invoice.SalespersonID} = "bl/mg"
then "Michael Glover" else
if {Invoice.SalespersonID} = "bl/mg"
then "Bryan Ledford"
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 20 Jan 2009 at 8:24am
You can handle this in a few ways.
Ifgroup displaying is not needed and you can just show totals/sums of these different combinations this would be the easiest solution (as long as you don't have a lot of different  salespersons).
Create one formula per salesperson and label it the salesperson name or initital to keep track like this:
if instr({Invoice.SalespersonID},"cc")>0 then "Camden Craig" else ""
From here you can do conditional running totals based on each of these formulas to get counts/sums of the other data and it would count the shared sales into each of the reps totals.
Will this work for you?
IP IP Logged
eluke
Newbie
Newbie


Joined: 20 Jan 2009
Location: United States
Online Status: Offline
Posts: 5
Quote eluke Replybullet Posted: 20 Jan 2009 at 9:37am
I do have a lot of technicians.  Also, when I tried that formula, it only pulled Bryan up and not Michael because the if Bryan was listed first.  Any more suggestions?  I have run out of ideas.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 20 Jan 2009 at 9:40am

It will only pull up Bryan but when you get to doing a second field for Michael it will also show up for that one as well. This is why you couldn't group on it...

Are you using SQL? If so do you have rights to creating views?
IP IP Logged
eluke
Newbie
Newbie


Joined: 20 Jan 2009
Location: United States
Online Status: Offline
Posts: 5
Quote eluke Replybullet Posted: 20 Jan 2009 at 12:18pm

Thanks for all of your help.  No, I do not have rights to create views, but I am pulling from a sql database.

I created the two fields for the technicians.  They are showing up, but I am unsure how to use it. 
 
This is my group formula to pull the list together.
 
if {@BL/CC} = "Bryan Ledford" or {@BL/MG} = "Bryan Ledford" or {Invoice.SalespersonID} = "BL" then "Bryan Ledford" else
if {@CC} = "Camden Craig" or {Invoice.SalespersonID} = "CC" then "Camden Craig" else
if {@MG} = "Michael Glover" or {Invoice.SalespersonID} = "MG" then "Michael Glover"
 
I tried this formula to group them so I could have a list of techs with figures.  However, I see that you say I cannot group them. 
 
This is going to be a one page summary report.  On it, I need a list of my 18 techs and their total amount billed.  If I don't group them, I don't know how to list them.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 20 Jan 2009 at 12:43pm
This will be a little labor intensive but it will work.
Created 18 running totals (1 per employer).
As an example here is Canden's.
Running total Name =Camden or CC
Field to summarize=amount billed field
Type of Summary=Sum (I assume a sum is what you need here)
Evaluate as "use a formula"
instr({Invoice.SalespersonID},"cc")>0
Reset as Never
and repeat this for each worker changing the initials per RT in the formula.
In your report Footer (running totals do not work in headers) Create 18 footers. Place a text box with each name and it's corresponding running total next to each name (one per footer). I recommend that because they are easier to move around in the future.
 
By grouping you will start excluding the "shared" jobs. This running total process will include them in each of the workers (e.g. counting it twice).
Note this will not preclude you from doing a grouping on the Salesperson ID and doing sums there where you could show the records once and each iteration of single and duoid' and an unduplicated real total.
 
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 21 Jan 2009 at 6:52am
I don't know, just throwing out an idea, as this solution can be a maintenance headache, could this be done in a subreport? or can you create stored procedures? (I know you can't do views but...)
 
Here is an idea, since the problem lies in the fact that if they only worked in a group, it is harder to group them, ok, nigh impossible. So, get rid of that requirement by select all tech in a command and then apply the grouping based on that field....
 
Let's see if I can say this better in another way.
 
On the database expert, on the data connection, select the command option.  Enter something like select distinct {techinitial field} from techtable.  Then, for the grouping, group on {command.techinitial field}.
 
I think that this can work, but it has been a long time since my reports run off of XML files created by stored procedures.  I don't know if the command will appear in the parameter request...I don't think so, but again my reports run off of a wizard so that I don't have to use the CR parameter string (not very flexible--or pretty)
 
Hope this helps
IP IP Logged
eluke
Newbie
Newbie


Joined: 20 Jan 2009
Location: United States
Online Status: Offline
Posts: 5
Quote eluke Replybullet Posted: 21 Jan 2009 at 9:52am

DBLANK,

Thanks for the tip on the report footer idea.  That is working out great, even though it is labor intensive like you said, but after working on a solution for a few days, I will take it.
 
Thanks,
Emilie
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.