How To – Basics of Xcelsius

[former_member209790]

How To – Basics of Xcelsius

This document is a basic How-To for BusinessObjects Xcelsius 2008.   It explains basic concepts of Core Xcelsius. This document can be helpful in creation of a simple Xcelsius application.

Using the Xcelsius interface

The Xcelsius interface is composed of several independent elements:

• Toolbars

• Components Browser

• Object Browser

• Property sheets

• Canvas

• Embedded Excel workbook

To create a new Xcelsius file

• From the File menu, click New and select one of the following options:
• To create a new Xcelsius file with an empty embedded Excel workbook, click New.
• To create a new Xcelsius file and import an Excel file into the embedded Excel workbook, click New with Spreadsheet, navigate to and select the Excel file in the Open dialog box and click Open.
• A new file is created in Xcelsius.

To add a component to the canvas

1. In the Components Browser, navigate to the appropriate component using one of the following methods:

• To browse using an accordion menu, click the Category tab and click the appropriate heading to expand it or scroll through the list.
• To browse using a folder structure, click the Tree tab and click the appropriate folder to expand it.
• To browse an alphabetical list of all components, click the List tab.

   2. Add the component to the canvas using one of the following methods:

• Click the component in the Components Browser, drag it to the appropriate location on the canvas, and release the mouse.
• Click the component in the Components Browser, and then click the appropriate location on the canvas.
• The component is added to the canvas, and the property sheets update to display context-sensitive options for configuration.

   3. To resize a component, click and drag the handles for the component to the appropriate proportions.

Using the Object Browser

Once you have added components to the canvas from the Components Browser, those components are listed in the Object Browser. You can use the Object Browser to select components so you can cut, copy, paste, delete, bring forward, move back, group, ungroup, and rename them by right-clicking to access the secondary menu. You can also place a check mark under the eye to hide the component so you can more easily work with other components. A check mark under the lock prevents changes from being applied to that component.

Using the Property sheets

Once a component has been added to the canvas, it must be configured using options on a Property sheet. You can access Property sheets by double-clicking on the component or right-clicking the component and selecting Properties from the context menu.

There are five Property sheets which allow you to specify options that change the functionality of the components on your canvas.

Working with Excel workbook

Xcelsius uses an Excel spreadsheet to serve as the mechanism for mapping data and formulas to the components in Xcelsius.

There are three key benefits that Microsoft Excel provides when embedded inside Xcelsius 2008:

    1.Data Model

You can enter highly aggregated data directly into the spreadsheet and visualize it, so there is no need for a database. You can also pull in highly aggregated data from external sources, including Web services, XML, and databases.

    2.Calculation Engine

In addition to Microsoft Excel having a very familiar and flexible formula language, it also provides a calculation engine.

     3.Eventing Model

When a cell changes, we get a data change event. This event is primarily used to instruct Excel to recalcuate cell values, but this data change event can be used to kick off other processes too.

The design of the Excel spreadsheet can hamper or facilitate success with your Xcelsius model.When setting up your Excel workbook, consider the following:

• Only one Excel workbook can be embedded into your Xcelsius model at a time. The workbook may contain multiple spreadsheets.

• Color-coding cells and ranges in your embedded Excel workbook can assist you in identifying the functions that specific cells serve in your model when binding components to ranges.

• If you plan to include interactivity as part of your model to perform "what-if" analysis, ensure that you have included the correct formulas in your Excel file.

To add content to the embedded Excel workbook

Do one of the following:

• Manually enter the content in the embedded Excel workbook.

• Copy the content from an existing spreadsheet and paste it into the embedded Excel workbook.

Using recommended Excel functions

Xcelsius supports most Excel functions. Several commonly used Excel functions are:

• vlookup

• hlookup

• match

• if

• if or

• if and

• concatenate

• index

