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
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
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
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
Split() function should return values you want
@Classification
Split(yourfield, "<br>")[2]
Ian
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
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