The format we dispaly the data is always different from the format we store the data. For example, we store the monthly trade summary in row based format, one row for each month, but when we display it, we need column based format for every year. Which we named it as crosstab feature.
Previously we can only use one column as the row identifier, in the recent update, I remove this limit, allow you specify multiple columns as the row identifier. I will show you a demo about it, we will add a quater column to the initial crosstab demo.
WEBCHART.XSLDOC=xsl/default.xsl
WEBCHART.QUERY_1=select to_char(trade_month,'"Q"q') quater, \
to_char(trade_month,'mm') month , \
to_char(trade_month,'yyyy') year , \
trade_count count \
from trade_monthly_summary \
order by 1,2
WEBCHART.CROSSTAB_1=quater,month|year|count
WEBCHART.GROUP_1=1
When we open the pages, it will look as following.
The customer's requirement is more complex then above, now we need to know the increase ratio and the increase trade count of 2008 compares to 2007. If you rewrite the SQL, it will be very complex. However we can get it done easily with formula column in WebChart.
WEBCHART.XSLDOC=xsl/default.xsl
WEBCHART.QUERY_1=select to_char(trade_month,'"Q"q') quater, \
to_char(trade_month,'mm') month , \
to_char(trade_month,'yyyy') year , \
trade_count count \
from trade_monthly_summary \
order by 1,2
WEBCHART.CROSSTAB_1=quater,month|year|count
WEBCHART.GROUP_1=2
WEBCHART.EXPRESS_1=Ratio|X/Y|2008,2007 \
Inc|X-Y|2008,2007
When we open the pages, it will look as following.
With crosstab and formula column, it make the data display very easy sometime.