Fomula Field Not An Available Field in Chart Expert 1

[dleewms]

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

 

[lbass]

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

 

[dleewms]

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

 

[lbass]

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

 

[dleewms]

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.

 

[lbass]

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

 

[dleewms]

I'm just getting back to the office.

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