Although supported, the sumif and countif functions may slow the performance of your Xcelsius Using a combination of the index and match functions can accomplish the same result more efficiently.Using a selector component with the Insert Filtered Rows option is more effcieint than using Excel's lookups functions.

Unsupported Excel functions

While Xcelsius does support most Excel functions, some functions are not supported. These functions are not supported in Xcelsius:

• is

• dlookup

These Excel features are not supported in Xcelsius:

• Macros

• Conditional formatting

• Pivot Tables

• Filters

• Excel Connectivity

• Excel Add-Ins

All of the Microsoft Excel functions (logic) are compiled into Adobe Flash at preview or export Certain Microsoft Excel functions perform better on smaller data sets (tens of rows) when compiled as Adobe Flash, so wherever possible avoid using the following functions on larger data sets. If you must access larger data sets, have the server or database perform the aggregation on the server side. Here is a list of the Microsoft Excel functions that you should try to avoid using unless your data is set small (tens of rows):

• SUMIF

• COUNTIF

• HLOOKUP

• VLOOKUP

Visualizing data with charts

There are multiple charts in Xcelsius. Charts can also use a secondary axis for visualizations.

Dynamic labels and Titles

When you define the properties of a component, you can manually enter values like the labels and titles for a component, or you can bind these elements to specific cells, rows, and columns in the source file.

Using data in ranges

A chart component displays data by series. You can manually create the series, or Xcelsius creates the series automatically by row or by column from a range of contiguous cells that you specify.

To insert and configure a chart component with a data range

1. Click and drag the appropriate chart type from the Components Browser to the canvas.
2. The restriction on using a data range is that all of the data that you want to represent in your chart must be located in a contiguous block in the Excel file. It can be a single column or row, or multiple columns or rows, but all of the data must be located together.
3. If you are dealing with multiple columns or rows, Xcelsius automatically creates series for the data based on the cells you select.
4. Click the button to the right of the field associated with the By Range option to activate the Select a range dialog box.
5. In the embedded workbook, select the range of cells that contains the appropriate values.Click OK.
6. Select the Data in Rows or Data in Columns option as appropriate.
7. Click Preview.
8. The chart now displays the actual values from your source file.

Using data in a series

If your data is not in a contiguous block, you will need to create each series manually. You can create the series by adding one row or column at a time. This is essentially the same process as selecting a data range, except that the data does not need to be in a contiguous block of cells.

Using Xcelsius components

Understanding input and output components

Within Xcelsius, components are intended for two purposes: displaying output and receiving input. Some components can be used for one purpose only, while others can both receive input and display output.

Output components like charts and gauges are intended to display data only. They can be used to show values or the results of formulas in a dynamic way (that is, you can hover your mouse over an output component to see the related values), but you cannot change the values in such a component directly. Any cell containing a formula is automatically considered to be output.

Input components like sliders and selectors, on the other hand, are intended to be bound to cells that contain the values that affect formulas. You can use these components to add interactivity to your models, which allows you to perform "what-if" analysis. When a user changes the value in one of the single value components, it can affect the output values of other components based on the formulas you use.

Understanding web connectivity component types

Web connectivity components link your model to external sources for dynamic content and up-to-the-minute data.

Slide Show

The Slide Show component is useful for displaying a large number of images. Unlike the normal Image Component, which requires that you first import the file, the External Slide Show component loads the image at the URL specified in the URL data source. By adding the URLs in the Excel file, you can select images and define the behavior to indicate the rate at which the slide show should progress.

URL Button

The URL Button component allows you to link to a URL within your model. When the button is clicked, the website specified opens in either the same window or a new window. You can use a single URL, or link to a dynamic cell and have the visual model dynamically drive what URL the end user can open. Note that a user's ability to link to this information requires Internet connectivity.

Publishing your visualization

Before you publish, you may want to take a snapshot of the model and view it in another format .From preview mode, you can publish to all available formats, or export the data to an external Excel workbook.

To preview a model

