Pentaho Ctools Table显示交叉表

Pentaho的CDE显示交叉表格

 

原来以为Table Component只能显示二维表格，但是在《Learning Pentaho CTools(PACKT,2016).pdf》看到交叉表(第6单元)

 

 

.

MDX:

WITH
MEMBER [Measures].[Trend] as IIF(ISEMPTY(([Time].CURRENTMEMBER.lag(1), 
[Measures].[Sales])) OR [Measures].[Sales]=0, 0, ([Measures].[Sales]-
([Time].CURRENTMEMBER.lag(1), [Measures].[Sales])) /[Measures].
[Sales])+0
MEMBER [Measures].[Sales ($)] as [Measures].[Sales]+0
MEMBER [Measures].[Qt] as [Measures].[Quantity]+0
SELECT
NON EMPTY CrossJoin({[Markets].[Territory].Members}, {[Measures].[Qt], [Measures].[Sales ($)], [Measures].
[Trend]}) ON COLUMNS,
NON EMPTY {[Product].[Product].Members} ON ROWS
FROM [SteelWheelsSales]
Where [Time].[2004]

 

Table Component

.

Post Execution

 

function f() {
    var cells = $( "#" + this.htmlObject + " thead th" );
    
    
    
    var level = "1";
    cells.each(function(i, v) {
        if( i > 0 ) { //skip the first cell of each row
            var cell = $( v );
            var originalText = cell.text();
            var originalTextParts = originalText.split( "/" );
            // cell.text( originalTextParts[1] ); //write the second part (the mobile company in your case)
            
            if(originalTextParts.length>1) {
                level = originalTextParts.length-1;
            }
        }
    });
    
    var idx;
    for (idx = 0; idx < level; idx++) {
        var newHeaderRow1 = "<tr><th></th>";
        var text=''
        var text_length=1
        cells.each(function(i, v) {
            if( i > 0 ) { //skip the first cell of each row
                var cell = $( v );
                var originalText = cell.text();
                var originalTextParts = originalText.split( "/" );
                // cell.text( originalTextParts[1] ); //write the second part (the mobile company in your case)
                

                if(originalTextParts.length==1) {
                    newHeaderRow1+="<th></th>"
                } else if(originalTextParts.length>1) {
                    if(text!=originalTextParts[idx]) {
                        if(text === '') {
                            text=originalTextParts[idx]
                        } else {
                            newHeaderRow1+=("<th valign='middle' align='center' colspan='"+text_length+"'>"+text+"</th>")
                            text_length=1
                            text=originalTextParts[idx]
                        }
                    } else {
                        text_length++;
                        if((cells.length-1) == i) {
                            newHeaderRow1+=("<th valign='middle' align='center' colspan='"+text_length+"'>"+text+"</th>")
                            text_length=1
                            text=originalTextParts[idx]
                        }
                    }
                }
            }
        });
        newHeaderRow1+="</tr>"
        $( "#" + this.htmlObject + " thead" ).prepend( newHeaderRow1 );
    }
    
    
    cells.each(function(i, v) {
        if( i > 0 ) { //skip the first cell of each row
            var cell = $( v );
            var originalText = cell.text();
            var originalTextParts = originalText.split( "/" );
            cell.text( originalTextParts[1] ); //write the second part (the mobile company in your case)
        }
    });
    
    
    var newHeaderRow = "<tr><th></th><th colspan='4'>Latenza Media</th><th colspan='4'>Through. Download Medio</th></tr>";
    // $( "#" + this.htmlObject + " thead" ).prepend( newHeaderRow );

    //add some style...
    $( "#" + this.htmlObject + " thead th" ).css( "border", "1px solid #DEDEDE" ).css( "background", "#F9F9F9" );
    $( "#" + this.htmlObject + " thead th:eq(0)" ).css( "border", "0px solid #DEDEDE" ).css( "background", "#FFFFFF" ); //hide first cell
} 

 

 

.