Group and count multiple fields
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=20066
Printed Date: 02 May 2024 at 9:34am
Topic: Group and count multiple fields
Posted By: deanrhurst
Subject: Group and count multiple fields
Date Posted: 30 Sep 2013 at 4:32am
Hi
I am using crystal X1.
I have to create a report to total up the number of book sales we are making on a certain project.
My issue is;
-we have 50 different books we sell
-For each order the customer could order up to 3 different books
-In the data the book they have ordered will write to one of 3 fields
so my data headers looks like:
customerinfo | order1 | order2 | order3 |
So my actual data would look like:
Mr Jones | Book1 | Book2|Null|
Mr Smith | Book 7 | Book 4|Null|
Mr Toms | Book3 | Book 8 | Book 2|
etc etc etc
I need to create a report that tells me how many of each book have been sold.
So my report would look like:
Book 1 = 5 sales
Book 2 = 3 sales
Book 3 = 7 sales
and so on and so fourth for up to 50 publications.
I though one solution would be to join the 3 order fields and make an array in Crystal, but in truth I dont know how to do that or then how to create my required output.
Any help on a solution would be most appreciated.
Thanks
|
Replies:
Posted By: lockwelle
Date Posted: 30 Sep 2013 at 5:15am
what comes to mind is to create a command object that is something like:
select order1
from table
union all
select order2
from table
union all
select order3
from table
this will give you a table with just 1 column, which is all the book, and from here the report should be simple...group on the book, suppress the detail and group header and display the count in the group footer.
HTH
|
Posted By: deanrhurst
Date Posted: 30 Sep 2013 at 10:34am
|
Thanks so much Lockwelle, that has worked perfectly :) Many Thanks
|
|