Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Formula Adding Post Reply Post New Topic
Author Message
biggysmalls
Newbie
Newbie
Avatar

Joined: 12 Feb 2009
Location: United Kingdom
Online Status: Offline
Posts: 12
Quote biggysmalls Replybullet Topic: Formula Adding
     Posted: 16 Mar 2009 at 10:16am
Hi guys, yet again Ihave a problem.  I have 3 columns of data that I am showing in a crosstab report.  I need some percentages numbers.
 
1st column is rowtype - starter, referral, leaver etcc.
2nd column periodtype - when it happened, year to datem last week,
3rd counter = this column indicates via a one or zero whether they started in a particualr week.
 
bearing in mind th crosstab groups by row - rowtype and column- periodtype and Sum = count.  How can I crate a formula that does starts/referrals .  I would have to somehow say "sum count where rowtype = "start" / sum count where rowtype = referrals" in crystal.
 
Anyone know how to do this?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 16 Mar 2009 at 11:06am
You can either do this by grouping on the Counter field and summing the records or you can use a running total per counter type and conditional set it per type then sum or count those.
Not sure about using the running total in a crosstab. For that you may have to stick with a group on that field.


Edited by DBlank - 16 Mar 2009 at 11:06am
IP IP Logged
biggysmalls
Newbie
Newbie
Avatar

Joined: 12 Feb 2009
Location: United Kingdom
Online Status: Offline
Posts: 12
Quote biggysmalls Replybullet Posted: 17 Mar 2009 at 2:37am

Thanks for that.   Yeah I am summing on each particular group however I now want to use two different grouping summations to calculate certain things.  For example, I have a total sum for starts group, a total sum for referrals group.  These appear in rows on the crosstab, the row (start referal) is one field.  I need to be able to put a formula in another field to work out the sum of starts divided by the sum of refferals.  I'm not sure of the crystal syntax.  
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 17 Mar 2009 at 7:15am
Does this have to end up in a crosstab column or just appear in the report somewhere?
IP IP Logged
biggysmalls
Newbie
Newbie
Avatar

Joined: 12 Feb 2009
Location: United Kingdom
Online Status: Offline
Posts: 12
Quote biggysmalls Replybullet Posted: 17 Mar 2009 at 8:00am
At first i wanted it in the crosstab - however I would settle for anywhere at the moment.
 
I have 3 columns with data like this:-
 
 rowtype, periodtype,count
start, YTD, 1
start,YTD,1
referral,YTD,1
referal,YTD,1
 
I need to be able to say I want to sum count when rowtype = start and Period type is = YTD and divide the answer by the sum of count when rowtype = referral and periodtype = YTD.
 
That sort of thing. Also while were on the subject of tabs, I want to put a label above the column in a crosstab, when i drag on the text it does not appear.  Any ideas?
 
Thanks for your responses!
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 17 Mar 2009 at 8:22am
You could use running totals which allow for counting or summing based on record conditions then use those running totals in a formula or make two formulas and sum on them. Here is how to do that.
The example is assuming you want a count of every instance in the table where the conditions are met.
Formula1 example called "StartCount":
if table.rowtypefield = "start" and table.Periodtypefield= "YTD" then 1 else 0
Formula2 example called "ReferralCount":
if table.rowtypefield = "referral" and table.Periodtypefield= "YTD" then 1 else 0
 
Create a Summary Formula called "DivisionStartReferral", note if Referral COunt coulod be =0 then you need to add an if statement to handle that or it will give you a division by 0 error:
 
Not sure about the Crosstab label. Can you describ emore what you want to do with that?
 
IP IP Logged
biggysmalls
Newbie
Newbie
Avatar

Joined: 12 Feb 2009
Location: United Kingdom
Online Status: Offline
Posts: 12
Quote biggysmalls Replybullet Posted: 17 Mar 2009 at 10:01am

Thanks for that D , i'll let you know how it goes!

the label thing is just I have columns with field of yesterday,Weektodate etc, above these I wanted to put the actual dates on top of them, so to theres no confusion as to the periods the report is looking at, I've done this in a normal standard report but putting them above each column in a crosstab is proving tricky.

 

Thank You for all your help so far!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 17 Mar 2009 at 11:15am

Not sure about the label. If you can create a formula that would give you the correct text per record grouping you could probably use that to replace the group or add to it via Cross Tab expert / Column /Group Options / Options Tab Use a formula as a group Name...



Edited by DBlank - 17 Mar 2009 at 11:16am
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.