Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: formula to calc Average Post Reply Post New Topic
Page  of 2 Next >>
Author Message
ricky969
Groupie
Groupie
Avatar

Joined: 03 Jul 2008
Location: United States
Online Status: Offline
Posts: 53
Quote ricky969 Replybullet Topic: formula to calc Average
     Posted: 22 Aug 2009 at 11:12am

Records returned by the SP:

oDays = 3 (output parm)
 
RowName | EvPlayMach | CmPlayMach
Players                 150             100
Machines              317             350
CoinIn               14735          8750
CoinOut              4253           3750
TimePlayed        24023        19873
 
for both EvPlayMach and CmPlayMach I need to calculate three different averages using subreports and graphs:
- Avg per day
- Avg per machine per day
- Avg per player per day
 
For the "Avg per Day" i divide each of fields by the "oDays" outupt param and everything works just fine.
 
i'm having issues calculating the other two averages.
 
to calculate the Avg per Machine per Day the formula should be:
 
RowName/oDays/RowName where RowName = "Machines"
 
this formula does not work! how can i create a variable Machine = 317 which i can use for all records listed in column EvPlayMach  and another variable Machine = 350 which i can use for all records listed in column CmPlayMach
 
i hope i was able to explain the problem, if not please ask. i need to come up with a solution ASAP.
 
Thanks
 
rick
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 24 Aug 2009 at 7:16am

Not exactly sure where you are going with this but you can create a value field using a Running Total for "MAchines" (or other specific row based items). NOte this can be uused in another formula if it is palced on the footer after the data has been read/calculated.

SInce I cannot see your source data design I have to guess on how you would need to do this...
Add  RT as "MachineEvPlayMach"
Field to Summarize=EvPlayMAch
Type of Summary=Sum
Evaluate "Use a formula": {table.rowname}="Machines"
Reset as Never
IP IP Logged
ricky969
Groupie
Groupie
Avatar

Joined: 03 Jul 2008
Location: United States
Online Status: Offline
Posts: 53
Quote ricky969 Replybullet Posted: 24 Aug 2009 at 8:42am

Brilliant! it worked perfectly.

i tried using a shared variable, set in the main report and shared with the subreports but it didn't work. it was evaluating all rows instead of just the one named "Machines". The formula i used was:

Shared NumberVar EvPlayMach;
EvPlayMach := If {SM_Analysis_RPT;1.RowName} = "Machines" and {SM_Analysis_RPT;1.EvPlayMach} <> 0 then {SM_Analysis_RPT;1.EvPlayMach}
else 0;
 
results:
 
Players       0
Machines    2
CoinIn        0
CoinOut      0
TimePlayed 0
 
If you have time, can you tell me if there's a way to get only the value assigned to "Machines" and assign that to the shared variable?
 
thanks so much for your help. your solution saved me a lot of time and headaches.


Edited by ricky969 - 24 Aug 2009 at 8:44am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 24 Aug 2009 at 8:52am
You formula should have been doing what you wanted. You can always place it on the details section and tweak it to see what it is doing.
You probably do not need the second part of your statement...<>0...
If it is Zero it can still be included as it would not change the total.


Edited by DBlank - 24 Aug 2009 at 8:52am
IP IP Logged
ricky969
Groupie
Groupie
Avatar

Joined: 03 Jul 2008
Location: United States
Online Status: Offline
Posts: 53
Quote ricky969 Replybullet Posted: 24 Aug 2009 at 9:18am

I tried

EvPlayMach := If {SM_Analysis_RPT;1.RowName} = "Machines"  then {SM_Analysis_RPT;1.EvPlayMach}
else 0;
 
and
 
EvPlayMach := If {SM_Analysis_RPT;1.RowName} = "Machines"  then {SM_Analysis_RPT;1.EvPlayMach};
 
they both worked the same way:
 
in the detail sections they returned:
Players       0
Machines    2
CoinIn        0
CoinOut      0
TimePlayed 0
 
in the page header section the value returned was "0" instead "2"
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 24 Aug 2009 at 9:37am
Hmm,
2 things working agianst me here. First I shy away from variables and tend to use RTs and second I am not sure what your row level data looks like as or your report design so I am not sure how and what you have these evaluating.
If Lockwelle reads this he may chime in as he uses variables (where i use the RTs)...Sorry I don't have a better answer for this.
IP IP Logged
ricky969
Groupie
Groupie
Avatar

Joined: 03 Jul 2008
Location: United States
Online Status: Offline
Posts: 53
Quote ricky969 Replybullet Posted: 24 Aug 2009 at 10:59am
That's OK. I appreciate your help.
 
i do have one more new issue though...it appears that i cannot use graphs if any of the formulas is using running totals:
error: A print tiem formula tht modifies variables is used in a chart or map
 
formula: If {SM_Analysis_RPT;1.CmAllMachAllPlay} <> 0 then {SM_Analysis_RPT;1.CmAllMachAllPlay}/{#RT_Machines}/{?@oNumOfDays}
else 0
I guess the formula i'm using is classified as second-pass and is calculated at the same time that the chart is being printed! Please tell me there's a solution for this.


Edited by ricky969 - 24 Aug 2009 at 11:09am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 24 Aug 2009 at 12:30pm

Few things here. Are you using sub reports?

What is your row level data (sample)?
Your getting this via a Stored Proc? Can you alter your Stored Proc?
IP IP Logged
ricky969
Groupie
Groupie
Avatar

Joined: 03 Jul 2008
Location: United States
Online Status: Offline
Posts: 53
Quote ricky969 Replybullet Posted: 24 Aug 2009 at 12:57pm
yes, i have three subreports.
- avg per day (the graphs display just fine because i am not using RT, the SP is returning an output param called oDays that i can use for each of the rows (i.e. Rowname/oDays)
- avg per machine per day (does not allow me to add graphs, i'm using RT)
- avg per player per day (does not allow em to add graphs, i'm using RT)
 
yes, i'm using a stored proc and if there are no other solutions i will have to ask the dba to alter the SP.
 
RowName | EvPlayMach | CmPlayMach  | ...
Players                 150             100
Machines              317             350
CoinIn               14735          8750
CoinOut              4253           3750
TimePlayed        24023        19873
 
in the example above i need the average per machine per day which should be:
assuming oDays=5
 
CoinIn         14735/317/5
CoinOut        4253/317/5
TimePlayed  24023/317/5
 
 
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 24 Aug 2009 at 1:15pm
OK, if you do not have dupe rows in your data set here is a siple way around it. Instead of using RTs you can SUM a formula
create a formula field as "MachineSUM"
if {table.rowname}="Machines" then table.EvPlayMAch else 0
 
Using the insert summary SUM this field. You should get the same value as you RT (this can be used in headers or footers unlike the RT which must be a footer).
Now you can create a formula field using the SUM this instead of the RT and that should be graphable.
IP IP Logged
Page  of 2 Next >>
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.