grouping case sensitive?

[mehlerak]

I am grouping the details by a field called tax_code. All the tax codes are 2 letters (alphanumeric) and uppercase, except for one. There are two groups for New York: NY and ny. I am grouping (and sorting) by this code. I have a parameter for the tax code, where the user can enter a (string) range, for instance L1-L9 and it returns all codes that begin with L and have a number. My problem is with the NY/ny groups. I would like an instance of the report to show all NY/ny, grouped and totalled. I asked for all codes from NY to ny (and also the opposite), and I get weird results. The grouping (on the left of the preview window) shows only one group (NY), but the data is for both groups, and the group footer is the footer for ny, but the totals are for both groups.
If I leave the range selection blank (to give me all groups), I get separate results for NY and ny.

CR 9 Pro.

selection code:
(if ({?tax code}='') then true //shows all if blank
else {TAX_TABLE.TAX_CODE}>= {?tax code} and {TAX_TABLE.TAX_CODE}<= {?tax code})

 

[lbass]

Try:

if {?tax code} <> '' then
(
ucase({TAX_TABLE.TAX_CODE}) >= {?tax code} and
ucase({TAX_TABLE.TAX_CODE}) <= {?tax code}
) else
true

Then also convert the field that you are grouping on to uppercase, e.g., ucase({TAX_TABLE.TAX_CODE}), for a consistent display.

Are you either prepopulating the parameter options list or setting up the parameter entry screen with an edit mask to make sure users only enter the parameters in upper case?

-LB

 

[mehlerak]

this doesn't help... these are two distinct codes and I need to keep them separate. still same results. I need to have two separate groups for NY and ny, but for some reason, this is not what is happening. CR is putting the two together.

thanks

 

[lbass]

Okay, I thought you were saying the opposite. I cannot recreate the problem with your record selection formula, as it selects the correct records when I created a dummy report.

However, to get the NY and ny separate, go to file->report options-> and uncheck "Case insensitive SQL data". This will separate them into two groups when you group on the tax code.

-LB

 

[mehlerak]

thanks. When I refresh the report and enter range from NY to ny, I get a message saying minimum cannot be larger than maximum (which suprised me, but I guess it is not standard ASCII sorting), and when I entered ny to NY, nothing came up, when there is data that should show up.

Any ideas?

 

[lbass]

This is weird. Before unchecking case insensitive data, "NY" came before "ny" in a sort, but after unchecking this, "ny" appears before "NY". When I choose "ny" to "NY" in the range selection, I get both groups, but if I choose "NY" to "ny" I get the error message about the minimum not being greater than the maximum. This would be okay, except the following displays:

1a
1A
a1
A1
aa
AA
L2
N2
ny
NY

In other words, the order appears to be:

1-number first before lower case letter
2-number first before upper case letter
3-lower case letter plus number
4-upper case letter plus number
5-lower case letters
6-upper case letters

-LB

 

[NRobertson]

Does your selection need to be a range?
Could you potential change it to allow multiple values instead?

-NRobertson

 

[mehlerak]

It would probably have to be a range, since there will be potentially many entries needed.