Include pivot table "like" summaries in a report footer

[Xarkath]

Hello,



I have a report based on a cross tab query of insurance premiums. The report contains a family description for each subscriber and there are approximately 30 possible descriptions. Is it possible for the report to count and summarize each of the descriptions? Something like a pivot table except that I don't want both count and grand total columns, just the count. If so how I would set it up? I have accomplished this by building extra queries and reports but it seems a bit sloppy on the backend so if it is possible I would rather build it right into the main report.

The end result would look something like:
Description Count
Single 10
Subcriber & spouse 50
Family with 1 child 45
Family with 3 children 24


Thanks

[June7]

If the report were based on a regular query, expression in textboxes in footer section:

=Sum(IIf([Description]="Single",1,0))

etc.

Are the descriptions pivoted as the column headers in the crosstab?

[Xarkath]

Hi June7,

The descriptions are not pivoted. The cross tab is used to list the rates by benefit elections and it is those benefits that are pivoted into columns. The family descriptions start and end as a single column throughout the database.

So if I understand your response correctly I would need to enter that formula for each variable?

Thanks

[June7]

I really don't understand the need for calculation. If the descriptions are the row headers then isn't there already an aggregate value for each? Perhaps if you provide sample data and example of desired output or even posted the db, could better advise.

[Xarkath]

Hello,

The security setting at work will not allow me to post an image. Basically the output has the following headers:
Group - Location - Member ID - Subscriber Name - Family Description - Medical Premium - Dental Premium - Vision Premium - Premium totals

The report counts the total number of subscribers just fine but our clients want the report to break it down by the family description. Of the total number of subscribers X are Subscriber only, X are Subscriber and spouse, X are Subscriber and child, and so on. All this is easy enough as separate componets, the trick seems to be getting the report to include it without the need for subreports.

Thanks

[June7]

Okay, options:

1. report Grouping & Sorting, group on Family Description field with everything else in Detail section and Count() function in Group footer - I don't have much call to use crosstab but think should work

2. textboxes in footer section with the conditional calc for each Family Description value

[Xarkath]

Thank you. Niether is what I was hoping for but both are better than I really expected and I truely appreciate the help.