Hi Experts! **I am relatively new to crystal**

I've been messing with this data for 2 weeks. (This is how my excel Database looks)

Chairs   Smith   2013   5
Chairs   Smith   2014   6
Chairs   Smith   2015   2
Tables   Smith   2014   8
Tables   Smith   2016   2
Lamp     Smith   2013   1
Lamp     Smith   2014   4
Lamp     Smith   2016   2

My mission is to arrange a report that shows what my client has bought - and highlight the items (chairs/tables/lamps) he's purchased in the past but NOT in 2016. (As we are trying to identify past clients who are no longer purchasing so we can follow up).

I would like the data to show up looking like
(Header) SMITH
(sub header) Product    2013 2014 2015 2016
(details) Lamp                   1     4     0      2

And then I would apply a conditional format to make the LAMP Sub header light up if there is nothing in 2016.

Similarly I'd like to also highlight SMITH if he hasn't purchased ANYTHING in 2016.

I have produced various reports with the wizard that work to a point but aren't laid out as I'd prefer. And the conditional formatting doesn't work if there are multiple product groups. I tried doing a report from scratch but I don't know how to make the years and qty's pop up in the header bar.

I'm comfortable trying new formulas as I do a bit of coding in Excel. Any suggestions would be greatly appreciated.

Thanks!
Suzy

Thank you Hilfy!

I ended up getting my desired layout using cross tabs. And it looks pretty good.

But now I'm finding an issue with trying to highlight the client that hasn't purchased anything in 2016.

I don't know how to make the highlighter (or conditional formatting) look at all the products that my client has purchased as a whole. It returns a highlighted result for example if they didn't buy a chair - but bought a table. I want it to highlight ONLY if they didn't buy any product at all.

Do you have any suggestions for that?

Sue

Getting that highlight is going to be a challenge, but I think it's possible.

You might try something like this:

If GetTotalValueFor('2016') = 0 then crRed else crBlack

-Dell