Skip to Content
Former Member
Aug 25, 2008 at 12:02 PM

Crosstabs vs. Manual: Both Coming Up Short


I have a significant issue and not enough breadth of knowledge in just how to solve it.

The basics: I have a report I'm making that is supposed to show a summary of scores from tests taken and there are quite a few suboptimal elements to it that make it a complex report to generate. The basic shape of it in the old version(FoxPro) was a grid with test areas for the rows and test takers as columns along the top. Each page would also have two averages, one for the group of test takers in each group and a second for the larger group overall.

I've tried to make it with Crosstabs, which works well save for three prominent factors. For one, the broader average is actually a value calculated elsewhere and put into the data manually. When it comes to Crosstabs, there's no way to put a static value into it cleanly. I could insert it as part of the area names for each row, but the header I put under the crosstab to mark it doesn't carry over when the crosstab goes to a second page.

A second issue is that there are various types of areas for each test, most of which belong to an overall group called 'equipment'. It isn't a real group, but rather a subtotal of sorts. As far as I can tell, inserting a subtotal with Crosstabs is impossible as well. Both factors lead me to believe that Crosstabs simply won't work for me. At least, not when meeting the customer's needs.

I put this stuff up because I hope in waiting that someone will enlighten me to some things I've not realized or learned yet when it comes to Crosstabs that will solve all of my myriad of issues because making the report manually is no more promising.

I've remade the report in a simpler way, displaying a more generic rundown of guy after guy(groups) with the areas displayed for each with large and small scale averages displayed for each one(detail field). The only issue I have here, thus far, is trying to generate the smaller average.

The way things are, each test could have a varying number of areas, from 4 to upwards of 15. So I can't predict how many areas there will be. And in that, I can't quite figure out a clean way to calculate up an average for each small group that will display for all of the guys. Usually for averages, I calced them up in the details as the report runs and stuck them in the footer, but I don't have that option this time. I do wonder if the WhileReadingRecords term will allow me to make a formula that will do all the calculations before the report is run, but if so will it allow me to reset the averages after each small group?

To be honest, I don't really care which way I go to generate this report. I just want to get this painful thing done, but no matter which path I take, it always seems to be lacking in some way, with none giving me just what I'm after. I'm in heavy need of some guidance.