| Author |
Message |
lw1990
Newbie
Joined: 04 Jun 2009
Location: United States
Online Status: Offline
Posts: 5
|
   Topic: Display a percentage in subtotal
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.
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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.
Edited by DBlank - 11 May 2010 at 9:17am
|
|
IP Logged |
|
lw1990
Newbie
Joined: 04 Jun 2009
Location: United States
Online Status: Offline
Posts: 5
|
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.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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})
|
|
IP Logged |
|
lw1990
Newbie
Joined: 04 Jun 2009
Location: United States
Online Status: Offline
Posts: 5
|
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.
Edited by lw1990 - 11 May 2010 at 11:29am
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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
Edited by DBlank - 11 May 2010 at 11:33am
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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})
|
|
IP Logged |
|
lw1990
Newbie
Joined: 04 Jun 2009
Location: United States
Online Status: Offline
Posts: 5
|
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.
|
|
IP Logged |
|
|
|
