1 2 Previous Next 22 Replies Latest reply on Dec 1, 2010 1:57 PM by denno

    charting help needed



      charting help needed


      Hopefully I can explain my problem...

      The records in my database are for patient visits and list the doctor and type. Type has 3 options. I have created a report that summarizes the percent of visits in each type by doctor. (screenshot)

      The report displays as



      TypeA 5%

      TypeB 45%

      TypeC 50%


      TypeA 20%

      TypeB ...


      I would like to create 3 charts that displays the same information. Each chart would represent 1 of the 3 visit types. The X axis would be the doctors and the Y axis would show the doctor's percentage of visits of that type. I figured out how to get the 3 charts to display. My problem is figuring out what fields to use for the Y axis values. I have tried a number of combinations and can't get what I want.

      In the example above, the chartA would display Name1 and Name2 on the X-axis. Y-axis values would be 5% and 20%.

      The "fr" field is defined as

      GetSummary (type count ; type) / GetSummary (doctor count ; doctorname)

      The chart is in a SubSummary by Type. Records are sorted by (1) Type (2) doctorname

      Thanks for any help-


        • 1. Re: charting help needed

          If I remember the details correctly, you'll need to move this chart into the body of the layout instead of the sub summary. Seems counterintuitive, but I seem to remember that that was where you needed to place such a chart as you describe.

          • 2. Re: charting help needed

            Wow! That did it--you have no idea how long I've spent tinkering with the formula.

            New problem, though--now in Preview it's no longer just 3 pages. (I plan to export this as a PDF.)

            • 3. Re: charting help needed

              Also, do you know how I can get the X-axis to sort by percentage (low-high) rather than by name? I tried creating a Summary field that calcs Fraction of total for "fr" then using that to Reorder by Summary Field but it didn't resort how I wanted.

              • 4. Re: charting help needed

                When you save as PDF, you could specify a page range to eliminate the extra copies. It's an option you can find when you click the Options button on the save as PDF dialog.

                The sorting issue is a problem, you have to sort by type in order to group the records to get the correct percentages. Thus you can't sort by the percentage like you want.

                Maybe someone else knows of a better way, but I think you'd have to set up a different structure for this. You could set up a table with three records, one for Type A, one for Type B, One for Type C that is related to your current file by this type and use aggregate functions such as Sum in a calculation to compute the percentages. This would enable you to then sort the records by percentage instead of by type.

                • 5. Re: charting help needed

                  Would you mind giving a little more detail about the related table approach? I know how to set it up but I'm not sure what to do with the 3 records.


                  • 6. Re: charting help needed

                    That "Three records" statement might be inaccurate. When you chart this data, do you want to see just the data for one doctor at a time?

                    • 7. Re: charting help needed

                      For each Type, I'd like to display a bar graph that shows all the doctors on the X-axis (see new image at top of thread)

                      • 8. Re: charting help needed

                        Ok, then that should be "3 records per doctor"

                        You'd have at least these three fields:


                        You'd use both Type and DoctorID to match a record in this table to your original data table.

                        ChartTable::DoctorID = DataTable::DoctorID AND
                        ChartTable::Type = DataTable::Type

                        For each doctor, you'd load the type field of each record with a different value for type. TypePct would use aggregate functions to compute the percentage needed from the related data in DataTable.

                        Now you can sort your records by doctor then by TypePCT to organize your bars in the desired order.

                        • 9. Re: charting help needed

                          Thanks for the help. I've followed your setup but can't get it to work.

                          I have the relationships set up between the 2 tables (I'm matching on name rather than ID.)

                          I created a new record in the new Type table and entered a name & type1. I then tried creating an aggregate field to pull in TypeCount from the original data table. I set it up as Count (DatatTable:TypeCount) but it shows up blank.

                          Am I missing something?

                          • 10. Re: charting help needed

                            First thing is to test the relationship.

                            Can you get any related data to appear on a layout based on the new table?

                            Go to that layout and use the field tool to add fields from the original table to your layout, such as the doctor's name, then switch to browse mode and check to see if anything appears in this field. If it's empty, you have something wrong with your relationship.

                            • 11. Re: charting help needed

                              It shows up as Index Missing

                              • 12. Re: charting help needed

                                What are you referring to as "it"?

                                Did you use the field tool to add the field or copy and paste the field?

                                Index Missing suggests that you pasted in a field formatted as a conditional value list and the value list isn't working from the context of the new layout.

                                Also, is DatatTable:TypeCount a summary field? if so change the calculation you mentioned eariler from

                                Count (DatatTable:TypeCount)


                                Count ( DataTable::Type )   (any non blank field should work here.)

                                • 13. Re: charting help needed

                                  I used the field tool to add a field that's in the DataTable. Instead of displaying the value from the DataTable, the new field I added displays Index Missing. If I click on that field, I get a dialog message that 1 or more of the relationships between the tables is invalid.

                                  Screenshot shows the relationship--not sure why it doesn't work.

                                  • 14. Re: charting help needed

                                    OK, sounds like attending last name or Type in Clarity Census Report is an unindexed field.

                                    1 2 Previous Next