One Sort On Multiple Fields

[steventr]

I have 1 to 3 unique SSNs per record.

I wish to create a single list of all SSNs in sorted order.

Tried to use a subreport, but couldn't get that to work.

Record SSN 1 2 3
1 123 12 1234 223 12 1234 323 12 1234
2 233 12 1234 023 12 1234 000 12 1234


I would like to see a listing of: (I am assuming I did sort it correctly)
000 12 1234
023 12 1234
123 12 1234
223 12 1234
233 12 1234
323 12 1234

 

[synapsevampire]

If you have lots of rows, you won't be able to sort on all 3 SSNs and display them very easily.

You might use an array to store them, but if you're using CR 8.5 or less, you're limited to only 254 characters output by a formula, so this might get messy.

You could create a View using:

SELECT *
FROM
(SELECT SSN1 as SNN, 'SSN1' as SSNTYPE
FROM dbo.AtsPerss
UNION ALL
SELECT SSN2 as SSN, 'SSN2' as SSNTYPE
FROM dbo.AtsPerss
UNION ALL
SELECT SSN3 as SSN, 'SSN3' as SSNTYPE
FROM dbo.AtsPerss
) DERIVEDTBL

Now you have a single table with just the SSN's, I added in a SSNTYPE field in case you need it. You can add in other columns as you see fit.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[steventr]

Thank you.

Your solution looks very good and is similar to the SQL union we used prior to Crystal.

steven