<%@ Language="JavaScript" %>
_
_
Analysis Services Cube Browser
<%
//
// SERVER SCRIPT
//
// Get and open the file containing the MDX XML
var i, j, k;
var strSource, strMemberSource, strDrillSource, strWithMembers, bGoodXML;
var sLHPane; // Defines which menu to show in the left hand pane: Field List or List of Reports
var con, cat, cst, rst, server, database, cube, reconnect, rowNonEmpty, colNonEmpty, showNoActions;
var page; // Distinguishes whether the page is a query, member, or drill page.
var sDB, sServer, sCube, sCaption, sAvailableCubes;
var QueryMDX = new Array();
var QueryCaption = new Array();
var fatalErr = false;
// Load the XML doc.
bGoodXML = false;
try {
var xmlDoc = new ActiveXObject("Microsoft.XMLDOM");
if (xmlDoc.load(Server.MapPath("CubeInfo.xml"))==true) {
bGoodXML = true;
}
} catch (exception) {
bGoodXML = false;
}
// Put all the available cubes defined in the XML doc in a place where the client script can
// get at them.
if (!fatalErr && bGoodXML) {
var xCubeNodes = xmlDoc.selectSingleNode("CUBEDEF").selectNodes("//CUBE");
sAvailableCubes = "";
for (i =0; i 0 ) {sAvailableCubes += ";;";}
sAvailableCubes += sCaption + "\\" + sServer + "\\" + sDB + "\\" + sCube;
}
}
// The default menu shown is the Field List.
sLHPane = "FieldList"
// If the form has no textfields, it is being requested for the first time.
if (!fatalErr && Request.Form.Count > 0) {
server = Request.Form("txtServer").Item;
database = Request.Form("txtDatabase").Item;
cube = Request.Form("txtCube").Item;
reconnect = Request.Form("txtReconnect").Item; // Force a reconnection or leave the existing one alone?
rowNonEmpty = Request.Form("txtRowNonEmpty").Item; // Non empty rows?
colNonEmpty = Request.Form("txtColNonEmpty").Item; // Non empty columns?
showNoActions = Request.Form("txtNoActions").Item; // Do we show actions or not?
sLHPane = Request.Form("txtLHPane").Item; // Defines whether the LHP is reports or fields.
if (bGoodXML==true) {
xmlDoc.async = false; // Don't try to load the xml file asynchronously.// var xReportNodes = xmlDoc.selectSingleNode("CUBEDEF").selectNodes("//REPORT"); ALL REPORTS
var xReportNodes = xmlDoc.selectSingleNode("CUBEDEF").selectNodes("SERVER[NAME $ieq$ '" + server + "']/DATABASE[NAME $ieq$ '" + database + "']/CUBE[NAME $ieq$ '" + cube + "']/REPORT");
for (i =0; i 0 && database.length > 0 && cube.length > 0 ) {
con.ConnectionString = "Location=" + server + ";Initial Catalog=" + database + ";Provider=msolap.2;";
con.ConnectionString = "Provider=MSOLAP.2;Persist Security Info=False;User ID=IUSR_BARNEY;Data Source=barney;Initial Catalog=SanPablo;Client Cache Size=25;Auto Synch Period=10000"
//"Provider=MSOLAP.2;Password=;Persist Security Info=True;User ID=IUSR_BARNEY;Data Source=barney;Initial Catalog=SanPablo;Client Cache Size=25;Auto Synch Period=10000";
//aux = con.ConnectionString;
//alert(aux);
try {
con.Open();
} catch(exception) {
Response.Write("
Connection Failed
");
Response.Write("
" + exception.description + "
Check your connection information");
Response.Write("
Server:" + server + "
Database:" + database+ "
Cube:" + cube);
server = ""; // Resetting parameters so a refresh relaunches the connection.
database = "";
cube = "";
}
}
}
}
// Reset the forced reconnection flag.
reconnect = "false";
if (!fatalErr && con.State!=0) {
cat.ActiveConnection = con;
cst.ActiveConnection = con;
// Dertermine if the regular grid is to be displayed, or if
// the user is showing a list of members or drilling through.
if (Request.Form("txtMemberMDX").Item.length == 0 && Request.Form("txtDrillMDX").Item.length == 0) {
// Set the page type. This is used by the client to figure out how to handle the page.
page = "query";
//
// 2. EXECUTE THE MDX
//
strMDX = Request.Form("txtMDXout").Item;
cst.Source = strMDX + " CELL PROPERTIES FORMATTED_VALUE, BACK_COLOR, FORE_COLOR";
try {
cst.Open();
} catch(exception) {
Response.Write("
MDX Failed
");
Response.Write("
" + exception.description + "
");
Response.Write("
Detailed MDX Statement follows:
");
Response.Write(strMDX);
page = "error"; // Set the page type to error.
con.Close(); // Close the connection to the database.
}
if (cst.State !=0) {
//
// 3. RECREATE THE MDX
//
// Create our own MDX from the cellset in a format we understand and can manipulate further.
var MDXAxisClause = new Array("","");
var MDXFilterClause = "";
var MDXDimClause = new Array("","");
for (i=0;i<2;i++) {
// Initialise the clauses for each dimension.
for (k=0; k 0) MDXAxisClause[i] += "*";
MDXAxisClause[i] += "{" + MDXDimClause[k] + "}";
}
if (cst.Axes(i).DimensionCount > 1) { // If there are crossjoins, need extra braces.
MDXAxisClause[i] = "{" + MDXAxisClause[i] + "}";
}
if (MDXAxisClause[i].length == 0) MDXAxisClause[i] = "{}";
}
// Reuse the filter clause. It is found by starting with the characters 'WHERE (['
MDXFilterClause = "";
if (strMDX.search(/WHERE\s+\(\[/i) >=0) {
MDXFilterClause = strMDX.substr(strMDX.search(/WHERE\s+\(\[/i)+ 6);
}
// Reassemble the complete MDX string;
strSource="SELECT\n" + MDXAxisClause[1] + "\nON ROWS,\n" + MDXAxisClause[0] + "\nON COLUMNS\nFROM [" + cube + "]";
if (MDXFilterClause.length > 0) {
strSource+= " \nWHERE " + MDXFilterClause;
}
i=0;
strWithMembers = "";
while (i
");
Response.Write("
Reports
\n");
Response.Write("
Field List
\n");
Response.Write("
");
Response.Write("
\n");
Response.Write("
\n");
for (i=0; i
" + sFolderCaption + "
\n");
Response.Write("
\n");
if (Hierarchies(j).UniqueName != "[Measures]") {
for (k=0;k
\n\n");
}
}
}
// Get the named sets
try {
rst = con.OpenSchema(43, Array(database, null, cube));
if (rst.RecordCount>0) {
// Write out the folder name for Named Sets
Response.Write("
Named Sets
\n");
Response.Write("
\n");
while (! rst.EOF) {
// Write out the named set if the function CreateVirtualDimension is not included - this was the means
// by which 7.0 cubes create virtual dimensions and these should not be included twice.
if (rst.fields.Item("EXPRESSION").value.search("CreateVirtualDimension") < 0) {
// The funny replace expression on the dimensions is to add square brackets for
// named sets with multiple dimensions.
Response.Write("
" + rst.fields.Item("SET_NAME") + "
\n");
}
rst.movenext;
}
Response.Write("
\n
\n");
}
rst = null;
} catch(exception) {
// Reserved. Don't report the error for now. Most likely caused by a 7.0/2000 version problem.
}
Response.Write("
\n");
Response.Write("
\n");
// Get the predefined queries against this cube
Response.Write("
\n");
// Loop through the reports. The array of queries was populated earlier from
// the xml file. Just loop through them. When clicked, the associated MDX
// is executed.
for (i =0; i<=QueryMDX.length-1; i++) {
Response.Write("
" + QueryCaption[i] + "
");
}
if (QueryMDX.length==0) {
Response.Write("
None defined
");
if (sLHPane=="") sLHPane = "FieldList";
// If there are predefined queries and the LHPane hasn't been defined, show the report pane.
} else if (sLHPane=="") sLHPane = "ReportList";
Response.Write("
\n");
//
// 5. Create the Grid object.
//
Response.Write("
\n");
}
// Here, we execute the member MDX to show a list of members the user selects from.
} else if (Request.Form("txtMemberMDX").Item.length != "") {
// Set the page type. This is used by the client to figure out how to handle the page.
page = "memberSelection";
// The original MDX is unchanged. Record it to be passed back to the client
// to be reused when the member has been selected.
strSource = Request.Form("txtMDXout").Item;
// Get the member selection MDX and open the cellset.
cst.Source = Request.Form("txtMemberMDX").Item;
cst.Open();
// Write out an attractive Header.
Response.Write("
Select a member from the list to filter query results
");
Response.Write("
");
if (cst.Axes(1).Positions.Count>0 && cst.Axes(1).Positions(0).Members.Count>0) {
var str = cst.Axes(1).Positions(0).Members(0).LevelName;
Response.Write("" + str.slice(1,str.search(/\]/)) + "");
}
if (cst.Axes(1).Positions.Count > 5000) {
Response.Write(" (Only the first 5000 members of a level can be displayed.)");
}
Response.Write("
");
// Populate a listbox with the members in the cellset.
if (cst.Source.search(/\[Measures\]/i)>=0) {
Response.Write("
![](\"images/report.gif\")
" + QueryCaption[i] + "