Group with numbers, how?

[DeafBug]

I have a few reports that has been left alone for quite some time. With a new report I created for our application, I can't have it ignored. So this report and the others need to be fixed.

I spent some time on it and I have no idea how. I did learn a few things while I was playing around. You can only group with a string value. You can sort with whatever data type. So that is the problem here. I am using CR10 with VB6. VB passed a lot of parameters to CR so the report shapes to clients expectations.

One of the parameters is a GroupBy parameter as well as a SortBy parameter. Client can group by Store Number. The data type for Store Number is Number, not a String. I used the TOTEXT to make the grouping work. It is fine for the first 9 stores. The tenth and so on store is not correct.

I use a formula to handle GroupBy and SortBy to have it the report display what the client selected.

I tried to use TONUMBER and there is an error as you can only group by string. I tried to use TOTEXT() to fill with "0000", it works but we can't have padded zeros. The store number can be up to 4 digits long and it is not necessary padded.

So instead of grouping by store number to have it like this.

1
2
3
11
22
33

It is grouping like this.

1
11
2
22
3
33

This works
0001
0002
0003
0011
0022
0033
But no padding is allowed. So I am back to square one.

Is there a trick to solve this?

 

[DeafBug]

Oh sorry I forgot to mention this. Even though I use the formula to handle the GroupBy and SortBy. All the other options are correct as they are what they are to group/sort by. But this is the only one that is not working.

 

[lbass]

First, you can group by a number. I'm guessing your parameter has a series of options with mixed datatypes, and so the options were all made strings.

One solution is to use "0000" totext formatting, but display the actual store number. You can conditionally suppress the groupname by using a field suppression formula like:

{?GroupBy} = "StoreNo"

Add the actual store number to the groupheader on top of the existing groupname and also conditionally suppress this using:

{?GroupBy} <> "StoreNo"

-LB

 

[synapsevampire]

As LB alludes to, group by a formula such as:

totext({table.field},"00000000")

But display the actual field in the table.

-k

 

[DeafBug]

Here is the formula. All works except the first one. I have that ToText there so there is no comma for the thousand separator as the store number can be four digits long.

GroupField=7 is for no grouping. And if they select that then I suppress the group field.


IF {?GroupField}=1 THEN
TOTEXT({AutomatedPartsGrading.CategorizingStoreNumber},0,"")
ELSE IF {?GroupField}=2 THEN
Left ({AutomatedPartsGrading.InventoryNumber},3 )
ELSE IF {?GroupField}=3 THEN
{AutomatedPartsGrading.CategoryCode}
ELSE IF {?GroupField}=4 THEN
{AutomatedPartsGrading.Classification}
ELSE IF {?GroupField}=5 THEN
{AutomatedPartsGrading.EmployeeInitials}
ELSE IF {?GroupField}=6 THEN
TOTEXT({AutomatedPartsGrading.PartRating})
else if {?GroupField}=7 then

 

[lbass]

Use:

IF {?GroupField}=1 THEN
TOTEXT({AutomatedPartsGrading.CategorizingStoreNumber},"0000") else//etc.

This will format the number without a comma also. Then use the method I suggested earlier for display of the original store number field.

-LB

 

[DeafBug]

Thank you.

I did what you said to have CR group by the formula GroupBy. I created a new formula called GroupByText to display the grouping in proper format.

All works well.