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.
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.)
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.
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.
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.
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?
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)
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.
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?
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.
It shows up as Index Missing
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 ( DataTable::Type ) (any non blank field should work here.)
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.
OK, sounds like attending last name or Type in Clarity Census Report is an unindexed field.