Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 426
  • Last Modified:

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..
0
jcneil4
Asked:
jcneil4
  • 7
  • 7
1 Solution
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now