Print Page | Close Window

Display a percentage in subtotal

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=9976
Printed Date: 02 May 2024 at 2:21am


Topic: Display a percentage in subtotal
Posted By: lw1990
Subject: Display a percentage in subtotal
Date Posted: 11 May 2010 at 8:56am

Hi,

I have a very simple report showing the amount of $ and a subtotals at Group Footer:

I’m trying to add a percentage under subtotals in the Group footer #1 like this:

 

Name |  Hours |--- |   BillableAmt    |   Non_BillableAmt      |    TotalAmt

---      |   ---    |           $---             |     $---                       |        $---

---      |   ---    |           $---             |     $---                       |        $---

                             ________________________________________

                                $4000               $1000                          $5000

                                 (80%)               (20%)        

                 

I tried to use percentofsum, but I didn’t use it right.

Can someone tell me how to display this (80%) and (20%) under the subtotal amount?

 

Thanks.


Replies:
Posted By: DBlank
Date Posted: 11 May 2010 at 9:15am
2 formula fields
 
SUM(billable_amount,grouplevel) % SUM(total_amount,grouplevel)
 
SUM(non_billable_amount,grouplevel) % SUM(total_amount,grouplevel)
 
Right click and use format field and use the insert the 'Display currency symbol' option as True but make it a % in the customize tab.

Posted By: lw1990
Date Posted: 11 May 2010 at 10:23am
Thanks DBlank,
 
What is the argument "grouplevel"? I got an error when I use it in Formula Workshop - Formula Editor on this "grouplevel":

A number, currency amount, boolean, date, time, date-time, or string is expected here.

 

 

Posted By: DBlank
Date Posted: 11 May 2010 at 10:28am
sorry for not being clear.
the "group level" is the field that you are grouping on, like sales rep.
SUM(field, grouplevel)
might really look lile
SUM({sales.amount},{sales.sales_agent})

Posted By: lw1990
Date Posted: 11 May 2010 at 11:20am
Thanks DBlank, it works!
For my case, it is:
SUM({sales.BillableAmt},{sales.Name}) % SUM({sales.TotalAmt},{sales.Name})
 
Now, I can display the number "%20" and "%80" as you directed earlier, how can I get the "%" displayed to make it "20%" and "80%" ? There is no place to set the field data type as a Percentage.
 
Thanks.

Posted By: DBlank
Date Posted: 11 May 2010 at 11:32am
Right click on formula field  (the 80 or 20)
select format field
Click on the Number Tab
click on the customize button
click on the currency symbol tab
Enable currency symbol (mark the check box as true)
leave it as Fixed
in the symbol type in '%' (over the existing $)
Change the position pick list to use "-123%"
click OK
click OK
 
 

Posted By: DBlank
Date Posted: 11 May 2010 at 11:37am
just a warning.
If a user can run the report and the
SUM({sales.TotalAmt},{sales.Name})
can be ZERO
you will want to account for this as it will throw a 'division by zero' error (say they run the report for 1 day whcih had no sales at all). They will not see the report but only this error which makes it look like you messed up on the design.
 
You can deal with this as an if-then in your orginal formulas.
 
if SUM({sales.TotalAmt},{sales.Name}) = 0 then
0 else SUM({sales.BillableAmt},{sales.Name}) % SUM({sales.TotalAmt},{sales.Name})

Posted By: lw1990
Date Posted: 11 May 2010 at 11:51am
Yes, I didn't set the position. It is "20%" and "80%" now.
And yes, I took care the data in the database of the report. It is better to use a View to get the data from table, and take care the "0" problem in the TotalAmt field. 
 
Thanks.


Print Page | Close Window