Skip to Content
avatar image
Former Member

New to Crystal - formula help

I'm creating a results letter using the following example dataset:

Name stream result

smith level2 fail

smith level2 pass

howse level 2 pass

howse level 1 pass

foote level 1 pass

barrow level 2 fail

yell level 3 fail

yell level 2 fail

so dataset can include same name with either:

same stream and different result or

diff stream and same or different result

The group is on name, the details include stream and result side by side

The footer includes several text boxes and I need to either show or supress each text box depending on the aggregated result for each name, i.e.

if result= fail all (in this case 'barrow' or 'yell') then show text box 1

if result= pass any (in this case 'smith') then show text box 2

if result= pass all (in this case 'howse' and 'foote') then show text box 3

I'm really struggling on how to even start - please help

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Mar 06, 2017 at 10:28 AM

    Hi Claire,

    1. Create a formula @fail with this code:

    If {Result} = 'fail' then 1

    2. 1. Create a formula @pass with this code:

    If {Result} = 'pass' then 2

    3. Right-click Text box 1 > Format Field > Common tab > Click the formula button beside 'Suppress' and use this code:

    Maximum({@fail}, {Name}) <> 1

    4. Right-click Text box 2 > Format Field > Common tab > Click the formula button beside 'Suppress' and use this code:

    Not(Maximum({@fail}, {Name}) = 1 AND Maximum({@pass}, {Name}) = 2)

    5. Right-click Text box 3 > Format Field > Common tab > Click the formula button beside 'Suppress' and use this code:

    Not(Minimum({@pass}, {Name}) = 2 AND Maximum({@pass}, {Name}) = 2)

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi thx for your kind assistance. my formula falls down a little ( I also need to correct my info - sorry!)

      • actually, the pass all, is only for people who pass all 4
      • and 'pass any' is only for people who pass 1, 2 or 3
      • some of the results are graded, i.e. grade a, grade b and grade c are equal to a pass, but grade d is equal to a fail.

      so I have adapted the formulas to take account of the grades as follows:

      1. @fail

      If {Result} = 'fail' then 1

      else If {Result} = 'grade d' then 1

      2. @pass

      If {Result} = 'pass' then 2

      else If {Result} = 'grade a' then 2

      else If {Result} = 'grade b' then 2

      else If {Result} = 'grade c' then 2

      3. text boxes have the formula's as suggested in Abhilash's answer above.

      .

      My problems are:

      1. Fail all

      where a name is 'fail all', they should only see text box 1 (this is happening ok)

      2. Pass 1, 2 or 3

      where a name is 'pass 1,2 or 3', regardless if they also have a failed paper - they should see text box 2 only (currently they see text box 1 and text box 2)

      2. Pass all 4

      where a name is 'pass all 4' they should see text box 3 only (currently text box 3 is visible even where one paper is passed, out of one paper taken)

      Thanks so much.

  • avatar image
    Former Member
    Mar 08, 2017 at 05:31 PM

    Hi thx for your kind assistance. my formula falls down a little ( I also need to correct my info - sorry!)

    • actually, the pass all, is only for people who pass all 4
    • and 'pass any' is only for people who pass 1, 2 or 3
    • some of the results are graded, i.e. grade a, grade b and grade c are equal to a pass, but grade d is equal to a fail.

    so I have adapted the formulas to take account of the grades as follows:

    1. @fail

    If {Result} = 'fail' then 1

    else If {Result} = 'grade d' then 1

    2. @pass

    If {Result} = 'pass' then 2

    else If {Result} = 'grade a' then 2

    else If {Result} = 'grade b' then 2

    else If {Result} = 'grade c' then 2

    3. text boxes have the formula's as suggested in Abhilash's answer above.

    .

    My problems are:

    1. Fail all

    where a name is 'fail all', they should only see text box 1 (this is happening ok)

    2. Pass 1, 2 or 3

    where a name is 'pass 1,2 or 3', regardless if they also have a failed paper - they should see text box 2 only (currently they see text box 1 and text box 2)

    2. Pass all 4

    where a name is 'pass all 4' they should see text box 3 only (currently text box 3 is visible even where one paper is passed, out of one paper taken)

    Thanks so much.

    Add comment
    10|10000 characters needed characters exceeded