Crosstab - Columns and Summary from Array

dfeiock

Is there a way to use an array to seed the columns and summary fields for a crosstab?

My data structure is as follows:
MainGroup, SubGroup, LABELS, AVGS, COUNTS
"GroupA", "GroupA.1", "Q1,Q2,Q3", "4,2,6", "50,103,42"
"GroupA", "GroupA.2", "Q1,Q2,Q3", "8,3,1", "13,24,86"
etc

Getting the MainGroup and SubGroup to display properly is not a big deal. The difficulty comes with the AVGS and COUNTS fields. These fields are dynamic, so adding a column for AVG1, COUNT1, AVG2, COUNT2, etc would be a maintenance problem. The other option I have seen is storing the AVGS and COUNTS one value per row, rather than aggregating them into a single field. Doing that would make the database row count spiral out of control.

I am using a computed column to make AVGS into an array (new Array(row["AVGS"].split(",")); ). Is there a way to iterate over an array to produce columns in a crosstab (using the same method for LABELS and I did for AVGS)? Also, is there a way to do the same iteration to display the values (AVGS array) in the summary fields?

Thanks,
- DF

mwilliams

Hi DF,

If you can access your data with java or javaScript, you could use a scripted dataSource and step through your data and structure it how you need it to use in a crosstab.