Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Group Sort based on formula Post Reply Post New Topic
Author Message
Chadzuk
Newbie
Newbie


Joined: 20 Oct 2010
Location: United States
Online Status: Offline
Posts: 3
Quote Chadzuk Replybullet Topic: Group Sort based on formula
     Posted: 17 Mar 2011 at 10:51am
Hello folks,
 
I have created a crystal report that pulls from a SQL stored procedure.   The basis of the report allows them to do a count of of patients by location(zip code). 
 
The report prompts the user for 5 different group parameter fields I created (Group1, Group2, etc) allowing them to control how the report groups the information.   They also have the ability to use a group of "None" which will then blank that group out entirely when the report is runs.    The report is set to show the total count of patients by zip code based on the last Group parameter field they selected an actual field to group by.   Whatever the last group by field is that isn't set to "None", the report automatically assigns one additional group by field called "Zip Code" above it and this is where it shows the totals by zip code.
 
Here's the issue I'm stumped on.   I want it to have it show the zip codes in order from highest count to lowest count.   I know I can do this by using the "Group Sort Expert" but it makes me choose the group I want to do this on which could change each time the user runs the report and changes the way they want it grouped.   
 
For example, the first time the user runs the report they could choose the following groups:
 
Group1: Year
Group2: Month
Group3: Type
 
The report will then automatically add the zip code group to Group4 on the report suppressing the rest of the groups.  In this instance I would go into "Group Sort Expert" and select "Group4" as where to sort "All" by the count.    However, the next time they run the report they may just want:
 
Group1: Year
Group2: Month
 
In this scenario the zip code group now falls into "Group3" and my Group Sort Expert" doesn't work.   If I tell it to Sort "All" on every group it start rearranges my other groups which I want to show in order.  
 
So in essance, I want to try to throw some type of formula in that says
 
If Group1 = "Zip Code" then Group Sort "All" by count ELSE
If Group2 = "Zip Code" then Group Sort "All" by count,
.
.
.
 
There doesn't seem to be a way to create formulas for the group sort expert and I was wondering if anyone found a way around this.
 
Sorry if the post was long winded.  It's a very complex report and I wanted to try to make sure my dilemma was understood.
 
Thank you in advance,
Chad
 
 
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 18 Mar 2011 at 2:48am

Here is what I would do...or at least a start.  Group 6 will always be zip code, set as that and don't worry about the rest.  If they only select 3 groups, set the groups 4 and 5 to the same value as group 3.  If there is subtotalling for each group, you could set in your stored proc a flag to hide groups 4 and 5.

I work with a reporting tool that is not nearly as CR when it comes to dynamic grouping, so my solution has been to create in the stored proc, the columns that I am grouping on...I call them (original, I know) Gp1, Gp2, etc.  Part of the stored proc, looks at the various parameters and then populates the Gpx columns with the appropriate information. You could use the same logic to set a flag as to whether the section is visible or not and then use the suppress function in section expert to read the flag and dynamically suppress the section, and it will always sort correctly by zip code as that is in a non dynamic section that is set to behave the way you want.
 
HTH
IP IP Logged
Chadzuk
Newbie
Newbie


Joined: 20 Oct 2010
Location: United States
Online Status: Offline
Posts: 3
Quote Chadzuk Replybullet Posted: 22 Mar 2011 at 5:53am
Thank you lockwelle, sorry it took me a bit to get back to you.    This sounds like it should work like a charm.  I just haven't had a chance to get back and make the alterations to it.   I appreciate you taking the time to help out.
IP IP Logged
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