XMLA 4 JavaScript Example

xmla4js-script.js

var query = "select {[Measures].[Sales Amount], " + 
	"[Measures].[Average Sales Amount]} ON COLUMNS, " +
	"[Product].[All Products].Children ON ROWS" + 
	" From [Sales Summary]";

var rowset =  function(mdx){
	xmla = new Xmla();
	return xmla.execute({
		async: false,
		url: "http://localhost/olap/msmdpump.dll",
		statement: mdx,
		properties: {
			DataSourceInfo: "",
			Catalog: "Adventure Works DW 2008R2",
			Format: "Tabular"
	
		}
		});
}

// Remove Dimension, Hierarchy and property name from the column header
// Remove square brackets from the column names.
function cleanColumnNames(cols){
	
	var new_col_names = new Array();
	for(var i = 0;i < cols.length;i++){
		s = cols[i].label
		if(s.search("MEMBER_CAPTION") > -1) {
			s = s.split(".")[2].replace(/[\[\]]/g, "");
			//console.log("Dimension: " + s);
		} 
		else { 
			s = s.split(".")[1].replace(/[\[\]]/g, "");
			//console.log("Measure: "  + s);
		}
		new_col_names[i] = s;
	}
	return new_col_names;	
}


function tabularToJSON() {
	var data;
	var text_data = '{ "dataset" : [ ';
	var rs = rowset(query);
	var headers = cleanColumnNames(rs.getFields()); 

	while (rs.hasMoreRows()) {
		s = '{';
		for(i = 0; i < headers.length;i++) {
			//console.log(headers[i] + ": " +  rs.fieldVal(rs.getFieldNames()[i]));
			s +=   '"' + headers[i] + '" : "' + rs.fieldVal(rs.getFieldNames()[i]) + '", ' ;
		}
		s = s.substring(0, s.length -2) + '}, ';
		text_data += s;
		rs.nextRow();
	}
	text_data = text_data.substring(0, text_data.length -2) + ' ] }';
	data = eval("(" + text_data + ")");
	console.log(data);
	return data;
	
}