Creating an array form a table in Crystal Reports

[Former Member]

Hi,

I have been trying to create an array in Crystal Reports (XI).

I have a single table with a column containing codes.

All I want to do is create an array in Crystal to hold the codes contained in the table. There are approx 2000.

Can anyone tell me the code?

Best Regards

rpecan

[Former Member]

Thank you both, Jason & Don. I will give this a go later this week when I'm back inthe office, it looks logal to me.

Thanks once again

Roger.

[former_member11696]

Hello,

That's a large array and will affect performance. What is it you are trying to do? There should be an easier way to accomplish your goal than using an array. SQL in a Command Object is my first pick...

Thank you

Don

[Former Member]

To further Don's point... I'm also thinking that there's a maximum of 1000 values for a single array in CR. Don can correct me if I'm wrong.

But if you want to give Don's idea a try and do it in a SQL Command, it shouldn't be too tough. Something like this should do it for you...

DECLARE @c VarChar(100), @a VarChar(MAX)
SET @c = (SELECT TOP 1 CodeField FROM TableName ORDER BY CodeField)
SET @a = '''' + @c + ''''

WHILE @c IS NOT NULL
BEGIN 
SET @c = (SELECT TOP 1 CodeField FROM TableName WHERE CodeField > @c ORDER BY CodeField)
IF @c IS NOT NULL
SET @a = @a + ', ''' + (SELECT TOP 1 CodeField FROM TableName WHERE CodeField = @c) + ''''
END

SELECT @a AS Array

FYI: If the CodeField is a numeric data type you may need convert it to a string type field where the @a is being SET so that it will concatenate properly.

HTH,

Jason

Edited by: Jason Long on Oct 8, 2010 4:40 PM