asked on

How to populate an array and compare the data

Hi all,

Im wondering how/where (withing the custom code?) im able to fill an array with data. What im doing is retrieving postal codes from clients and stripping the first 3 characters and trying to put those values into an array. With this array i will then compare each value against a string value (will be a list of all Alberta's postal codes) and keep a count so i can then display the number of clients from that certain area of Alberta.

eg. The array will contain thousands of postal codes (T2W, T0E, T0L, etc...) and then compare it to a string (such as T2W) and i would like it to return the total number within the array that are T2W.

Im just trying to figure out how im able to create the array within SSRS and populate it, then pass the array to another method or procedure that does the string comparison and returns a count value.

This may not be explained very well but any help is greatly appreciated.

Thanks!

timberbt

If you're really just looking to get a list or a count, why not do it all in SQL? Could you provide a sample table structure that you're pulling from? Should be a pretty trivial query after that if I understand correctly.

timberbt

Something like this maybe....

DECLARE @SearchString VARCHAR(3)
SELECT @SearchString='T2W'
 
DECLARE @PostalCodes TABLE
(
	Code VARCHAR(6)
	)
	
INSERT @PostalCodes VALUES ('T0E123')
INSERT @PostalCodes VALUES ('T0E124')
INSERT @PostalCodes VALUES ('T2W123')
INSERT @PostalCodes VALUES ('T0L123')
INSERT @PostalCodes VALUES ('T0E125')
INSERT @PostalCodes VALUES ('T2Wzzz')
INSERT @PostalCodes VALUES ('T0L456')
 
--List matching codes
SELECT * FROM @PostalCodes
WHERE
	LEFT(Code,3) = @SearchString
 
--Get a count of matching codes
SELECT COUNT(1) AS [MatchingCount] FROM @PostalCodes
WHERE
	LEFT(Code,3) = @SearchString

Open in new window

agilebiz

ASKER

My search string will be every postal code in alberta. Basically the company wants to see how many clients came from each area of Alberta. There are 150 differnt postal codes in Alberta so ill need to iterate through each and spit out the number of clients per area in alberta. Attached is my select statement.

Basically what they got me doing is retreiving the number of clients who contacted this company via certain referral methods (ie. Yellow pages, Friend, etc) In my report i have it displaying the different types of referral sources as well as the different postal codes of the clients who contacted them (with out duplicates). Where im having troubles is displaying the total number of referrals per postal location.

What im looking for:

Referral Source Location Total Referrals
Yellow Pages T2W 5
L0E 2
etc..
Friends T7E 1
etc..
etc..

SELECT     dbo.tlkpReferralSource.refPK, dbo.tlkpReferralSource.refDescription, dbo.tblBankruptsAddress.addPostalCode, dbo.tblBankrupts.banBankruptPK
FROM         dbo.tblBankrupts INNER JOIN
                      dbo.tlkpReferralSource ON dbo.tblBankrupts.banReferralSource = dbo.tlkpReferralSource.refDescription INNER JOIN
                      dbo.tblBankruptsDates ON dbo.tblBankrupts.banBankruptPK = dbo.tblBankruptsDates.datBankruptPK INNER JOIN
                      dbo.tblBankruptsAddress ON dbo.tblBankrupts.banBankruptPK = dbo.tblBankruptsAddress.addBankruptPK
WHERE     (dbo.tblBankruptsDates.datInsolvency >= @startDate) AND (dbo.tblBankruptsDates.datInsolvency <= @endDate)
GROUP BY dbo.tlkpReferralSource.refPK, dbo.tlkpReferralSource.refDescription, dbo.tblBankruptsAddress.addPostalCode, 
                      dbo.tblBankrupts.banBankruptPK
ORDER BY dbo.tlkpReferralSource.refPK

Open in new window

agilebiz

ASKER

Excuse my inability to really explain what im asking for, i have very limited knowledge in SSRS.

ASKER CERTIFIED SOLUTION

timberbt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

agilebiz

ASKER

I will give this a shot and get back to you. Thanks

timberbt

No problem. It looks like you'll have to work a couple tables into that join, but doing the subselect with the distinct filter on the first 3 character of the postal code sounds like what you need. Enjoy!

timberbt

All done and working?

agilebiz

ASKER

Sorry timberbt, we have decided to go with a more graphical representation of the data using mappoint, although this was helpful, Thank you.