?
Solved

Crystal Reports Percent Bar Chart

Posted on 2005-02-28
12
Medium Priority
?
1,141 Views
Last Modified: 2008-01-09
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.
0
Comment
Question by:madcatmk2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
12 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 13419540
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
 
LVL 19

Expert Comment

by:GJParker
ID: 13419732
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
 

Author Comment

by:madcatmk2
ID: 13420283
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 19

Expert Comment

by:GJParker
ID: 13420626
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
 

Author Comment

by:madcatmk2
ID: 13420725
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
 
LVL 19

Expert Comment

by:GJParker
ID: 13421042
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
 

Author Comment

by:madcatmk2
ID: 13421163
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
 
LVL 19

Expert Comment

by:GJParker
ID: 13421283
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 13425402
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
 
LVL 19

Accepted Solution

by:
GJParker earned 750 total points
ID: 13427552
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
 

Author Comment

by:madcatmk2
ID: 13427747
GJParker:

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

Thanks to everyone for their help.
0
 
LVL 19

Expert Comment

by:GJParker
ID: 13427789
Glad I could help

Gary
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question