Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Stop Running Totals using variables Post Reply Post New Topic
Author Message
kreusch
Newbie
Newbie


Joined: 04 Nov 2011
Online Status: Offline
Posts: 22
Quote kreusch Replybullet Topic: Stop Running Totals using variables
    Posted: 23 Aug 2016 at 8:36am
Hi,
I am using variables to create running totals for a report grouped by item class for inventory. I reset the variable to 0 (w:=0), use a formula to keep a running total (w:w+ {product total}), then I display the total (numbervar w;) It works great to show me the total for each item class. (I use a different variable for the item class totals, I)
I now want to use the ending total, w, as a divisor to come up with percentages for each item class. So I take (NumberVar I/NumberVar w)*100. However it keeps using the running total for the item class group for w, rather than the total for the whole report. Is there a way to convert the total variable w, to a number and use it in the formula?

Here is what it is doing:

item class studs (I)       5000    100%
item class structural (I)   5500     52%
total:             (w)      10500


Here is what I need it to do:

itme class studs     (I)    5000    47%
item class structural (I)   5500    52%
total                 (w)   10500


I cant use summations/normal running totals because I have other variables used to evaluate the dates of the transactions

Thanks for your help

IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 23 Aug 2016 at 10:01am
I don't think you use it as you have it set up now. The total value does not exist at the group level where you want to use it.
If your other criteria are row level criteria might be able to create a formula field to handle the amount per row then sum that result using group summary function which can be used in a group header.
IP IP Logged
kreusch
Newbie
Newbie


Joined: 04 Nov 2011
Online Status: Offline
Posts: 22
Quote kreusch Replybullet Posted: 24 Aug 2016 at 6:20am
Hi Dblank,
I thought about that but am having trouble converting the totals to a number. I tried ToNumber () hoping that it would save the number but it keeps reevaluating the formula. Is there a better way to change the totals to a number so that it is saved as a value rather than a variable?

Thanks
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 24 Aug 2016 at 6:51am
In theory you can just group on the item class
create a summary as a SUM the value field at the group level
create a second summary as a SUM of the value field as a but also as a % of the same sum of the same field for the report
if the 'value' field you are summing is conditionally included or excluded use a formula field in its place
like
if field = condition then table.value else 0
IP IP Logged
kreusch
Newbie
Newbie


Joined: 04 Nov 2011
Online Status: Offline
Posts: 22
Quote kreusch Replybullet Posted: 26 Aug 2016 at 3:04am
Hi DBlank,
Yes I would normally do the grouping and use a summation with the percentage. However I have to use variable to evaluate the dates. I use a dataset to pull in all the fiscal year data based off of the month and year the user selects. For fiscal year to date, it works fine because i just use all the data. But for the month to date i have to find the maximum date, then find the month of that date and then if that month equals the month of the invoice date I pull the total. Since I do this all with variables I am unable to sum it.
Is there a better approach that would allow me to get away from variables and then i would be able to use the summation function?
Thanks for your help
k
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 26 Aug 2016 at 4:13am
if I understand you you can use a formula to create a value you could sum
//mtd formula
if {table.Datefield} in monthtodate then {table.valuefield} else 0
IP IP Logged
Post Reply Post New Topic
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



This page was generated in 0.047 seconds.