Skip to Content
0

Crystal reports counting multivalue field

Mar 02 at 06:37 AM

32

avatar image
Former Member

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Abhilash Kumar
Mar 02 at 06:42 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Mar 06 at 04:39 AM
0

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


capture.png (8.7 kB)
Show 1 Share
10 |10000 characters needed characters left 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

0
Ian Waterman Mar 06 at 12:01 PM
0

Split() function should return values you want

@Classification

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

Ian

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Mar 07 at 03:29 AM
0

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

Show 1 Share
10 |10000 characters needed characters left 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

0