Crosstabs, sort dynamic columns by descending column totals, show blank columns
Posted on 2006-06-16
Hello all, I am trying to create a crosstab for a report. This is the SQL I am using for my working crosstab...
TRANSFORM Count(x.SKU_NBR) AS CountOfSKU_NBR
SELECT PRTHD_FSCL_YR_WK.FSCL_YR_WK_NBR AS Week
FROM PRTHD_FSCL_YR_WK, x
WHERE (((x.TIMESTAMP) Between [WK_BGN_DT] And [WK_END_DT]))
GROUP BY PRTHD_FSCL_YR_WK.FSCL_YR_WK_NBR
PIVOT x.PROCESSING_FLG In (0,1,2,3,4,5,6,7,8,9,10,11,88,99);
You will notice that I have a custom header to show the type codes of even the blank headers, however this will become inacurate if another typecode is added, can this be done in a way so as to account for all type codes (incluuding blank ones) on the fly?
The other slightly less important , however probably more difficult question is whether or not I can sort the columns by a sum of their totals in descending order from left to right.
Currently I copy the cross tabs into excel where I match the processing flag to the statement it represents, calculate the totals for each column and add percentages to the ends of the rows between proc type 99 devided by the sum of all the columns on each row, however I wouldnt mind putting all this in a report so that I could just open the report and have the data how I need it.
Thanks so much,