We help IT Professionals succeed at work.

Crystal Reports Chart - Forula Fields

Genius123
Genius123 asked
on
Hello,

My chart shows a blank in one of Ledgend Keys.  For the field NoSubDepartment, if {qryCOQ.CSIID} >0, I want it to show "Test" and nothing else.  Instead it's showing "Test" and a blank for all the other ones that are not >0 are showing a blank.  File is attached.

Thanks,
Joel Experts-11-1.rpt
Comment
Watch Question

Mike McCrackenSenior Consultant
SILVER EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Try changing your formula.  

if {qryCOQ.CSIID} >0 then
    "Test"
Else
    {qryCOQ.CSIIssueDepartmentSub}

By leaving the ELSE off the formula returns nothing

mlmcc

Author

Commented:
The formula would be more like:

if {qryCOQ.CSIID} >0 then
{qryCOQ.CSIIssueDepartmentSub}

it's still showing blanks in the ledgend.  Sorry, I'm if I'm not following.
Mike McCrackenSenior Consultant
SILVER EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Try it as

if {qryCOQ.CSIID} >0 then
   {qryCOQ.CSIIssueDepartmentSub}
Else
   "Test"

mlmcc

Author

Commented:
THat made it worse.  I dont want Test or a blank to show up.  See attached. Experts-11-1.rpt

Author

Commented:
Also, if I could just put a separate filter on just the chart, that would solve the problem.  Is that possible?
Mike McCrackenSenior Consultant
SILVER EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
You can't really filter the data from the chart however you could use a group on a formula like

if {qryCOQ.CSIID} >0 then
   {qryCOQ.CSIIssueDepartmentSub}
Else
   "Test"

Put the chart in the group header.  
You could suppress the Test group header

mlmcc

Author

Commented:
I get what you're saying, but putting it in the group header shows a chart for every instance.  I can supress the Null or "" values, but it will show a separate chart for all the other categories .  I only want one chart.
Mike McCrackenSenior Consultant
SILVER EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Sorry.  Meant for the formula to result in 2 groups

if {qryCOQ.CSIID} >0 then
   "WantThese"
Else
   "DontWantThese"

Author

Commented:
There are more than two groups though.

if {qryCOQ.CSIID} >0 then
 if {qryCOQ.CSIIssueDepartmentSub} <> "" then
   {qryCOQ.CSIIssueDepartmentSub} (could equal various departments, but if department is blank, don't show a blank in ledgend)
else
Don't show a blank value
else
Don't show a blank value
Mike McCrackenSenior Consultant
SILVER EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
If you group on the formula I gave you then there are 2 groups.
Put the chart in that group header

You can then group within that group on the department name

You can use this for the groups inthe chart or as inner groups

if {qryCOQ.CSIID} >0 then
 if {qryCOQ.CSIIssueDepartmentSub} <> "" then
    {qryCOQ.CSIIssueDepartmentSub}
  Else
     "Test"
Else
  "Test"

mlmcc

   
Senior Consultant
SILVER EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
Is this more like what you want?

mlmcc
Experts-11-1Reva.rpt

Author

Commented:
That's brilliant!  It worked. Thank you.  However, there's just one more obsticle and am so so so close!  Certain formulas calcuate based on the department.  So I had to add another group level for NoSubDepartment.  Notice the graph gives me the same value for both months.  I think it's because of the way it's grouped.

In short, one of my formulas might look like this: (Sum ({qryCOQ.CSISellOne}, {@NoSubDepartment}).  How can I sum the field based on a field if I remove the group for that field?

File attached. experts-11-2.rpt
Mike McCrackenSenior Consultant
SILVER EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
I don't follow the question.

Can you show what the issue is?

mlmcc

Author

Commented:
I figured it out.  Too complicated to explain - my oversight.  I'm going to accept your solution above now.

Author

Commented:
That's brilliant!  It worked. Thank you

Explore More ContentExplore courses, solutions, and other research materials related to this topic.