|
|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
| ||||||||||||||||||||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
|
#1
|
|||
|
|||
|
displaying Crosstab column headings
i have a form that users can select three separate variables from. Term, Var1, and Var2. I use the term to determine which table to get data from, and the other variables determine which field names will be compared with each other in a crosstab.
i created the crosstab in Access and just copied and pasted the code, then changed the absolute field names to the variable names, var1 and var2. this means i only need one sql statement that can be used no matter what term or variables the person chooses. Unfortunately, it seems i still need to write different code for each combination possible (at least 70) in order to display it correctly. for example, after the sql code: <cfset term = #Form.Term#> <cfset var1 = #Form.Var1#> <cfset var2 = #Form.Var2#> <cfset comb = "#var1##var2#"> <CFQUERY NAME="#var1##var2#" DATASOURCE="DSN"> TRANSFORM Count(#term#.count) AS CountOfcount SELECT #term#.#var1#, Count(#term#.count) AS [Total_Of_count] FROM #term# GROUP BY #term#.#var1# PIVOT #term#.#var2#; </CFQUERY> i'll display the results as such: <table cellpadding="3" cellspacing="0" border="0" width="600"> <tr bgcolor="#E1E8EC"><td><cfoutput>#var1#</cfoutput></td><td align="right">Undergrad</td><td align="right">Graduate</td><td align="right">Law</td><td align="right">Medical</td><td align="right"><b>Total</b></td></tr> <CFOUTPUT QUERY="#comb#" group="#Evaluate(var2)#"> <tr><td>#Evaluate(var2)#</td></tr> <tr><td>#Replace(Evaluate(var1), "_", " ", "ALL")#</td><td align="right">#Undergrad#</td><td align="right">#Grad#</td><td align="right">#Law#</td><td align="right">#Medical#</td><td align="right"><b>#Total_Of_count#</b></td></tr> <cfif #Undergrad# gt 0><cfset atotal = atotal + #Undergrad#></cfif> <cfif #Grad# gt 0><cfset btotal = btotal + #Grad#></cfif> <cfif #Law# gt 0><cfset ctotal = ctotal + #Law#></cfif> <cfif #Medical# gt 0><cfset dtotal = dtotal + #Medical#></cfif> <cfset etotal = etotal + #Total_Of_count#> </CFOUTPUT> <cfoutput><tr><td><b>Total</b></td><td align="right"><b>#atotal#</b></td><td align="right"><b>#btotal#</b></td><td align="right"><b>#ctotal#</b></td><td align="right"><b>#dtotal#</b></td><td align="right"><b>#etotal#</b></td></tr></cfoutput> </table> what i would prefer to do is somehow replace the Grad, Law, Medical, etc variables with something not specific to any case. So that i'll only need one section that is used to display the results. Undergrad, Grad, Law, and Medical were in a column in the original table, and they are being summed up in the crosstab. in order for this to work, i would need a way to have the words "Undergrad", "Grad", etc displayed as the column headings. And for every possible combination the user might pick, it would change not only the heading names, but the number of headings being displayed. I don't know if this is possible or if i've explained it well enough, but any suggestions would be much appreciated. thanks |
![]() |
| Viewing: Dev Articles Community Forums > Programming > Cold Fusion Development > displaying Crosstab column headings |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|