Skip to Content

Global Variable Array in Record Selection Formula

I was searching for a way to display the description of a parameter.  The only solution I found, that I could understand, was in this thread. Based on that thread I did the following:

  1. Created a parameter that has a list of 35 values.  Examples of those values:
    • 0-ALL
    • 100026-CCH5
    • 100025-CCH6
  2. Created two formulas:
    • Formula 1 - Placed on the Report Header; works very well.
      1. BeforeReadingRecords;
      2. numberVar counter;
      3. stringVar display;
      4. for counter := 1 to Count({?Unit}) do
      5. (
      6. if {?Unit}[counter] = '0-ALL' then
      7. display := 'ALL'
      8. else
      9. if counter = 1 then
      10. display := MID({?Unit}[counter],8)
      11. else
      12. display := display + ', ' + MID({?Unit}[counter],8)
      13. );
      14. display;
    • Formula 2
    1. BeforeReadingRecords;
    2. numberVar UnitsCounter;
    3. numberVar Array SelectedUnits;
    4. Redim SelectedUnits[35];
    5. for UnitsCounter := 1 to Count({?Unit}) do
    6. (
    7. if {?Unit}[UnitsCounter] = '0-ALL' then
    8. SelectedUnits[UnitsCounter] = 99
    9. else
    10. if UnitsCounter = 1 then
    11. SelectedUnits[UnitsCounter] = ToNumber(LEFT({?Unit}[UnitsCounter],6))
    12. else
    13. SelectedUnits[UnitsCounter] = ToNumber(LEFT({?Unit}[UnitsCounter],6))
    14. );
    15. ToText(SelectedUnits[2]);  //This line is for debugging to see if array is correctly built. Formula placed on RH or Detail get the same results
  • Created Record Selection Formula
    1. WhileReadingRecords;
    2. numberVar Array SelectedUnits;
    3. {command.Patient Age} >= 18
    4. AND ({command.DepartmentID} IN SelectedUnits or 99 in SelectedUnits
  • ISSUE

    No matter where I place Formula 2 I get no results because the Global Variable Numeric Array is always empty. 

    1. Putting the code of Formula 2 in the Record Selection does not work, as well.
    2. Hard coding values in line 11 and line 13 of formula 2 confirms that the issue is not the functions being used to assign a value to an entry in the array
    3. I confirmed that the data being selected does exist in my SQL Server database. 
    4. I cannot use a Shared variable in the Record Selection because the formula must run while reading records
    5. I placed the following formula (Formula 3) in detail section to confirm the contents of the array
      1. WhilePrintingRecords;
      2. numberVar ArrayIndex;
      3. numberVar Array SelectedUnits;
      4. stringVar DisplayArray;
      5. for ArrayIndex := 1 to Count(SelectedUnits) do
      6. (
      7. if ArrayIndex = 1 then
      8. DisplayArray := DisplayArray + ToText(SelectedUnits[ArrayIndex],0,"")
      9. else
      10. DisplayArray := DisplayArray + ', ' + ToText(SelectedUnits[ArrayIndex],0,"")
      11. );
      12. DisplayArray;
    6. Here is a screen shot of the report results

    • Why won't this work?  Is there a better way?
    MRBKscreenshot.jpg (97.9 kB)
    Add comment
    10|10000 characters needed characters exceeded

    • Get RSS Feed

    2 Answers

    • Best Answer
      Jan 09, 2015 at 07:55 AM

      I have also changed the set the parameter Option "Show Value or Description" to "Show Description" and dragged the parameter to the report header.  I still get the value not the description.

      I have Crystal Reports 2008 Version 12.6.3.1915.  I believe that is up through SP 6.

      Add comment
      10|10000 characters needed characters exceeded

      • Wonderfull!! 😀   With a few tweaks that worked.  Here is the final record selection formula:

        numbervar i;

        stringvar array arr := {?Unit};

        numbervar array sunits;

        for i := 1 to ubound(arr) do

        (

             redim preserve sunits[i];

             If arr[i] = '0-ALL' then

                  sunits[i] := 99

             else

                  sunits[i] := ToNumber(Left(arr[i],6))

        );

        {command.Patient Age} >= 18

        AND ({command.DepartmentID} IN sunits or 99 in sunits)

    • Jan 09, 2015 at 07:04 AM

      By the way,  I am using CR 2008.

      Add comment
      10|10000 characters needed characters exceeded