Crystal Reports Percent Bar Chart

As far as I know, the chart is built based on the order in which the data is found.  Add a sort to the data so all data is sorted by Reason_For_Care.

mlmcc

At the moment the data will be sorted by the 3 groups you have, If the charts are added at Staff name level then the next sort is date of visit and only then by Reason_For_Care.

If you want the chart to display the raesons in teh same oredr each time the you will need either remove the date of visit group or switch the order of the last 2 groups.

Hope this helps

Gary

Firstly, Thank you for the quick responses.

mlmcc: All of the groups are ordered at present anyway, so that makes no difference to the outcome of my charts.

GJParker: When i get the chart output i want it to be split firstly by months then the proportion of each reason for care to be shown for each month. As far as i can make out, i need the to group the visits on the Date_Of_visit group before then splitting it down further with the Reason_For_Care group. If i swap these two fields around the legend then shows each date and the chart shows the reasons for care on the X axis with what proportion of each date was assigned to it.

I need to show the months along the x axis of the graph and percentage of each reason for care in the bar rising from each date.

Any futher suggestions are most appreciated

Thanks for bearing with me, i realise i may not be explaining myself as well as i could.

OK I se the problem now. The problem is that crystal will chart the reasons in teh order that they occur in the data so if for example the data looks like this

Fred                                               John
   Jan                                                 Jan
      Leg Ulcer   4                                    Diabetes    1
      Foot Ulcer  2                                    Leg Ulcer   2
   Feb                                                 Feb
      Diabetes    1                                    Leg Ulcer    1
      Leg Ulcer   2                                    Foot Ulcer   2
      Foot Ulcer  1

then the bars in the chart for Fred for each month will all always be in the order Leg Ulcer, Foot Ulcer, Diabetes whilst the charts for John will always be in the order  Diabetes, Leg Ulcer, Foot Ulcer

Fred                                                            John

     Foot                                                                                      
     Foot                                                      
     Leg           Diabetes                                  
     Leg           Foot                                       Leg             Foot
     Leg           Leg                                        Leg             Foot
     Leg           Leg                                        Diabetes      Leg
-----------+-------------                                 ------------+------------
    Jan            Feb                                        Jan             Feb

This is the way Crystal charting works and the only way I can see around this is to include all reasons in the data for each month i.e.

Fred                                               John
   Jan                                                  Jan
      Diabetes    0                                    Diabetes    1
      Leg Ulcer   4                                    Leg Ulcer   2
      Foot Ulcer  2                                    Foot Ulcer  0
   Feb                                                 Feb
      Diabetes    1                                    Diabetes     0
      Leg Ulcer   2                                    Leg Ulcer    1
      Foot Ulcer  1                                    Foot Ulcer   2

Hope this helps

Gary

Thats exactly whats happening.
I now understand why it is happening.
As you have also pointed out, The way round this problem would be to include all counts for all reasons for care which at present the report doesn't do this. I was under the impression that crystal didn't show the records if there were no values for them.

Sorry to ask this one more question but, how would i go about showing all entries even if there are no contacts for the particular reason for care.

Thanks so much for that, I can now at least said i've learnt something about the charting side of things.

Cheers

Well I'm glad you have at least learnt something.

You are of course right that crystal does not display data which isn't there, so we will somehow have to get the data into the recordset. The best way to do this will depend on how the data is held in the database.

What database are you using and how is the data held in the table(s), include any linking information. It is probably easiest to show the SQL you are using for the report you can get this via the Database menu -> Show SQL query

Gary

Database is Access 97.

SQL Query is as follows:
[code]
 SELECT `CONTACTS`.`STAFF_NO`, `CONTACTS`.`DATE_OF_VISIT`, `CONTACTS`.`TIME_OF_VISIT`, `CONTACTS`.`PRIMARY_REASON_CARE`, `CONTACTS`.`PATIENT_NO`, `CONTACTS`.`OTHER_REASONS_CARE`
 FROM   `CONTACTS` `CONTACTS`
 WHERE  (`CONTACTS`.`STAFF_NO`=10003 OR `CONTACTS`.`STAFF_NO`=10071) AND (`CONTACTS`.`DATE_OF_VISIT`>=#2004-08-01 00:00:00# AND `CONTACTS`.`DATE_OF_VISIT`<#2005-02-01 00:00:00#)
[/code]

I kind of figured that i'd have to have the data in the database already. I'll look into how this can be done.

Thanks for your help.

I'll have a think about this one overnight before suggesting a way forward. Also see what some of the other experts can come up with.

Gary

The easiest way to include the data in the tables would be to have a MALADY table as the main table (Your PRIMARY_REASON_CARE)

Something like this

Table Malady
Fields - PRIMARY_REASON_CARE   STRING

New Query

SELECT 'MALADY.PRIMARY_REASON_CARE',   `CONTACTS`.`STAFF_NO`, `CONTACTS`.`DATE_OF_VISIT`, `CONTACTS`.`TIME_OF_VISIT`, `CONTACTS`.`PRIMARY_REASON_CARE`, `CONTACTS`.`PATIENT_NO`, `CONTACTS`.`OTHER_REASONS_CARE`
 FROM   'MALADY' 'MALADY'  LEFT OUTER JOIN `CONTACTS` `CONTACTS`
ON 'MALADY.PRIMARY_REASON_CARE' = `CONTACTS`.`PRIMARY_REASON_CARE`
 WHERE  (`CONTACTS`.`STAFF_NO`=10003 OR `CONTACTS`.`STAFF_NO`=10071) AND (`CONTACTS`.`DATE_OF_VISIT`>=#2004-08-01 00:00:00# AND `CONTACTS`.`DATE_OF_VISIT`<#2005-02-01 00:00:00#)

mlmcc

GJParker:

That helped a lot. thanks for that. I see what i need to be doing now.

Thanks to everyone for their help.

Glad I could help

Gary