Skip to Content
avatar image
Former Member

Crystal reports counting multivalue field

Hi,

I am trying to find a workaround for reporting from a Statewide system by using extracts to excel and creating the reports in crystal. Being a statewide system, I cannot alter tables, I can only work with the extracts which has one field with multiple values - very annoying as these should have been in rows. This is how it looks:

ID Name Class date

456 Fred Fall <br>Infection<br>Deterioration<br>Manual Task 11/12/17

I need a count of each value in class so I can get a count of Falls, a count of infection etc and added to this, I need to chart the class,

ie class on x axis with count of class on y, so I think this needs to be in one formula.

I've tried splitting the class field in excel so each value has it's own column but still can't workout how to count each class in one formula.

Any assistance will be hugely appreciated :)

thanks

B

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Mar 02 at 06:42 AM

    Hi Brenda,

    1. Create a formula called "@Falls" with this code:

    If Instr({Class field}, "Fall") > 0 then 1

    2. Similarly, create a separate formula for each value. The formula for infection would look like this:

    If Instr({Class field}, "Infection") > 0 then 1

    3. You should now be able to add these formula fields to a chart (set its sum function to "sum").

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 06 at 04:39 AM

    Thanks so much for replying Abhilash,

    If I create separate formulas then I won't be able to chart "Classifications" as one . The attached example is what I am trying to achieve. Each classification has a rating, hence the stacking. So the 'on change of' option in charts should be Classification and Rating, with the sum or count of Classification in 'show value'. Am I missing something fundamental?

    thanks again, very much appreciated.

    Cheers

    Brenda

    capture.png

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Brenda,

      I misunderstood the data setup.

      You would have to pivot the data into rows so that CR sees it as a "single column".

      If this is not possible, you'd need an elaborate SQL logic to generate "dummy" rows per ID row depending on how many values there are in the "Class" field.

      If you're able to setup the SQL to generate dummy rows, you could use a Subreport to pivot the data using some more CR level techniques.

      -Abhilash

  • Mar 06 at 12:01 PM

    Split() function should return values you want

    @Classification

    Split(yourfield, "<br>")[2]

    Ian

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 07 at 03:29 AM

    Thanks Ian and Abhilash,

    Not sure what you mean Ian. When I use

    Split({Incidents_.Class}, "<br>")[{@arraylength}+1] returns the last classification in the field. How do I count each classification - not a total, but by each classification?

    Ta

    B

    Add comment
    10|10000 characters needed characters exceeded

    • Hi

      I thought this was the contents of a single field

      456 Fred Fall <br>Infection<br>Deterioration<br>Manual Task 11/12/17

      So for the above field

      Split(yourfield, "<br>")[2]

      Would return "Infection"

      What is this formula doing

      @arraylength

      Ian