One of the most useful things that I do when developing summary reports is to allow the user to select how they want the data summarized when they run the report. So, for example, I provide them a parameter for Group1, a parameter for Group2 and a parameter for Group3. In Group1 they have certain predefined choices (in my world they are things like Location, Department, Physician, Insurance) or "None" in case they don't want to use that summary level. I provide similar options for groups 2 and 3. In this manner the user can choose to run the report one time by Location, then Department and then Physician. Next time they can run it by Department / Physician / Insurance. The one report allows them complete flexibility in how to summarize.
It is relatively easy to set this up. First of all, as I outlined above, you will create multiple group level parameters: Group1, Group2 and Group3 for my purposes. These will be string parameters that are static. I will enter in the list of possible choices that you want them to have in the value list, putting "None" as the first value. For my example, I would enter values like "Loc","Dept", "Phys", etc. In the description field for the value I would enter "Visit Location", "Visit Department" and "Visit Doctor".
Once I've done this for all three Group parameters, I need to set up 3 formulas: one for each group. I will use these formula fields to actually group my data. My formula for Group1 would be something like:
if {?Group1} = "None" then "No Grouping" else
if {?Group1} = "Loc" then {MyTable.LocID} + {MyTable.LocationName} else
if {?Group1} = "Dept" then {MyTable.DeptID} + {MyTable.DeptName} else
if {?Group1} = "Phys" then {MyTable.PhysID} + {MyTable.PhysicianName} else
"No Grouping"
After I have a formula field set up for each possible grouping, I set my report groupings to be Group1/Group2/Group3 in that order.
Finally I set the group headers and footers to be suppressed if the value for that level of grouping is "None". This is usually only necessary on Group3 but I do it for Group2 as well.
Have fun! If you have any questions don't hesitate to ask.
Edited by dbodell - 30 Apr 2012 at 5:47am
IP Logged