1. On the standard toolbar, click Preview.
2. To export a snapshot of your model to another format, perform the following steps:
3. From the File menu, select Snapshot and choose the appropriate menu command for the desired format.
4. Navigate to the appropriate location for the file.
5. In the File name field, enter a unique name for the file.
6. Click Save.
7. To exit preview mode, click Preview again.

Choosing the right output for your audience

The Role of Adobe Flash at Preview or Export Time

When you preview or export your visualization, all of the data, logic, and formatting from the embedded Microsoft Excel spreadsheet is compiled as Adobe Flash to produce a Shockwave Flash (SWF) file. When you distribute your Xcelsius visualization as a SWF, only Adobe Flash player (version 9 or later) is required to view the SWF .

Note: Microsoft Excel is only required at design time when building Xcelsius visualizations.

Once your model is complete, you can publish it in multiple formats. Regardless of the format you choose (for example, email, HTML, PowerPoint, and so on), a SWF file is embedded inside the output. To view the visualization, your audience needs both a Flash player and the program associated with that output. The format you choose depends on the data you are using and your intended audience. PDF and PPT desktop formats maintain the securityof your Business Objects Enterprise connection, if applicable.

Flash (SWF)

This option creates a SWF file that you can then embed in other files, run as a SWF in a Flash Player, or call from an HTML file. If you double-click the file from Windows Explorer and have a Flash Player installed, the SWF opens in your default web browser.

AIR

Adobe AIR is a cross-operating system runtime that lets developers combine HTML, Ajax, Adobe Flash®, and Flex technologies to deploy rich Internet applications (RIAs) on the desktop.Adobe AIR allows developers to use familiar tools such as Adobe Dreamweaver® CS3, Flex® Builder™ 3, Flash CS3 Professional, or any text editor to build their applications and easily deliver a single application installer that works across operating systems. A web browser enables a user to interact with content and applications typically located on a website on a server. Adobe AIR builds upon capabilities and technologies used in the browser to enable deployment of applications on the desktop. Adobe AIR complements the browser by providing users and developers with a choice about how to deliver and use applications built with web technologies.

HTML

This option creates a SWF file and an HTML file that calls that SWF file using the following

HTML code:

<HTML>

<HEAD>

<TITLE>FILENAME.swf</TITLE>

</HEAD>

<BODY>

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

codebase="http://fpdownload.adobe.com/pub/shockwave/cabs/flash/swflash.cab#version=9,0,0,0"

WIDTH="800" HEIGHT="600" id="myMovieName">

<PARAM NAME="movie" VALUE="FILENAME.swf">

<PARAM NAME="quality" VALUE="high">

<PARAM NAME="bgcolor" VALUE="#FFFFFF">

<PARAM NAME="play" VALUE="true">

<PARAM NAME="loop" VALUE="true">

<PARAM NAME=bgcolor VALUE="#FFFFFF">

<EMBED src="HTML_TEST.swf" quality=high bgcolor=#FFFFFF WIDTH="800" HEIGHT="600"

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

loop="true"

PLUGINSPAGE="http://www.adobe.com/shockwave/download/index.cgi?P1_Prod_Version=ShockwaveFlash">

</EMBED>

</OBJECT>

</BODY>

</HTML>

Business Objects Platform

This option generates a SWF file that can be stored in a BusinessObjects Enterprise or Crystal Reports Server folder. This option prompts you for your BusinessObjects Enterprise logon. Once you are logged on, the Save As dialog box appears to allow you to save the automatically generated SWF file to the Enterprise repository. You can then navigate to the SWF file in InfoView.

Note: Users of BusinessObjects Xcelsius Engage Server 2008, BusinessObjects Xcelsius Engage 2008 and Business Objects Xcelsius Present 2008 do not have this option.

PDF

This option creates a new Adobe PDF document with your model embedded in it, which is particularly useful for creating interactive reports.

PowerPoint

