Crystal Reports Group sorting

OK Here goes...
I have a crystal report that reports on how an item is selling and breaks down that item by color.  There are 2 groups.  Group 1 is an item# and group 2 is color field.  The table also contains quantity sold period1, period2, ... period12- for 12 months.  Here is a sketch...  

Group1- Item1              Jan                          Feb                          Mar  
   Group2- BLUE             2                              0                            5    
   Group2- RED              4                              1                            7    
   Group2- YELLOW        1                              2                            2    
                               --------------            --------------             ----------------
Group1 Footer           sum of P1               sum of P2                 sum of P3         formula1 = (sum of P1) + (sum of P2) etc...

I want to sort the group (group1) by formula1- I want the item that sold the most on top of the report...  I've looked at Top n/sort but that seems to want a summary instead of a formula.  Is this possible to do? Let me know if I wasn't clear in my description.  Thanks for any help..
LVL 1
jcneil4Asked:
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.

sturquetteCommented:
You could create a parent group to group one on the "Formula" and sort that group in descending order
0
mlmccCommented:
That might work.

How are the totals calculated?

mlmcc
0
jcneil4Author Commented:
the details section contains the qyt.sold.period1...12 fields and is hidden.  Those fields are summed in group2 footer to show total for each period for each color.  

item              Jan         Feb         Mar
     <group2 footer>
101 blk           2            50           25
101 red          11          10            5
101 blu           2            5            10
     <group1 footer>
101 totals       15          65           40..................................................... (formula1)= 120        
         

Group1 footer contains total for all colors in that period for that item.  These are the totals that I am adding together the see overall qty sold for item ytd (formula1).  I want th sort by formula1 so hottest selling items are on top.  Details, group1 header, and group2 header are all hidden..  Does that make sense?

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jcneil4Author Commented:
I'm not sure how to create a parent group to group one on the "Formula".
0
sturquetteCommented:
what version of Crystal are you using.
can you send the report
0
jcneil4Author Commented:
Sorry Crystal Reports 8.5- Modified for Best Mas200.  How would I send it?
0
sturquetteCommented:
Do you have control over the datasource of your report?
0
jcneil4Author Commented:
I think so... how would I know that?
0
sturquetteCommented:
Well when I write Crystal Reports, I first create a SQL Server 2000 stored proc.  When I have the stored Procedure working like I want it to, I create a Crystal Report with that stored procedure as my data source.  If I need different data or to change the data some how, I can just change the stored procedure.  If the modifications are not too drastic, I do not even have to modify the Crystal Report, it just shows up with the changes in the data.

If you have control over your data source, you could change its presentation to Crystal by having it give you the information in the order you need.  You can then turn off the sorting in the Crystal Groups and use the Datasource Provider to do that work for you.  A benefit of this is that it is typically faster for large reports.

Steve T
0

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
jcneil4Author Commented:
Our database is not SQL server and I'm pretty sure I have no such control.  No way that I can do this within the report?
0
sturquetteCommented:
is this a crosstab report
or just a regular report
0
sturquetteCommented:
Ok
I found a feature that is available in Crystal 9, but I do not know if it it available in Crystal 8.5

In the Menu Under Report
there is a Group Sort Expert

If you have that functionality, then you should be able to do what you need to do
0
jcneil4Author Commented:
Just a regular ODBC report.
Nope- don't have that option.  Thanks anyway.  I'll keep hacking away but I think I'm screwed..
0
sturquetteCommented:
if it is ODBC you might have access to the SQL statement that returns the information.

In the Database menu you should have an option to Show SQL Query
0
jcneil4Author Commented:
I solved this by using an SQL Expression Field instead of a formula.  I then summed the SQL Expression Field and was able to use the Top n/sort feature and sort by the sum of SQL Expression Field.  It wouldn't let me use that feature on the formula...  I will award the points to sturquette for trying as I know this was probably very hard to understand my description and he tried to help.  Also, he turned me on to studying stored procedures which helped me stumble onto my solution.
0
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
Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.