Altek Solutions Business Intelligence Blog

Keeping right in line with the other blogs that I have written about QaaWS (What is a QaaWS and Quick and Dirty Guide to Creating a QaaWS) I decided to put together a quick one and a dashboard to show how to leverage your Business Objects Universe to fuel your Xcelsius Dashboards. We can leverage the same universe that is utilized in Web Intelligence, Crystal Reports, LiveOffice, and Polestar right in Xcelsius which is as we like to call it…. Seeing one version of the Truth. A prerequisite of this blog would be to have your QaaWS already created. Check out below the steps to use QaaWS to drive your Xcelsius Dashboard.

Create a New Connection (QaaWS)

To create our QaaWS connection, in Xcelsius, we will need to go to Data > Connections to open the Data Manager.

Data Manager

Once in Data Manager we can click on Add > Query as a Web Service to add a new connection.

Add a New QaaWS Connection

* Note: There are many other connections that we can choose, but for the purposes of the blog we will just be using QaaWS. I will keep in mind the other connections for a future blog!

Configuring your New Connection

Once you add your new connection, there will be a couple of different aspects of it that will need to be configured and are as follows:

• Name
• Web Service URL
• Input/Output Values
• Refresh Options

Import your Web Service URL (WSDL)

After you specify a name for your connection, you can go ahead and import the WSDL that was generate from the QaaWS that we created using our Client Tool. To access the WSDL, log into QaaWS and navigate to the QaaWS you wish to import into Xcelsius and click To Clipboard.

QaaWS Copy to Clipboard

Once you have the WSDL URL you will copy and paste that into the connection and click the Import button. Importing the WSDL will allow you to see the Input and Output values of the QaaWS that you created and get you one step closer to showing your data in Xcelsius.

Import QaaWS

Map your Input and Ouput Values

After importing your WSDL, you now have to map your values to your excel model in Xcelsius. The input objects consist of a user id and password for Business Objects (if you leave these blank, the user will be prompted for a user id and password when running the dashboard) and any prompts that you might have defined in your QaaWS. At the bottom of the Input Values dialog box click on the icon next to read from to map your values. Same method applies for your output values, which are your objects being brought back by your QaaWS as well as some canned objects that are in every QaaWS connection. Click the icon next to insert in to map your output values to your excel model.

Input/Output Objects

Mapping Objects to Excel Model

* Note: When returning rows in a QaaWS, make sure that your Excel Options under File > Preferences > Excel Options is set for the number of rows that you will be returning with the QaaWS. The default number of rows returned is 512 and any after that will be truncated off the end. As a best practice, I try not to return more than 1500 rows per QaaWS. Anything over that degrades performance to the point where it loses its functionality as a dashboard.

Set your Refresh Options

Now it is time to turn on our data. With every QaaWS connection that you create, you have the option of setting up when the data gets refreshed. We have a couple of different options to choose when it comes to refreshing.

We can set up our dashboard to refresh on load, refresh every couple seconds, minutes, hours, and also set up to refresh on a trigger cell. For a trigger cell, as users click through the dashboard, the data will be updated with the value that the user has chosen. The connection can be set to refresh when the trigger cell changes or becomes a specific value. I am crossing my fingers for a dependency refresh in a future release! i.e. kick off QaaWS 2 when QaaWS 1 finishes loading. This can be done now with loading/idle messages but can be tedious and a huge undertaking when multiple QaaWS are being used.

Refresh Options

Set your Message Options

On the Usage tab we also have the option of setting up Messages. These messages will update a specific cell in your excel model to let the user know if the connection is getting refreshed or not.  You can hard code the loading/idle messages directly into the connection or you can pull it from your excel.

Two other options are enabling load cursor and disabling mouse input on load. Respectively, these options let the user know that the data is loading and will disable the user from clicking on the dashboard while it is loading. I like to use the loading/idle messages do this for me instead of these two options. With a background, label, and dynamic visibility, I can quickly create a loading screen that lets the user know the data is refreshing and also prohibits them from clicking on any other items on the dashboard.

Message Options

Map your Xcelsius Components

Now that we have data mapped into our excel, we need to go ahead and manipulate the data as needed and then map it to a component. Here is where we can utilize excel and its functions such as vlookup() and sumif(). Just a heads up, not all excel functions are currently supported with Xcelsius.

After getting our data into the right format we can go ahead and map the component to our data. In my the screen shot below, I am mapping a line chart to some data from the eFashion universe. Mapping for the component and mapping for our QaaWS is the same. Click the Title, Subtitle, Series, etc. mapping icon and select a range or cell in your excel model.

Mapping Xcelsius Components

Preview and Export your Dashboard

Got our components mapped and everything seems like it is ready to roll. We need to check to make sure that all out connections are working and our data is fueling our visualizations. To take a quick look at what your dashboard will look like, click on the Preview button in the quick links tool bar. The SWF will be generated and the dashboard will initialize. Click through and make sure everything is to user specifications.

Preview Mode

Once everything looks up to par, you can then go ahead and publish your dashboard. Go to File > Export  to choose the format you wish to use to export your dashboard. We are going to export it out as a .swf but you can also choose HTML, AIR, Business Objects Repository, etc. After exporting, you can now deliver this information to your end users!

Exporting to SWF

Wrap Up

With QaaWS and Xcelsius you can create custom dashboards that get your users the live information they need in the way that they want to see it. Check out the dashboard I created for this blog below and look for more Xcelsius blogs coming in the near future!

Related Posts:

1. Quick and Dirty Guide to Creating a QaaWS
2. Crystal Dashboard Design (Xcelsius 2008) Tips and Tricks ep1: Adding a Select All to your Selector
3. Crystal Dashboard Design (Xcelsius 2008): Tips and Tricks ep5: Parent and Child Dashboards from InfoView using Doc Download!
4. What is Query as a Web Service (QaaWS)?
5. How to Implement the Xcelsius Publishing Plugin