Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Forula to find total of values across multiple col Post Reply Post New Topic
Author Message
RoadRunner
Newbie
Newbie
Avatar

Joined: 06 Aug 2014
Online Status: Offline
Posts: 8
Quote RoadRunner Replybullet Topic: Forula to find total of values across multiple col
     Posted: 18 Aug 2014 at 4:44am
Hi - hoping someone can help.
 
I have a number of fields of data showing employee names. These names can differ depending on which country i al looking at.
 
What I need is a formula that looks at each column and if a name appears in any column it returns the value 1. So I can get a total at the end of the report for each employee.
 
I can do this easily by hard coding in a formula for each employyee using running totals but this is not very flexible when we have a large ever changing work force.
 
Any idea of how I can do this in a more clever and flexible way?
Thank You!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 18 Aug 2014 at 6:00am

Confused by the data set...

First it sounds like you have different name columns, 1 per country.
I would assume if a row exists that at least one of those columns would have a name, other wise why would the row exist, unless you are tracking other resources in the same table.
Do you have an employee ID field and an 'active' field? I owuld think the DB should be able to indicate curretn employess from past employees unless they get deleted?
IP IP Logged
RoadRunner
Newbie
Newbie
Avatar

Joined: 06 Aug 2014
Online Status: Offline
Posts: 8
Quote RoadRunner Replybullet Posted: 18 Aug 2014 at 6:29am
Hi - sorry to not be more clear.
 
I have at least 3 colums of names.
 
e.g. John       Trudi      Sam
        Trudi      Trudi      John
 
Any name could appear in any column (or not at all). If a name appears in a row this should count as 1 and I need to know how many rows each name appears in.
 
So above
 
John = 2
Trudi - 2
Sam - 1
 
 
Thank You!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 18 Aug 2014 at 8:09am

if you have a finite amount of columns and and infinite amount of possible names I think I would I use a union query to alter the data set to make it simple

something like:
select name, count(distinct Counter) from
(
select Col1NameField as Name, Col1NameField+'Col1' as Counter from table
UNION
select Col2NameField as Name, Col2NameField+'Col2' as Counter from table
UNION
select Col3NameField as Name, Col3NameField+'Col3' as Counter from table
) as NewTable
group by newtable.Name


Edited by DBlank - 18 Aug 2014 at 8:10am
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.