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
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
//{@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
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
-LB
RE: Fomula Field Not An Available Field in Chart Expert
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
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
Again, LBass, thank you for your assistance! Worked perfectly!