Setting the sort order of a group at run time 3

[JCruz063]

Hi All,
I have a report that has a group based on a formula. At design time, I can set the sort order of the group to Ascending, Descending, or Specified Order. If I select "Specified Order", I have unmatched flexibility as to how I want the records ordered. I could make them display in any way I want.

Is it possible to set the ordering of a group to "specified order" at run time? I was playing around with the report doing stuff like:

rpt.DataDefinition.Groups[x].GroupOptions.???

but I was getting nowhere with it. I have the version of Crystal Reports that shipped with Visual Studio 2002. Can anybody please tell me how I can accomplish this?

Thanks!

JC

We don't see things as they are; we see them as we are. - Anais Nin

 

[synapsevampire]

I think that's a toy version of Crystal and may not have all of the functionality of a real developer's version.

Anyway, using the RDC in CR 9 there is the specifiedgroups property, you might try that.

-k

 

[JCruz063]

-k,
How dare you insult my copy of CR like that - calling it a toy version? (just kidding!). Well, if what you're telling me is that my version doesn't do the job and others do, I guess I'll have to look for an upgrade. Now, I'm not very familiar with Crystal Reports, so can you give me an overview of the versions and how they're used with VS .NET? Also, before I upgrade to a newer version, I need to be completely sure that I can set the "Specified Order" at run time, otherwise I'm not sure if I'll be purchasing the upgrade.

Thanks!

JC

We don't see things as they are; we see them as we are. - Anais Nin

 

[lbass]

Here is a method (within CR) for setting the sort order of groups at run time using CR 8.0:

First, for groups based on a numerical field:

Create two parameters:
{?Sort} - a discrete string parameter, with defaults of:
"Asc","Desc","Spec"

{?Spec Order} - a discrete, multiple value, number parameter with defaults that include all the group values plus 0. The zero should be placed in the topmost position so that it is always the default if the parameter is not selected.

For this example, let's assume that there are 5 group values and that they are based on {Orders.Customer ID}.

Then create a formula {@Sort}:

if {?Sort} = "Asc" then {Orders.Customer ID} else
if {?Sort} = "Desc" then -{Orders.Customer ID} else
if {?Sort} = "Spec" then
(
if isnull({Orders.Customer ID}) then 0 else
if {Orders.Customer ID} = {?Spec Order}[1] then -5 else
if {Orders.Customer ID} = {?Spec Order}[2] then -4 else
if {Orders.Customer ID} = {?Spec Order}[3] then -3 else
if {Orders.Customer ID} = {?Spec Order}[4] then -2 else
if {Orders.Customer ID} = {?Spec Order}[5] then -1 else 0)

For specified order, the subscript would increase to the total number of groups, while the result would start with minus the total number of groups.

Insert a group on {@Sort} and check "Customize Group Name" and "Use a formula as the group name" and then enter:

totext({Orders.Customer ID},0,"") //this displays the customer ID
//instead of the specified order results if {?Spec} = "Spec"
//and without showing the minus sign if the {?Spec} = "Desc"

The user can now answer the {?Sort} prompt with "Asc" and "Desc" and ignore the {?Spec Order} or choose {?Spec} and then choose {?Spec Order} and enter the groups in the order he/she wants the groups displayed, e.g., 4,3,5,1,2. However, the user must enter all non-zero numerical options or the report will fail, so the prompt message should say "Enter ALL values in the order you would like to see them displayed" or something like that.

I would also add formulas to the page header to show the selected parameters:

//{@DisplaySortOrder}:
"Sort Order: " + {?Sort}

//{@DisplaySpecOrder}:
NumberVar i;
NumberVar counter := UBound({?Spec Order});
StringVar Display := "Specified Order: ";

For i := 1 to counter do (
Display := Display & (if totext({?Spec Order},0,"") <> "0" then
totext({?Spec Order},0,"") +", "));
left(Display,len(Display)-2);

Conditionally suppress the latter formula (format->field->common->suppress->x+2) with:

{?Sort} <> "Spec"

Alternatively, for groups that are based on string fields, change {?Spec Order} to a multiple value string parameter, with a list of group (string) values for the defaults. For this example, I created a formula {@ProdInit} that resulted in the first letter of a product name field, but these could be complete words, entered in the following formula {@Sort} in alphabetical order:

if {?Sort} = "Asc" then {@ProdInit} else
if {?Sort} = "Desc" then
(
if {@ProdInit} = "A" then "Z" else
if {@ProdInit} = "B" then "Y" else
if {@ProdInit} = "C" then "X" else
|
| //fill in
V
if {@ProdInit} = "X" then "C" else
if {@ProdInit} = "Y" then "B" else
if {@ProdInit} = "Z" then "A" //if there are more than 26 group
//instances, you could use Z2,Z1,Y2,Y1, etc.
)
else
if {?Sort} = "Spec" then
(
if isnull({@ProdInit}) then "" else
if {@ProdInit} = {?Spec Order}[1] then "A" else
if {@ProdInit} = {?Spec Order}[2] then "B" else
if {@ProdInit} = {?Spec Order}[3] then "C" else
if {@ProdInit} = {?Spec Order}[4] then "D" else
if {@ProdInit} = {?Spec Order}[5] then "E" else //etc.
""
)

Again, you would group on this formula, customizing the group name to use the formula {@ProdInit}. The display formula would change to:

NumberVar i:= 1;
NumberVar counter := UBound({?Spec Order});
StringVar Display := "Specified Order: ";

For i := 1 to counter do (
Display := Display & (if {?Spec Order} <> "" then
{?Spec Order} +", " ));
left(Display, len(Display)-2);

You could decide to limit the number of groups just for the specified order option, if you wanted to, by adding a record selection formula of:

{?Sort} in ["Asc","Desc"] or
{@ProdInit} = {?Spec Order} //substitute {Orders.Customer ID} for
//the numerical example

-LB

 

[JCruz063]

-LB,
You're the man (or the woman - I don't know your sex). Thanks a million. I really needed this formula. Thanks!

JC

We don't see things as they are; we see them as we are. - Anais Nin

 

[JCruz063]

You know LB, I tried giving you 5 stars but the system allows only one per each member. Just pretend I gave 5.

Thanks...

JC

We don't see things as they are; we see them as we are. - Anais Nin