Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.
Suppress Section if Field is Null
Hello,
I have a report that is grouped by Customer, then Engine Program, then Part Number. I would like to suppress everything for the Engine Program and Part Number sections if the Engine Program field is null. In other words I only want to see results if there is a value in the Engine Program field. Can this be done.
I have attached a copy of the report in case it helps.
Thank you
example.rpt
I have a report that is grouped by Customer, then Engine Program, then Part Number. I would like to suppress everything for the Engine Program and Part Number sections if the Engine Program field is null. In other words I only want to see results if there is a value in the Engine Program field. Can this be done.
I have attached a copy of the report in case it helps.
Thank you
example.rpt
Asked:
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
Nontheless CR might react irregular on dbNull values ... and besides of that it ist probably more self-documenting to cast that behaviour into some formula.
In the formatting formula "suppess section" of the two sections you could enter:
Furthermore you could insert another section telling "No engine defined" and garnish that with a suppress formula
In the formatting formula "suppess section" of the two sections you could enter:
IsNull({engine})
Furthermore you could insert another section telling "No engine defined" and garnish that with a suppress formula
not IsNull({engine})
It's been a while, but, contrary to what mlmcc said, I don't think CR suppresses a group if the field is null (I'm pretty sure I've seen a report with a group for a null field). I would hate to think that CR would just remove a whole group from the report because the group field was null.
The "Engine Program" field that you're referring to is tcspnm.pohspec, correct?
Is pohspec actually null, or is it just blank? Those are two different things. In your example report, that field is sometimes blank, but it's never null.
What do you want to "suppress"?
If you want to get rid of the entire group, including the details, the simplest thing would be to go into the record selection formula and add
and {tcspnm.pohspec} <> ""
That's assuming that the field is actually blank, not null.
If the field actually can be null at times (not just blank), you could add
and not IsNull ({tcspnm.pohspec})
That will remove any records where pohspec is blank (or null in the second case) from the report completely, so they won't be included in any summaries either.
For the record, instead of using the record selection formula, you could suppress the desired sections when the field was blank (or null), but then the report would still be reading those records and they would be included in any summaries.
James
The "Engine Program" field that you're referring to is tcspnm.pohspec, correct?
Is pohspec actually null, or is it just blank? Those are two different things. In your example report, that field is sometimes blank, but it's never null.
What do you want to "suppress"?
If you want to get rid of the entire group, including the details, the simplest thing would be to go into the record selection formula and add
and {tcspnm.pohspec} <> ""
That's assuming that the field is actually blank, not null.
If the field actually can be null at times (not just blank), you could add
and not IsNull ({tcspnm.pohspec})
That will remove any records where pohspec is blank (or null in the second case) from the report completely, so they won't be included in any summaries either.
For the record, instead of using the record selection formula, you could suppress the desired sections when the field was blank (or null), but then the report would still be reading those records and they would be included in any summaries.
James
Ok, what if I did not want to suppress it but have a label like "UnKnown" instead of the blank if the the tcspnm.pohspec field is blank or null ?
Ok, what if I did not want to suppress it but have a label like "UnKnown" instead of the blank if the the tcspnm.pohspec field is blank or null ?I've outlined that in my first response ... you create a separate section with only that text "Unknown" in it.
In the suppression formula of the "regular" section you use
isNull({tcspnm.pohspec})
isNull(tcspnm.pohspec}) or (trim({tcspnm.pohspec}) = "")
not isNull({tcspnm.pohspec})
not (isNull(tcspnm.pohspec}) or (trim({tcspnm.pohspec}) = ""))
Quite simple, isn't it ?
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
If you have a record but a field is NULL then frankhelk's method will work since the group will show as nothing.
My comment was thinking along the lines of joined tables where since there was no engine number the rest would also be null.
mlmcc
My comment was thinking along the lines of joined tables where since there was no engine number the rest would also be null.
mlmcc
frankhelk's ideas should work, but if you simply want to change the displayed value for tcspnm.pohspec, there are simpler solutions.
It seems that the field is actually blank, instead of null, but you can go ahead and check for both. Create a formula like the following (call it whatever you like):
if IsNull ({tcspnm.pohspec}) or {tcspnm.pohspec} = "" then
"UnKnown"
else
{tcspnm.pohspec}
If there is more than one pohspec for a customer and you want the "blank" one first, just put that formula in the pohspec group header, instead of the group name.
Or, you could change the group to use that formula, instead of the pohspec field, and then the "blank" pohspec will be replaced with "UnKnown", and you'll get an "UnKnown" group at the end of the customer.
James
It seems that the field is actually blank, instead of null, but you can go ahead and check for both. Create a formula like the following (call it whatever you like):
if IsNull ({tcspnm.pohspec}) or {tcspnm.pohspec} = "" then
"UnKnown"
else
{tcspnm.pohspec}
If there is more than one pohspec for a customer and you want the "blank" one first, just put that formula in the pohspec group header, instead of the group name.
Or, you could change the group to use that formula, instead of the pohspec field, and then the "blank" pohspec will be replaced with "UnKnown", and you'll get an "UnKnown" group at the end of the customer.
James
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports
From novice to tech pro — start learning today.
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - Excel 2010 Adva… 183 lessons
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
mlmcc