Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Formatting a report Post Reply Post New Topic
Author Message
bbfdsue
Newbie
Newbie
Avatar

Joined: 09 Mar 2017
Location: Canada
Online Status: Offline
Posts: 4
Quote bbfdsue Replybullet Topic: Formatting a report
    Posted: 16 Mar 2017 at 10:23am
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
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 20 Mar 2017 at 6:09am
How are your SQL skills? I think the only way you're going to be able to do this is with a command, which is a SQL select statement. What type of database are you connecting to? If you post the query that Crystal is running in the report that gets you the correct data (go to Database >> Show Query), I should be able to help you get the data in the format that you need.

-Dell
IP IP Logged
bbfdsue
Newbie
Newbie
Avatar

Joined: 09 Mar 2017
Location: Canada
Online Status: Offline
Posts: 4
Quote bbfdsue Replybullet Posted: 20 Mar 2017 at 7:18am
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
IP IP Logged
bbfdsue
Newbie
Newbie
Avatar

Joined: 09 Mar 2017
Location: Canada
Online Status: Offline
Posts: 4
Quote bbfdsue Replybullet Posted: 20 Mar 2017 at 7:18am
PS My data source is a simple excel spreadsheet.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 20 Mar 2017 at 7:58am
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
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.