This option creates a new Microsoft PowerPoint presentation PPT file with your visualization embedded as an object on a slide inside the PPT file. You may copy the object to other slides or PPT files, or you can copy the entire slide to another PPT file.

Outlook

This option creates a new Microsoft Outlook email message with your model attached as a SWF file.

Word

This option creates a new Microsoft Word document and embeds a SWF of your visualization in the document. The embedded visualization is dynamic and interactive.

Adding Interactivity to a Visualization

Using selectors

Adding a selector

The selector serves as the primary method for users to interact with the Xcelsius models by toggling data or the visibility of charts. Selectors facilitate an intuitive end user interface that enables easy navigation, filtering, and drilling down into information.

Use selectors to:

• Transform static visuals into dynamic visual models

• Copy rows and columns of data within the Excel spreadsheet from a source location to a target destination that can be read by a chart or another output component

• Define parameters for ad hoc queries against a live data source or reporting applications

• Toggle different visuals within the dashboard to appear or disappear (coupled with dynamic visibility)

• Open child dashboard SWF files within a parent SWF

• Accomplish the same function as Excel lookup functions, but without the drop in performance.

Understanding data insertion

To select a particular piece of data, a selector copies data from a source range to a destination If a component is bound to the destination range, the data in the destination range will appear in that component.In the example, the bar graph displays data for one product type at a time. When the Computers button is pushed, the selector copies the data in the Computers row and inserts it into the destination row, which is highlighted in yellow. The bar chart reads the highlighted row, which displays data for sales of Computers. By selecting just the computers sales data, the bar chart is simpler to read than if you had included sales data for computers, laptops, and table PCs.

Using each data insertion type in selectors

To set up a destination range functionality for most selectors, you can use the Data Insertion section on the General property sheet.

To insert and configure a selector

Click and drag the appropriate selector from the Components Browser to the canvas.The Property sheets change to reflect the configurable properties for the selector. The available fields depend on the type of selector.

On the General property sheet, for the Title, Labels, and Display Data fields, if available,do one of the following:

• To manually enter a single value, type the value in the appropriate field.

• To manually enter multiple values for the Labels field, click the list icon to the right of the field to open the Labels dialog box, enter the names in the Label Name column for each numbered item. To add a label, enter the name in the Type a label field and click Add. To delete a label, click the X icon for the row. To re-order labels, click the up and down arrows.

To bind the values for the component to the values in specific cells, click the button to the right of each field to activate the Select a range dialog box, select the cell, and click OK.Under Data Insertion, do the following:

If applicable to the selector, in the Insertion Type drop-down list, select the appropriate type.

Tip: To view a help movie explaining how each insertion type works, click the info icon next to the drop-down list.

For the Source Data and Destination fields, do one of the following:

• To manually enter the values, type the values in the appropriate fields.

• To bind the values for the component to the values in specific cells, click the button to the right of each field to activate the Select a range dialog box, select the cell, and click OK.

Adding dynamic visibility

Understanding dynamic visibility

The dynamic visibility feature toggles the visibility of components based on criteria you define.Using dynamic visibility facilitates a rich user experience by simulating multiple levels of drill down and empowering the user to decide what information they would like to view based on their interaction with the model. By binding the status and key fields to different cells in your embedded Excel workbook you can use this logic: if the value in the Key cell matches the value in the Status cell, then the component will be visible. The value can be a number, a word, or any combination of characters. Most frequently, the status is bound to the field that is the destination range of the selector.

The following is an example of the workflow:

1. The selector populates the destination range.
2. The component (for example, a chart) looks in the destination range to determine the status value.
3. When the status changes, the chart decides if the status value matches a defined key (usually a label for the data that displays in the chart).
4. If the status matches the key, then the component is displayed.

          For example, the following is a model with a label based menu selector and two charts. When a label is selected, the selector inserts either Monthly Trend or Quarterly Trend into the destination cell. When the destination cell has a value of 'Monthly Trend', the Monthly Regional Sales line chart displays:

When the destination cell has a value of 'Quarterly Trend', the Quarterly Regional Sales bar chart displays.

Defining dynamic visibility display status

The following steps are involved in setting up and using dynamic visibility:

Step 1: Define user interaction

First, define the action that will toggle dynamic visibility.A selector is used to insert a value for each option into the target cell that will be used to determine whether the chart is visible or not.

Step 2: Define display status and display status key

Next, define the display status and display status key for each chart. The display status is the cell that determines whether the component is visible. This is the same cell where the selector component inserts the data that corresponds with the user's choice.The display status key is the value that needs to be in the cell to toggle dynamic visibility. This is the value that the selector inserts into the target cell for that item.

Step 3: Trigger dynamic visibility

When the user makes a selection, the selector copies a value into the target cell. The charts then check that cell, and the chart with the display status key that matches the value becomes visible.

Choosing dynamic visibility options

When you set up a component for dynamic visibility, there are also some entry effects available to really polish your presentations. In addition, you can specify the duration of the effect for all entry effects.

Using formulas

When dynamic visibility is based solely on data insertion, the dynamic visibility for a component should always be tied to a specific selectable item. The status cell can only contain a single value at any given time, and a unique value is used to represent each selectable item. You can use formulas in your Excel source files to extend the functionality of dynamic visibility so that you can define visibility for a component based on multiple selectable items. To do this,follow these three steps:

1. Define the insert in cells. You will still be using a selector to enable dynamic visibility. As with other models that use this functionality, you will specify the target cells for the selector. To support the use of a formula, one of the cells must contain information that is unique to each selectable item,such as its name. Make note of the cell reference for this location.
2. Create a formula In another cell in the Excel file, you will create a formula that looks up information in the cell where the unique value is located. The most common function to use in Excel is the IF formula, which you will construct as follows:

          IF(OR(cell=value, cell=value), value_if_true,value_if_false)

          Set up the parameters like this:

• Cell

Enter the location of the unique identifier for the selectable item.

• Value

Enter the value inserted in the cell for the selectable item (such as the name of the item).

You can either enter a specific value, or you can include a cell reference. The simplest technique is to enter 1.

• Value_if_false

Enter an alternate value, which will not trigger dynamic visibility. The simplest technique is to enter 0.

• Value_if_true

Enter a value that you will use to trigger dynamic visibility.

    3.     Set up dynamic visibility

For the component that will be dynamically visible, use the location of the formula, rather than the target cell for the selector, as the display status cell, and specify the Value_if_true value as the display status key. When the user makes a selection, the item is inserted into the target cell, which triggers the formula to determine whether the value meets the logical test or not. If it meets the logical test, the cell displays the Value_if_true value, thereby triggering dynamic visibility for the component.

Creating a Connected Visualization

Using Live Data Sources

Understanding the workflow required to use live data sources

Creating a connected model involves four steps:

When developing and deploying a connected model, you can push or pull data from the XML-compliant database directly to the SWF file. The SWF file still uses the self-contained business logic from the Excel file, but refreshes with live XML data. Using one of the available connectivity methods eliminates the need to manually refresh data in Excel or Xcelsius unless there are changes required to the logic or visual appearance of the model.

Using the Data Manager to add and configure connections

The Data Manager is a central place to add, configure and manage external connections in your visualization.

You can configure the following types of connections:

New Connections

• Query as a Web Service (QaaWS)

• Web Service Connections

• XML Data Connections

• Portal Data

• LiveCycle Data Services (LCDS)

Existing Connections

• Excel XML Maps

• Live Office connections

While you cannot add a new Excel XML Maps connection type or a Live Office Connection type using the Data Manager, the Data Manager automatically detects when these connection types are present in an Excel workbook that has been imported. Each connection has a Definition tab to organize the details of your connection. Some connection types also have a Usage tab to customize how your connection will load and refresh data. While most connection types will allow multiple connections, you may only have one connection for the Portal Data, Flash Variable, or Crystal Reports Data Consumer connection types.

To add a connection

1. From the menu bar, click Data.
2. From the Data menu, select Connections. The Data Manager dialog box appears.
3. Click Add and select a connection type from the list.The new connection will appear in the left hand pane.
4. Configure Definition and Usage options as required.

          To remove a connection

1. From the menu bar, click Data.
2. From the Data menu, select Connections.The Data Manager dialog box appears.
3. In the left hand pane, select the connection you would like to remove.The definition tab will appear in the right hand pane.
4. Click X to remove the connection.

Using the Connection Refresh button

You can use a Connection Refresh button to allow users to refresh data ondemand rather than relying on the refresh options set on the Usage tab of your connection. The Connection Refresh button is added and removed from the canvas like any other component, but has unique settings.

You can also use the Connection Refresh button to update the data connection according to the behavior of a trigger cell. Data can be refreshed when the trigger cell updates, when data in the trigger cell changes, or when the value of the trigger cell matches a fixed quantity or the value in another cell. Under any of these conditions, the visualization can trigger a web connection or connection refresh. This feature allows another action within the visualization, such as a List Box selection, to trigger the component - as if the component itself was clicked. The component is triggered according to the trigger behavior selected.

To configure a Connection Refresh button

1. From the General Properties sheet, type a value into the Label field or bind the Label field to a cell in the embedded Excel workbook by clicking the cell selector button.
2. From the Available Connections section of the General Properties sheet, check the box adjacent to each connection you would like to update when users press this Connection Refresh button.
3. From the Behavior Properties sheet, select the Common tab.
4. Bind the Trigger Cell field to a cell in the embedded Excel workbook.
5. Select one of the Trigger Cell options (for example, When Cell Updates, When Value Changes,or When Value Equals).If you choose When Value Equals, be sure to bind the field to a cell in the embedded Excel workbook.
6. Configure options for animation and appearance as desired.

Understanding the External Interface connection type

External Interface connections allow Xcelsius visualization developers to expose selected data ranges of the workbook. This creates a generic framework for getting data into and out of the SWF file of the published visualization. The External Interface also provides built-in eventing so that it is instantly recognized when a particular data source has changed (e.g. Slider component is dragged and updates a cell's data, etc) .

Add or edit the following connection parameters for External Interface connections:

Definition tab

• Range Name

Enter a name for the range, or click the cell selector to select a cell in the spreadsheet.

• Range Type

Select one of the following from the dropdown menu:

○ Cell – the data range consists of a single cell

○ Row/Column – the data range is in a single row or a single column

○ Table – the data range has more than one column or row

• Range

Click the cell selector button to select a range from the spreadsheet.

• Access

Select one of the following from the dropdown menu:

○ Read – an external application will be able to be read data in the range

○ Write – an external application will be able to be write data into the range

○ Read/Write – an external application will be able to be read data in the range and write data into the range

Connecting to SAP data

To consume SAP data in an Xcelsius visualization, you will need to use QaaWS to connect to the OLAP universe.

To set up QaaWS to connect to the OLAP cube to access SAP data in a Crystal Report

These are high level steps that describe the procedure required for using data from SAP in an Xcelsius visualization.

1. Create a Crystal Report.
2. Publish to your BusinessObjects Enterprise.
3. Create Live Office-enabled workbook that consumes Crystal Report data.
4. Save your Live Office-enabled workbook to your Enterprise.
5. Create a new visualization using Xcelsius.
6. Import data from the Live Office-enabled workbook that you saved to your Enterprise.
7. Create a new visualization using Xcelsius.
8. Create a new connection using the Data Manager.
9. Add and binds components to the data you imported in step 7.
10. Export and save to your Enterprise.