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

    charting help needed

    denno

      Title

      charting help needed

      Post

      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

      []

      Name1

      TypeA 5%

      TypeB 45%

      TypeC 50%

      Name2

      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-

      2010-11-17_17-51-01.png

        • 1. Re: charting help needed
          philmodjunk

          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
            denno

            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
              denno

              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
                philmodjunk

                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
                  denno

                  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.

                  Thanks-

                  • 6. Re: charting help needed
                    philmodjunk

                    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
                      denno

                      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
                        philmodjunk

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

                        You'd have at least these three fields:

                        DoctorID
                        Type
                        TypePCT

                        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
                          denno

                          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
                            philmodjunk

                            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
                              denno

                              It shows up as Index Missing

                              • 12. Re: charting help needed
                                philmodjunk

                                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)

                                To

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

                                • 13. Re: charting help needed
                                  denno

                                  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
                                    philmodjunk

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

                                    1 2 Previous Next