Print Page | Close Window

Using report total in calculation

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=8654
Printed Date: 02 May 2024 at 4:59pm


Topic: Using report total in calculation
Posted By: developer
Subject: Using report total in calculation
Date Posted: 16 Dec 2009 at 8:24am
Hi all,
I'm trying to use the report total of a calculated field(Qty * price) to find its
percentage.
Here's an example, I'm looking for a solution to get the percent values in red.
 
                                                    % formula -> (qty * price)
                       (Qty * price)                               / report total of Qty * price 
   eg row 1      $261,508.84           15% -> (261,508.84 / 1,657,473.26)
        row 2      $189,183.97           11% -> (189,183.97 / 1,657,473.26)
        row 3              .                        .
        row 4              .                        .
report total   $1,657,473.26
 
Thanks


Replies:
Posted By: DBlank
Date Posted: 16 Dec 2009 at 8:57am
Create one formula to get the row total(qty* price)
Call it 'RowAmount':
table.Qty * Table.price
Create another formula to get your % per row.
mailto:%7b@rowAmount - {@rowAmount } % SUM( mailto:%7b@rowAmount - {@rowAmount })
Report total = SUM( mailto:%7b@rowAmount - {@rowAmount })
 

Posted By: developer
Date Posted: 16 Dec 2009 at 11:29am
Hi Dblank,
When I try to save the percent formula that you suggested, I get a "The summary/running total field could not be created" error.
 
I'm not sure if this is related to the version.I am using ver 8.0.
Soon to be upgraded to XI
 
Thanks
 

Posted By: developer
Date Posted: 16 Dec 2009 at 11:37am
One other thing, my qty comes from the table but my price comes from a subreport that gets the latest price. I do this  because some of the same item #'s on different transactions don't always have a price therefore I force a price on the ones that are at $0.00.
 
That might be why I'm getting that error.

Posted By: DBlank
Date Posted: 16 Dec 2009 at 11:40am
Yes. The subreport issue would break this formula.
Is there another way to force a price without using a subreport?

Posted By: developer
Date Posted: 16 Dec 2009 at 11:49am
I don't think so, because my subreport has to go find the price from another transaction of the same item #.
 
If my main report was sorted by item #, then I could easily use the price from a previous transaction by placing the price in a variable & using it based on the price being 0.00 or not.
 
Thanks anyway, I'll have to think of something else.
 
P.s. Too bad Crystal doesn't allow a subreport to run within a subreport, then I could run the whole report as a subreport in the report header to get my report total & use that value in my main report for the % calculation.

Posted By: DBlank
Date Posted: 16 Dec 2009 at 12:05pm

Can you make a SQL view or a crystal command to get a 'master price list table' for each item # then join  that into your table and use it in the calculation instead?



Print Page | Close Window