Only display data set to YES in Crystal Report

printmedia
printmedia used Ask the Experts™
on
Hi All.

Don't know if this is possible but going to give it a shot. We have a SQL table, myTable, that stores item numbers and descriptive information for item numbers:
Table fields: ItemNumber, ProductLine, ProductLineAppearOnReport, Height, HeightAppearOnReport, Width, WidthAppearOnReport, Color, ColorAppearOnReport, UnitOfMeasure, UnitOfMeasureAppearOnReport

As you can see each field has a "AppearOnReport" that will either be Yes or No.

We want to create a Crystal Report that will list the ItemNumber and only the fields that have the "AppearOnReport" equal to YES. The problem is that each item number will be different because some items we don't want to display the Height and Width on the Report while others we do. The problem is we don't want to display a blank where the field would display data if it was set to YES.

So for example, let's say we have item ABC (has ProductLineAppearOnReport = YES and ColorAppearOnReport = YES), item DEF (only has UnitOfMeasure = YES ) and item GHI (HeightAppearOnReport = YES and WidthAppearOnReport = YES) here's how the data should appear on the report:

ABC--ProductLineA--Blue
DEF--Box
GHI--12 feet--10 feet

As you can see there is no blank space where ProductLine or Color would be if it was set to YES for item DEF and the same for item GHI

Any idea if this can be done?

Thank you in advance!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Hi,

Are you trying to achieve this at SQL level or at Crystal Reports level?

Here is what you can try at Crystal level:

Instead of displaying multiple fields, you can create a formula and write code similar to below one to concatenate the fields values where "AppearOnReport" is set to YES.

Sample formula content:

ItemNumber
& if ProductLineAppearOnReport = 'YES' then '--' & ProductLine
& if HeightAppearOnReport = 'YES' then '--' & Height
& if WidthAppearOnReport = 'YES' then '--' & Width
& if ColorAppearOnReport = 'YES' then '--' & Color
& if UnitOfMeasureAppearOnReport = 'YES' then '--' & UnitOfMeasure

P.S. : You need to replace each of the fields mentioned in above formula content with actual database fields.


Hope that is a feasible one for you.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
What version of Crystal?

mlmcc

Author

Commented:
Version 14
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Did you try the comment above?

mlmcc

Author

Commented:
Thanks for your suggestion Raghavendra! How would I handle the column headers? Would I do it the same way as the data as you suggested?

Commented:
Hi,
Yes, that would do. In case you need space between column headers, you can include the same by including space between quotes like Column 1 & '  ' & Column 2 etc..

Author

Commented:
That worked. Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial