Result Data Newsletter - www.resultdata.com

Look What's Inside?

by: Aaron Zecman, BI Consultant, CRCP

Looking to enhance your Web Intelligence document? How might you do that, you ask? Look what’s able to be embedded inside your Web Intelligence document. That’s right, it is an Xcelsius gauge.

The first step is working on the Xcelsius model that we will use to import into the Xcelsius document.

Then open up Crystal Xcelsius and drag a single value gauge onto the canvas and import the Excel data model.

Next set the properties of the gauge to the following values.

General – Link To Cell – C17

Behaviors – Minimum Value – C13

Behaviors – Maximum Value – C16

Alerts Tab – Enable Alerts – Checked

Alerts Tab – Value Alerts – C14:C15

Appearance – Show Limit – Checked

Now save your document. It is time to make the gauge dynamic. Go to File > Export Settings. Select the Use Flash Variables, CSV format and Generate Sample Code. Now click on Define Variables. Now define all the variables corresponding to the spreadsheet and values as follows (except for currentValue which you will need to set to E17.

Click OK twice and then save the file.

Now create a folder inside the business objects tomcat directory:

..\tomcat\webapps\businessobjects\enterprise115\desktoplaunch\webix

Inside that directory make a file called webix.jsp with the following code.

response.setHeader("Cache-Control", "no-cache, post-check=0, pre-check=0");

response.setHeader("Pragma", "no-cache");

response.setHeader("Expires", "0");

<HTML>

<HEAD>

<TITLE>gauge</TITLE>

</HEAD>

<BODY>

// default values

String defaultLowerThreshold;

String defaultUpperThreshold;

String defaultLowerBoundary;

String defaultUpperBoundary;

// decide on what to load (Bar or Gauge?)

// and set the appropriate defaults

String graphMode = "gauge.swf";

defaultLowerThreshold="0.3";

defaultUpperThreshold="0.7";

defaultLowerBoundary="0";

defaultUpperBoundary="1";

// initialize values from parameters

String lowerThreshold = request.getParameter("lowerThreshold");

String middleThreshold = request.getParameter("middleThreshold");

String upperThreshold = request.getParameter("upperThreshold");

String currentValue = request.getParameter("currentValue");

String lowerBoundary = request.getParameter("lowerBoundary");

String upperBoundary = request.getParameter("upperBoundary");

// err handling

String errMsg = "";

// validate values

if( currentValue == null)

{

// let's make sure there is a current value, if it is empty, set it to zero

currentValue = "0";

}

if( lowerThreshold == null || lowerThreshold.equals(""))

{

// make sure lower threshold is set, if it is null, assign a default value

lowerThreshold = defaultLowerThreshold;

}

if( upperThreshold == null || upperThreshold.equals(""))

{

// make sure upper threshold is set, if it is null, assign a default value

upperThreshold = defaultUpperThreshold;

}

// if middleThreshold wasn't passed in

if( middleThreshold == null || middleThreshold.equals(""))

{

// if middle is not set, we'll omit the lower boundary and offset it

// set lower to zero and user middle as lower

middleThreshold = lowerThreshold;

lowerThreshold = "-1";

}

if( lowerBoundary == null || lowerBoundary.equals(""))

{

// make sure lower boundary is set, if it is null, assign a default value

lowerBoundary = defaultLowerBoundary;

}

if( upperBoundary == null || upperBoundary.equals(""))

{

// make sure upper boundary is set, if it is null, assign a default value

upperBoundary = defaultUpperBoundary;

}

try

{

// make sure the value is less than the boundaries, if the value is lower than the lower value or higher

// higher than the upper boundary, we'll have to adjust the boundary

double iValue = Double.valueOf(currentValue).doubleValue();

double iUpperBoundary = Double.valueOf(upperBoundary).doubleValue();

double iLowerBoundary = Double.valueOf(lowerBoundary).doubleValue();

if( iValue > iUpperBoundary)

upperBoundary = currentValue;

else if ( iValue < iLowerBoundary)

lowerBoundary = currentValue;

}

catch(NumberFormatException e)

{

errMsg = e.getMessage();

}

// prepare the string buffer

StringBuffer params = new StringBuffer();

params.append("lowerThreshold=");

params.append(lowerThreshold);

params.append("&middleThreshold=");

params.append(middleThreshold);

params.append("&upperThreshold=");

params.append(upperThreshold);

params.append("&currentValue=");

params.append(currentValue);

params.append("&lowerBoundary=");

params.append(lowerBoundary);

params.append("&upperBoundary=");

params.append(upperBoundary);

<OBJECT classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000"

codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=7,0,19,0"

WIDTH="100%" HEIGHT="100%" id="myMovieName">

<EMBED src="<%=graphMode%>" quality=high bgcolor=#FFFFFF WIDTH="100%" HEIGHT="100%"

FlashVars="<%= params.toString()%>"

NAME="myMovieName" ALIGN="" TYPE="application/x-shockwave-flash"

PLUGINSPAGE="http://www.macromedia.com/go/getflashplayer">

</EMBED>

</OBJECT>

</BODY>

</HTML>

Now once you have a percentage inside your Web Intelligence document. Insert a column on the right side of your document and in the data column and in the formula bar type the following:

=”<iframe> height=’150px’ width=’150px’ frameborder=’0’ src=’../../webix/webix.jsp?lowerThreshold=0.20&middleThreshold=0.80&upperThreshold=0.90&currentValue=” + [Percent field] + “’></iframe>”

Now this is not the only thing. You will need to modify the field to display as HTML. You can do this by going to the properties tab, underneath the Display section you will find the “Read cell contact as” and change it to HTML.

Save the Web Intelligence document and then view it in either the DHTML or Interactive viewer.

Viola! An Xcelsius file inside a Web Intelligence document.

Go to Top | Return to Newsletter Contents