×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Fomula Field Not An Available Field in Chart Expert

thread149-1791725

Fomula Field Not An Available Field in Chart Expert

Fomula Field Not An Available Field in Chart Expert

(OP)
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

RE: Fomula Field Not An Available Field in Chart Expert

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

RE: Fomula Field Not An Available Field in Chart Expert

(OP)
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})

RE: Fomula Field Not An Available Field in Chart Expert

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

RE: Fomula Field Not An Available Field in Chart Expert

(OP)
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.

RE: Fomula Field Not An Available Field in Chart Expert

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

RE: Fomula Field Not An Available Field in Chart Expert

(OP)
I'm just getting back to the office.

Again, LBass, thank you for your assistance! Worked perfectly!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close