Only display data set to YES in Crystal Report

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!
printmediaAsked:
Who is Participating?
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.

Raghavendra HullurSoftware DeveloperCommented:
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.

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
mlmccCommented:
What version of Crystal?

mlmcc
printmediaAuthor Commented:
Version 14
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

mlmccCommented:
Did you try the comment above?

mlmcc
printmediaAuthor 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?
Raghavendra HullurSoftware DeveloperCommented:
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..
printmediaAuthor Commented:
That worked. Thanks!
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
SQL

From novice to tech pro — start learning today.