Home / Tech Tip / Crystal Tech Tip / Crystal Reports and Arrays

Crystal Reports and Arrays

Question:
I need to combine a list of details, or group information, and print it in the footer. How can I accomplish this?

Answer:
This Tech Tip will show how you can do this using Dynamic Arrays within Crystal Reports formulas.

There are two types of arrays in Crystal Reports – Static and Dynamic. Single dimension arrays are supported with a maximum size limit of 1000 elements.

To use an array, you need to first declare the array then assign the values. Finally, you reference elements.

Arrays have the same scope as, and data types, as other variables. They consist of the:

• Array Name
• Assignment Operator
• List Of Values

Scope Datatype Array Name:= [value1, value2, value2, etc…];

An array value is referenced with a subscript
Name[x]

Change value in array
Name[x] := newValue;

In the following example, a customer has a list of different products. There is a need to print the entire list on one line. This can be accomplished using a Dynamic Array. An array is loaded and the list of customer products are printed in the customer group footer.

Three formulas are used for this example:

1. Initialize Array
2. Load Array
3. Print Array

 

1. Initialize Array is placed in the “Customer” group header.

Name – Initialize Array
Shared stringVar array productType := [“”];
Shared numberVar x := 1;

2. Load Array is placed in the “Product Type” group header.

Name – Load Array
Shared numberVar x;
Shared stringVar array productType;

productType [x] := {Product_Type.Product Type Name}; //Load array with product type
Redim preserve productType[UBound(productType)+1];//more space added to array.
x := x+1;

3. Print Array is placed in the “Customer” group footer.

Name – Print Array
Shared stringVar array productType;

Join (productType, “, “) // Use Join function to print array on one line

Now, the list of products can be printed on one line.

About Y Grill

Yolande Grill is the COO at InfoSol Inc. "I like to think of myself as the Chief Transformation Officer. My favorite quote to ponder is one from Trina Paulus: 'How Exactly does one become a butterfly? You must want to fly so badly, that you are willing to give up being a catterpillar'. I love feeling inspired."

Check Also

How to Skip Dataflows for Testing or Restarting a Job for Recovery

One of the greatest features of SAP Data Services is the ability to divert dataflows/workflows …

11 comments

  1. Do we have same solution with Crystal Report – Basic Syntax please?

  2. Here is what I did to fix the trailing “,” at the end.

    I first selected a character to pad on to the end of the array; I chose ! in my example, below:
    Join(productType, “, “)+”!”

    Then I used the replace function to remove the combination of “, !”, which would only be at the end of the line. That makes the fill Print Array formula:


    Shared stringVar array productType;
    replace(Join(productType, ", ")+"!",", !", "");

  3. IF, In this case “Shared stringVar array” changed to “Shared dateVar array”. How to join in this case?

  4. This solution worked a treat for me. Thanks for posting

  5. I am trying to match a field on a input record to validate it against a array of about 500 elements. Ex. pay code on the input record looked up against an array to see if it is valid or not. How/where do I create create a array of valid pay codes for the program and how/where do I code this comparison logic.
    Thanks, steve

  6. This was a great help! Thank you.

  7. How would you do this to pass an array of numbers to a subreport? I have only found solutions for string variables but nothing for numbers!

  8. I have a crystal report to print previous page totals on last page as a summary page.

    Ex:

    Page 1
    EmpNo Gross_Sal Tot_Ded Net_Sal
    ———- ————- ———— ———-
    990 2500 500 2000
    995 2750 250 2500
    ————– ———– ———-
    5250 750 4500

    on the last page

    Page No Tot_Gross Tot_Ded Tot_Net
    ———– ————– ———— ————
    1 5250 750 4500
    2 999 99 999
    —————————————————–
    Grand xxxxxx xxxx xxxx
    Total
    =================

  9. Hi,
    I am trying to implement your solution above to what I need but I’m running into problems.
    Basically, I have a few tables linked and want to return a UTI field from the Remark table in a confirmation report. It works fine when I have only a single remark for that trade but not if there are multiple remarks. I need some way of looping through the items in the remark table to return ONLY the one with Remark class = UTI.
    My way of attempting to do this (I’m sure this is the very long winded way) was to assign all the items from the remark.remarkclass and remark.remarktext into 2 arrays and then loop through so as to pick out the one where remark.remarkclass = UTI. I’m sure there is an easier solution to this but my crystal experience is limited.
    I have the following 3 formulas but getting an error with subscript is out range on x:
    Initialise:
    Shared stringVar array remarkText := [“”];
    Shared numberVar x := 1;
    Shared numberVar y := 1;

    load:
    Shared numberVar x;
    Shared numberVar y;
    Shared stringVar array remarkText;
    Shared stringVar array remarkClass;

    remarkText [x] := {REMARK.REMARKTEXT}; //Load array with remarktext
    remarkClass [y] := {REMARK.REMARKCLASS}; //Load array with remarkclass
    Redim preserve remarkText[UBound(remarkText)+1];//more space added to array.
    Redim preserve remarkClass[UBound(remarkClass)+1];//more space added to array.
    x := x+1;
    y := y+1;

    print:
    WhilePrintingRecords;

    //shared numbervar x;
    local numbervar j;
    local stringvar UTIText;

    Shared stringVar array remarkText;
    Shared stringVar array remarkClass;

    for j:=1 to UBound(remarkClass) step 1 Do
    IF remarkClass[j] = “UTI” Then
    UTIText :=remarkText[j]
    Else UTIText := “”;
    UTIText;

  10. ps Any help would be greatly appreciated. Thanks

  11. Hi Ger,

    I have forwarded your question to one of our consultants.

    Thanks!

    -John

Leave a Reply

Your email address will not be published. Required fields are marked *