Formatting a report
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22267
Printed Date: 05 May 2024 at 5:48pm
Topic: Formatting a report
Posted By: bbfdsue
Subject: Formatting a report
Date 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
|
Replies:
Posted By: hilfy
Date 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
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: bbfdsue
Date 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
|
Posted By: bbfdsue
Date Posted: 20 Mar 2017 at 7:18am
|
PS My data source is a simple excel spreadsheet.
|
Posted By: hilfy
Date 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
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
|