Excel Type Reports???

[Thom Lee]

I�m stuck.

We have a database that we use to track our memberships with. The director wants a report next week of the following information:

Breakout report of database by State and then within each state the Group totals with Subgroup totals broken out of it. It is very difficult to explain here, so I�m attaching a sample Excel sheet to show what we are trying to do.

The membership use to have this information broken out by going through and tallying all the �cell� information by hand. One at a time. Considering there are probably 300+ �cells� that would need to be tallied, I don�t think that is an efficient way to do this.

I�ve got some of the programming down, but keep getting lost with what I am trying to do. Even if I can just get the information to print out on a sheet of paper, I guess I can rekey the data over to Excel to make it look pretty. I don�t know if there is a way to get the report to look like a spreadsheet � if there is, that would sure save a load of time.

I would appreciate any help.

Thanks,
Thom

[crimmelcp]

RE: Excel Type Reports???

Thom:
i dont think it would be hard to do.
Car you post the entire database so some of us could try.
just need sample data
charlie crimmel

If you want to try it
add fields to the table (you might already have these)
group char 15
level1 numeric 6 0
level2 numeric 6 0
state counts
alcount numeric 6 0
decount numeric 6 0
gacount numeric 6 0
mdcount numeric 6 0
totcount numeric 10 0

use an update routine to populate the fields
(easier that using calculated fields)

summarize the table by group
total
level1
level2
alcount
decount
gacount
mdcount
totcount

base your report on the summarized file

[CALocklin]

RE: Excel Type Reports???

I've been working on this at the same time as Charlie and here are my thoughts:

First, consider how often you run this report. If it is only an annual report, you might want to consider doing it as you are now because it can probably be done in under an hour and any report format changes that may be required from year to year might take just as long. If it is a monthly report, it is probably worth automating.

The first thing is that it often easier if the totals are at the end of the report. This isn't a requirement but it is usually more logical and reports based on thousands of records will run faster. (Based on this, you have a very small amount of data.)

Second, each grouping will have the same layout so the Toddlers section will end up with at least 3 lines: a title line, at least one Lvl line, and a Total line.

You didn't tell us what the 'groups' are called so I will refer to the Lvl# as the Level and the Boys, Girls, Junior Men, etc. as the Category.

I would start by building a new, temporary database using the crosstab operation. This will be the key to the whole thing. You need to get columns (fields) based on the State and rows (records) based on a combination of the Category and Level. For example, the rows would be Boys Level 1, Boys Level 2, Girls Level 1, etc. This can be done by using an expression of Category + Level for the rows. The resulting field name will be something like Category_Level. The resulting field values will later be broken down into two groups so it is important that these names be built so they can be split appropriately. In your case, it appears as though the last part of the name will always be 4 characters, "lvl1" or "lvl2", so this should work. Otherwise, you would need to change the definition of the row to something like Group + "|" + Subgroup so you could split it at the "|" character.

Now, building the report becomes fairly easy. The first report group is based on right(Category_Level,4) and the next group (actually the 'parent' group) is based on left(Category_Level, len(trim(Category_Level))-4). And, of course, each 'parent' group will need a group header for the title (which is defined the same as the group) and a footer to hold the total of the levels within it.

To get the grand totals, you will need a report footer with some calculated fields:
1. The total of the qty in each level to get the total members.
2. The count of all quantities greater than 0 to get the total divisions. This sounds difficult but one way to do it is to define a calculated field, I'll call it Lvl_Cnt, as IF([lvl_qty_fld_name]>0,1,0). Now you can simply total the Lvl_Cnt field to get the total number of divisions.

One last problem! Since the report is based on a temporary table, how do you keep the report for the next time? Easy, once you get the table and report working as you want it, make it a permanent table then, next time, create a crosstab table, zap the records in the permanent table, and append the data from the crosstab into the permanent table so you can run the report.

[Thom Lee]

RE: Excel Type Reports???

I think with the use of crosstabs you have sent me in the right direction. Thank you BOTH for your input.

The file that I first attached was a simplistic layout of what I really needed. I didn't want to make it "more difficult" than what I needed to.

You see, there are 26 columns by probably 50 or so rows. The database is over three megs with about 7000 members. So as you can see, a large deal of work.

I'm going to try to work it in this direction. Even if I can't get it to look pretty (for the boss!) I can save it in another format and forward it to excel.

Thanks!
Thom