Skip to Content
author's profile photo Former Member
0
Former Member

Creating an array form a table in Crystal Reports

Posted on Oct 08, 2010 at 10:38 AM 101 Views

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • author's profile photo Don Williams
    Don Williams
    Posted on Oct 08, 2010 at 03:43 PM
    0

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member
      Oct 08, 2010 at 09:39 PM

      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

  • author's profile photo Former Member
    Former Member
    Posted on Oct 12, 2010 at 06:57 AM
    0

    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.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.