Hi,
I am trying to use dbr.editable in cross tab report. And from the current version I run, it said "no support", the same result for trying dbr.report or dbr.colstyle. That let me come to following solution:
I use prepare statement (from MySQL) to generate crosstab, also use while - do to loop through all cross tab column and set the dbr.report, dbr.colstyle.
But after the report works, the dbr.editable, dbr.report ... say "Unknown column reference" for all column, except some. I dont know why.
DROP TEMPORARY TABLE IF EXISTS tblTargetSale;
-- this code will create all cross tab string
SET @sql1 = null;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT('SUM(case when DATE(Invoice_Date) =''', DATE(Invoice_Date), ''' then Sale end) AS Day.', LPAD(DAYOFMONTH(Invoice_Date),2,'0'), '[D', DAYOFMONTH(Invoice_Date) ,']'
)) INTO @sql1
FROM Sales.target_all
WHERE Year = inYear AND Month = inMonth
;
SET @sql1 = IFNULL(@sql1, '''0'' as None ');
--
SET @sql1 = CONCAT('CREATE TEMPORARY TABLE IF NOT EXISTS tblTargetSale AS (
SELECT s.Com as Company[Company], s.BizLine as BU[BU], s.Center as Center[Center], s.Dept as Dept[Dept], s.Geo as Geo[Geo], SUM(Sale) as Month[xMonth],', @sql1 ,
'FROM Sales.target_all s WHERE Year=', inYear, ' AND Month=', inMonth,
' GROUP BY s.Com, s.Bizline, s.Center, s.Dept, s.Geo)')
;
-- this is for debug SELECT @sql1;
PREPARE stm FROM @sql1; EXECUTE stm;
SELECT * FROM tblTargetSale;
The Company and Center don't show 'Unknown', but nothing happen, no editable, no link, no style effect. Other column like xMonth, BU. Dept, Geo all say 'Unknown'. The same result when I dont use Temporary table (tblTargetSale) and the @sql1 is (SELECT .... FROM ... GROUP BY ...) query.
I saw the @sql1 after generated:
CREATE TEMPORARY TABLE IF NOT EXISTS tblTargetSale AS ( SELECT s.Com as Company[Company], s.Bizline as BU[BU], s.Center as Center[Center], s.Dept as Dept[Dept], s.Geo as Geo[Geo], SUM(Sale) as Month[xMonth],SUM(case when DATE(Invoice_Date) ='2014-06-02' then Sale end) AS Day.02[D2],SUM(case when DATE(Invoice_Date) ='2014-06-04' then Sale end) AS Day.04[D4],SUM(case when DATE(Invoice_Date) ='2014-06-01' then Sale end) AS Day.01[D1]FROM Sales.target_all s WHERE Year=2014 AND Month=6 GROUP BY s.Com, s.Bizline, s.Center, s.Dept, s.Geo)
Of course main column doesn't work, the cross tab column can't not too.
Thanks,
