Extracting and Grouping by Country Code

[fuzzyocelot]

I'm using SQL Server 2000 and Crystal Reports 9.

I'm writing a report that references a 'Faxes' table. I need to display the total Elapsed Time for each country code the faxes are being sent to. The only way to get the country code is to extract it from the outbound fax number. Any fax number that is a U.S. number can be lumped into one group. So basically, I need to group on the extracted country code.

The 'ElapsedTime' and 'origFax' fields are in a 'Faxes' table. The 'newFaxNum' is what I am using in the report and is NOT in the table. I just listed it below as a reference.
[tt]
ElapsedTime origFax newFaxNum
10.00 209-1234 2091234
15.00 2091234 2091234
2.00 (209) 1237 2091237
1.00 (209)-1237 2091237
5.00 1(555)209-1239 15552091239
10.00 9-1-209-1240 912091240
2.00 1.209.1241 12091241
15.00 +01135129993241 01135129993241
1.00 001135129993242 001135129993242
5.00 901135129993243 901135129993243
2.00 901132629993243 901132629993243
[/tt]
To strip the punctuation from the 'origFax' field, I ended up writing a SQL expression (see below). It also returns only the first 7 digits from the 'origFax' field. The country code is the 2 or 3 numbers after '011', for international numbers. If it's a U.S. number, I just need to lump them together under one group.

Note: I grabbed the first 7 digits so it would group them together and return less data from the database to the report. Using this logic, I get about 5000 records. Without it, I get over 80,000 records.

I put the 'origFax' and 'ElapsedTime' fields in the details section and suppressed the section. I also inserted a subtotal via a summary field on the 'ElapsedTime' and put that in the group footer.

SQL Expression:[tt]
%stripPunctuation:
{fn left({fn REPLACE({fn REPLACE({fn REPLACE({fn REPLACE({fn REPLACE({fn REPLACE("Faxes"."origFax", '.', '')}, '+', '')},
' ', '')}, ')', '')}, '(', '')}, '-', '')},7)}[/tt]

So, the field values from above should now be:
[tt]
ElapsedTime origFax newFaxNum
10.00 209-1234 2091234
15.00 2091234 2091234
2.00 (209) 1237 2091237
1.00 (209)-1237 2091237
5.00 1(555)209-1239 1555209
10.00 9-1-209-1240 9120912
2.00 1.209.1241 1209124
15.00 +01135121213241 0113512
1.00 001135121213242 0113512
5.00 901135121213243 9011351
2.00 901132621213243 9011326
[/tt]
The report groups on the 'newFaxNum', so the groups are:
[tt]
2091234 25.00
2091237 3.00
1555209 5.00
9120912 10.00
1209124 2.00
0113512 16.00
9011351 5.00
9011326 2.00
[/tt]
I have a formula that evaluates the group to determine if it's a U.S. number or international. If it's a U.S. number, return a '1'. Otherwise, return the appropriate 3 digits after '9011', '0011', or '011'.
[tt]
Formulas:
@getCCode: (currently in the group footer)
stringVar strLeft4 := left({%stripPunctuation},4);
stringVar strLeft3 := left({%stripPunctuation},3);
stringVar strRight3 := right({%stripPunctuation},3);

if strLeft4 = '9011' or strLeft4 = '0011' or strLeft3 = '011' then strRight3
else '1'
[/tt]
In the report header, I have a formula that populates an array with the valid country codes. Some are 2 digits and some are 3 digits. I have only 1 array containing the 2 digit codes. If the extracted digits are not in this array, then I assume it's a 3 digit code.
[tt]
@BuildArray: (in the report header)
whileprintingrecords;
stringVar array strArr2Digit := ['20','27','31','32','33','34','36','39','40','41','43','44']; //this is just a small set of the actual codes.
numberVar numCounter := 0;

@checkArrays: (currently in the group footer)
EvaluateAfter ({@getCCode});

stringVar array strArr2Digit;
stringVar strCCode := {@getCCode};
stringVar str2Digit := left(strCCode,2);

if strCCode <> '1'
then (
if str2Digit in strArr2Digit then str2Digit
else strCCode;
)
else '1';
[/tt]
So, now the groups are as follows:
[tt]
1 25.00
1 3.00
1 5.00
1 10.00
1 2.00
351 16.00
351 5.00
32 2.00
[/tt]
But, I want it to look like this:
[tt]
1 45.00
351 21.00
32 2.00
[/tt]
How do I get it to do that? Any ideas? This is a very confusing and messy process so I hope I explained it clear enough.

As a last resort, I may end up writing a stored procedure.

Thanks for any help or advice! It's greatly appreciated.

 

[fuzzyocelot]

One thing I didn't stress in my main post is that I need to have the database return as few records as possible. The number of records must be less than 20,000 records. The reason is that all of our Crystal Enterprise machines have the max record setting at 20,000. So, it will time out if the report has more than 20,000 records. In order to get this setting changed, we would have to go through a long process of filling out forms and getting approval. It's not easy to do, plus the number of reports that contain this amount of data is relatively small.

Thanks for any advice or suggestions. I really do appreciate it.

 

[fuzzyocelot]

Sorry, I found an error in my original post.

The @getCCode should be as follows:
[tt]
stringVar strLeft4 := left({%stripHyphens},4);
stringVar strLeft3 := left({%stripHyphens},3);
stringVar strMid4_3 := mid({%stripHyphens},4,3);
stringVar strMid5_3 := mid({%stripHyphens},5,3);

if strLeft4 = '9011' or strLeft4 = '0011' then strMid5_3
else if strLeft3 = '011' then strMid4_3
else '1';
[/tt]

Thanks

 

[fuzzyocelot]

Well, I've made some progress.

I combined the two formulas @getCCode and @checkArrays into one formula @testGroup. I then grouped on this formula instead.

[tt]
@testGroup:
stringVar strLeft4 := left({%stripHyphens},4);
stringVar strLeft3 := left({%stripHyphens},3);
stringVar strMid4_3 := mid({%stripHyphens},4,3);
stringVar strMid5_3 := mid({%stripHyphens},5,3);

stringVar strCode;

if strLeft4 = '9011' or strLeft4 = '0011' then strCode := strMid5_3
else if strLeft3 = '011' then strCode := strMid4_3
else strCode := '1';

stringVar array strArr2Digit;
stringVar str2Digit := left(strCode,2);

if strCode <> '1'
then (
if str2Digit in strArr2Digit then str2Digit
else strCode;
)
else '1';
[/tt]

Now my results look like this:
[tt]
1 45.00
32 2.00
351 21.00
[/tt]

The only problem left is that the database returns over 25,000 records. Don't get me wrong; I'm happy that it's at least working and the database is returning less than 80,000 records. But, unfortunately, it is not enough. Any ideas as to how I can get the database to return even less records?

Thanks

 

[fuzzyocelot]

*sigh* ... I was too hasty in my last post. When I refreshed the report, it indicated that the database was returning over 80,000 records and not the 25,000 I thought it was. So I'm back at square one. :-(