Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Fomula Field Not An Available Field in Chart Expert 1

Status
Not open for further replies.
dleewms

dleewms

Programmer
Aug 19, 2001
118
US
Hi,

I using Crystal Reports 2013.

I am grouping my report by departments. I've created the following two running totals.
#ActiveEmployees
Field to Summarize: SSN
Type of Summary: Distinct count
Evaluate: isnull({Termination Date})
Reset: On Change of Group #1

#FiftykEmployees
Field to Summarize: SSN
Type of Summary: Distinct count
Evaluate: If isnull({Termination Date}) AND Salary >=50000
Reset: On Change of Group #1.

I then created a formula to display percentage of employees per department.
@DeptCount
if {#ActiveEmployees} = 0 then 0 else {#FiftyKEmployees} / {#ActiveEmployees} * 100

Ultimately, I want to use a chart, perhaps a doughnut or gauge chart, to display the percentage of active employees who earned more than 50K. The chart will be placed in the Group Footer section. However, the @DeptCount field is not in the Available Fields list on the Data tab.

Any suggestions to get the chart created will be greatly appreciated.

Thanks,
DLeeWms
 
Sort by date Sort by votes
If you use regular summaries instead of running totals, you can chart the percentage. First create a formula {@null} by opening a new formula and then saving without entering anything. Then create formulas like this:

//{@active}:
If isnull({table.termdate}) then
{table.SSN}else
{@null};

//{@50k}:
If isnull({table.termdate}) and
{table.salary}>=50000 then
{table.SSN} else
{@null}

Then create a formula {@percent}:
Distinctcoount({@50k},{table.dept})%Distinctcount({@active},{table.dept})

You should be able to chart on this formula. By using {@null} in the earlier formulas you are eliminating a distinctcount of 1 representing all items that don’t meet your criteria, i.e., those that equal 0. {@null won’t be counted.

If the SSN is a number field and not a string, then wrap {@null} in tonumber() in each formula.

-LB
 
Upvote 0 Downvote
Thanks so much for your assistance Lbass. This was perfect. The formula returned the correct percentage and it is listed in the Available Fields on the data tab for my chart.

However, the chart (Doughnut) returns with the entire area shaded in. For example, if the returned percentage is 30, I only want 30% of the circle shaded, not the entire 100%.
On the Chart Expert, I've selected Advanced Layout. On Change of field is Dept. and Show Values is @Percent. I've tried creating a formula @One Hundred which simply returns 100. I've selected the @OneHundred field for my On Change Of and get the same results, the entire doughnut is shaded, not just 30% of it.

Do you know what I'm doing wrong?

Thanks!

Also, in the @Percent formula, there's a small typo. Here is the correct formula
Distinctcount({@50k},{table.dept})%Distinctcount({@active},{table.dept})
 
Upvote 0 Downvote
In wWhat section did you place the chart? If you placed it in a dept group section, only one value will be returned. Place it in the report header or footer instead—if this is indeed the issue. Otherwise, please explain where you located it.

-LB
 
Upvote 0 Downvote
It was originally in the GF section because for each department, I want a graphic to display the percent of individuals in that department only who earned more than 50K. The formula returns that let's say HR has 30% of employees who make more than 50K, but I'm not sure how to get the chart to show 30%. Somehow, it needs to know/display it as 30 out of 100.

When I place chart in Report Footer, the number 30 is returned for HR, but each department is represented in the chart.
 
Upvote 0 Downvote
Okay, insert a chart in the group footer then. First create another formula {@Other} (name the formula what you want the non-50k people to be named in the chart):

100-{@percent}

Then use department as the on-change of field and add both {@percent} (you should rename this too though) and {@Other} as the show value fields. I got an error message when I did the doughnut chart, but the chart generated correctly anyway.

-LB
 
Upvote 0 Downvote
I'm just getting back to the office.

Again, LBass, thank you for your assistance! Worked perfectly!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

smmt2207
  • Locked
  • Question
Text field formula help
Replies
1
Views
502
hilfy
hilfy
G
  • Locked
  • Question
Multiple groups in same level
Replies
0
Views
164
george1610
G
David Richardson
  • Locked
  • Question
Adding BLOB Fields to Reports in Crystal Reports Professional 8.5
Replies
7
Views
870
hilfy
hilfy
K
  • Locked
  • Question
We're using command in crystal report
Replies
1
Views
577
hilfy
hilfy
JustMe24
  • Locked
  • Question
How to select from 2 rows in a table
Replies
1
Views
490
SoftwareRT
SoftwareRT

Part and Inventory Search

Sponsor

Back
Top