Crystal Reports Percent Bar Chart

Hi,

I have a problem with a percent bar chart in the report i am currently working on.

To give some background:

The Report is being developed in Crystal Reports V.9.

The report is to calculate how many visits to patients a staff member makes and what proportion of these calls are the different reasons for care.

The report has three groups: StaffName, Date_Of_visit (grouped by month) and Reason_For_Care (4 categories : Diabetes, Leg_Ulcer, Foot_Ulcer and All_Other_Reasons)

I have the chart set up to be displayed in the footer of each staffName group (in the case of the current report there will be 2 members of staff hence two charts)

The Chart that i have created seems to give the correct values but the output is where the problem is. The legend for the chart is in a different order for each member of staff in the report. This means that the percentage sections in the bar chart are not ordered uniformly for each chart.
Is there a way to alter the order of the legend so that it is the same format for each chart. As it is at the moment, comparing similar reasons_for_care is very awkward between differnet charts.

Hopefully this makes sense, any help is much appreciated.

Thanks in advance.
madcatmk2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
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
0
GJParkerCommented:
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
0
madcatmk2Author Commented:
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.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

GJParkerCommented:
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
0
madcatmk2Author Commented:
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
0
GJParkerCommented:
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
0
madcatmk2Author Commented:
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.

0
GJParkerCommented:
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
0
mlmccCommented:
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


0
GJParkerCommented:
mlmcc

I know what it is your trying to do but this won't work as the selection on the contacts table will nullify the LOJ and even without the selction cxriteria tis would return CONTACT.STAFF_NO = NULL for records with no vists in the main table.

The way I would do this is to create a table that contained 2 fields STAFF_NO and PRIMARY_REASON_CARE , then create a record for each staff_no and care reason i.e.

STAFF_NO                PRIMARY_REASON_CARE
10003                       Diabetes
10003                       Leg Ulcer
10003                       Foot Ulcer
10003                       .......
10003                       .........
10071                       Diabetes
10071                       Leg Ulcer
10071                       Foot Ulcer
10071                       ......

Then create an access query to select the requierd records from the contacts table using teh SQL above.

You can then join the New table to the Query in crystal using a LEFT OUTER JOIN on the new table to return teh data required.

Hope this helps

Gary
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
madcatmk2Author Commented:
GJParker:

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

Thanks to everyone for their help.
0
GJParkerCommented:
Glad I could help

Gary
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.