Use ‘Query as a Web Service’ and BOE XI 3.x to produce Live Xcelsius Visualizations

‘Query as a Web Service’ (QaaWS) is a client tool that was first made available with BusinessObjects XI R2. Now with XI 3.0 and 3.1, it is quickly becoming a commonly used client tool because of its ability to interface with Xcelsius 2008 and convert your static presentations into dynamic and live visualizations.

The following set of screen shots will walk you through the process of creating a simple application that integrates QaaWS and Xcelsius 2008 within BOE XI 3.1.

This example will include two QaaWS queries; one to provide the countries for our ListBox and a second query that will accept the selected country and provide the data for our chart. Let’s begin by opening QaaWS and creating a new Web Service query.

Launch the QaaWS Tool

Launch the QaaWS Tool

Give the Web Service a name and click ‘Next’.

Name the Web Service

Name the Web Service

Select your Universe then click ‘Next’. This example will use the ‘Island Resorts Marketing’ Universe that ships with BOE XI 3.1.

Select the Universe

Select the Universe

Only the Country field is added to the ‘Result Objects’ section. Also added is a ‘Filter Object’ (predefined in the Universe).

Creating a Web Service query

Creating a Web Service query

Sample data from the query is displayed after clicking ‘Next’. Click ‘Publish’ to proceed.

Preview of Web Service data

Preview of Web Service data

This screen will provide the URL for the WSDL that was just created. You can click the “To Clipboard” button to save the URL to the clipboard or simply copy the URL. The URL will be used later within Xcelsius to consume the Web Service data.

WSDL URL

WSDL URL

Now we’ll go back and create a new Web Service query to retrieve the Revenue data for the selected country. Just drag the country field into the ‘Filter Objects’ section then change the ‘Prompt text’ to “COUNTRY”. Be sure to set the type to ‘Prompt’. This will allow the Web Service query to receive data passed to it.

Setup Prompted Web Service query

Setup Prompted Web Service query

We are ready to open Xcelsius 2008 and setup our data connections. Click Data from the menu at the top then click Connections to open the Data Manager. Click the ‘Add’ drop-down then select ‘Query as a Web Service’ from the list of available connection types.

Add Data Connections

Add Data Connections

Adding Connections in the Data Manager

Adding Connections in the Data Manager

Paste the first URL that we generated in QaaWS into the WSDL URL box and click Import. This will make the connection to the data and allow Output Values to be inserted into your model by allowing the developer to point specific table and row values to cells in your model.
Be sure to color code these cells because we’ll be using them later.

Click the Country field in the ‘Output Values’ section and point the ‘Insert In’ cell range to F3:F9. (The first QaaWS connection was renamed to “Countries”).

This screenshot shows our second QaaWS connection (renamed “Country Data”) and shows the result of pasting our second URL into the WSDL URL, clicking Import, and seeing the fields available to be used.

WSDL with Input and Output Values

WSDL with Input and Output Values

In addition, we have a new “Input Values” object called “COUNTRY”. This was the ‘Prompt text’ value of the Country field that we added to our ‘Filter Objects’ section when building our second WSDL query. To tie the object to data being passed in, click COUNTRY then set it to ‘Read From’ cell A10 in our Xcelsius model.

WSDL with additional Output Values

WSDL with additional Output Values

Later we will set cell A10 as the Destination cell of our ListBox.

This slide shows how the Xcelsius model is color-coded with ‘Insert In’ values from our QaaWS WSDL fields.

Color-coded cells

Color-coded cells

Now add a ListBox to your Xcelsius model. Set the Labels and Source Data to F3:F9, set the Destination to cell A10 and set the Insertion Type to ‘Filtered Rows’.

Our next task is to make use of the data that we just brought into Xcelsius. The ‘Revenue’ column and a ‘Month name’ column should be used to create a chart with revenue by month. But first we will need to tie the two columns together. An excellent function to use in this situation is SUMIF().

To get the cells ready, label cells E12:P12 with month names in the same format as the month names coming into Xcelsius from QaaWS. In this case, enter the first three digits of each month name. It is time to use our SUMIF formula to search through our Month column to find the associated revenue value.

The syntax for this function is: =SUMIF($D$13:$D$71,E12$,$C$13:$C$71). Enter the formula in cell E11 and drag it all the way down the row to P11. We are ready to chart E11:P11 and use E12:P12 as the Category Labels.

Add a Column Chart to your model and set the range to cell E11 then click the ‘By Series’ heading. Click Series1 then set the Y Values to E11:P11, optionally change the Series1 name. Then set the Category Label values to cells E12:P12 or the Month names.

Now go back to the Data Connections. Click each ‘Query as a Web Service’ connection then click the Usage tab. On the “Countries” Web Service, check the ‘Refresh On Load’ checkbox. On the “Country Data” QaaWS connection, click the Usage tab then point the Trigger to cell A10. This will allow our prompted Web Service query to refresh every time our Xcelsius ListBox has a new Destination value.

Countries Connection - Refresh on Load

Countries Connection - Refresh on Load

Save your Xcelsius model then publish it to BusinessObjects Enterprise. In Qws_16 above, you can see that the BOE logon and password were in cells A1 and A2. If you tied your QaaWS ‘Input Values’ for login and password to these cells, your Xcelsius model will connect and refresh automatically without prompting the user to login. If you prefer that your Xcelsius model be more secure and require a valid BOE logon, be sure to leave these ‘Input Values’ blank.

Below is a screenshot of our finished model viewed within BOE XI 3.1.

Screenshot of Live Xcelsius model in BOE XI 3.1

Screenshot of Live Xcelsius model in BOE XI 3.1

Please feel free to email me if you have any questions or comments.  Thank you!  –  Jim Brogden

  • Kenny

    Thanks, this tutorial was very helpfull.

  • Lex

    Hi,

    Thanks nice tutorial.

    After trying connecting QaWS to XCelsisu I have a question:
    I have QaWS with 2 selection criteria, for example Creation date of document and customer number. When I put QaWS in XCelsius. Apart of login and password I only have 1 input value which is creation date. My question is Can we have input value morethan one input?

    Please help me.

    thanks,
    Lex

  • Andrey Kim

    Did you know about problem “Unable to load URL” when trying to impoirt WDSL link in Xcelsius-Connection interface? I have XI31 deployment, but my QaaWS doesn’t work 🙁

  • Oleg

    >Did you know about problem “Unable to load URL”

    I work on XI3.1 and the problim is the same – “Unable to load URL”
    I perform the same steps:
    1. Create QAAS, publish it, copy URL
    2. Start XCelsius, Connections, Web Services, Paste link….and this error

    WSDL link is:
    http://boapp1:8080/dswsbobje/qaawsservices/?def&cuid=ASrAk8fiyUNDonH1BAhRjSs

    Any ideas?

    • Rafeeq

      Click on import again if you get the message “Unable to load URL” after clicking on import first time and that should help to resolve the issue.

    • Morten

      I got the same error mesagge when I pasted the URL from QAWS into Xcelsius. I found out that I needed to add the domain into the URL manually (our network works like that) so the URL looks like this:

      http://boapp1.”domain”:8080/dswsbobje/qaawsservices/?def&cuid=ASrAk8fiyUNDonH1BAhRjSs

  • Paul

    Great article! Keep up the good work!

  • Gaurav

    Very informative and exhaustive article for the beginners….
    Good work…

  • Jason

    I am using Xcelsius 2008 (Xcelsius Engage 5.3.0.0) and I don’t have the option to choose ‘QaaWS’ from Data > Connections > Add. Any advice? Much thanks!

    • Morten

      Hi Jason,

      You need to upgrade to the Enterprise version. The engage/engage server version does not include QAAWS.

      Morten:-)

    • Jason,

      Not sure if you have answer to ur question yet. Xcelsius Engage Server has Flynet WSG option. Flynet in my opinion is better than QaaWS.

      Ladi

  • Paul

    I have set up a qaaws and can see data in the preview before publishing it. I have specified all of the read from and insert in cells for the login credentials and data rows.

    However, when I click on Close in the Data Manager nothing happens. No data is displayed.

    I’m sure there is a simple explanation. I am no doubt missing something obvious.

    Can someone please point me in the right direction?

    Thanks

    Paul

    • Morten

      Is data displayed when you preview the xcelsius file after closing the connection manager?

    • Paul

      A classic case of UTS (user too stupid) error. No need for anyone to reply.

    • padma

      I also have same issue. What did you do to see the data?

      • Hi Padma,

        Did you try previewing the file to see if data appears?

        Morten

  • Ugo

    Also for me, I cannot see the data after defined the connection!
    I’ve checked also refresh data before load components but nothing appear !!!

  • Jim Brogden

    Hi Guys,

    Data is only returned by a QaaWS WSDL during runtime. You will not see data during development time in Xcelsius.

    Color-coding your Xcelsius cells is very important to identify the cells that will hold the output values and are used to create Xcelsius visualizations. Please remember that the data is only returned during runtime when the QaaWS WSDL is invoked.

    Jim Brogden

  • Karim Jaffer

    Hi Guys,

    new to Xcelsius, so please bear with me.

    When previewing the file, I get the Country data in the list box to show but not data in the graph. I have followed the instructions above but not sure whats going on. Can anyone help?

    thanks in advance!

    • Vanessa Chong

      Karim, If no data in the graph, check in Xcelsius (Date -> Connections -> Contry Data QAAWS -> Usage tab -> make sure “Refresh before components are loaded” is selected.

  • Vanessa Chong

    I followed the instructions and everything seems working, I can see data in both List Box and Graph. However, I got error “Cannot Access External Data (promptValues is invalid)”. I have the corssdomain.xml file under the Tomcat Root directory, so not sure what else I missed. Any ideas? Thanks!

    • Vanessa Chong

      Problem solved. After I set the prompt to “optional” in QAAWS client, I no longer get the error.

  • Good Job, Vanessa! Glad you got it working. 🙂

    Jim
    jim.brogden@daugherty.com

  • naocisneros

    Hi, I have one QAWS connected to xcelsius with two refresh options or method of usage:
    (1) when the swf is loaded and (2) when a value of a cell changes.

    The connection does not shows any error at runtime, and refreshes the information “on load” but when the value of the cell changes there is not an update of the information. I had made tests with spreadsheet tables object to see the changes of the cell, and made a label of “loading message” but the loading message never appears and the cell is changing.

    I am working on a sp2 BOE XI, do I need to upgrade?

    my question here is, does it only support a single method of usage? why when the value changes does not trigger again the connection of the QAWS ?

    thanks!
    Naomi

  • Nelson

    Hi, i have followed the steps, but revenue is not displayed on the column chart…any idea?

    Please help! Thanks!

  • Keith

    On the second QaaWS (Country Data), there appear to be a range of spreadsheet entries needed as “Insert In:” values for:

    Country
    Sales_Person
    Revenue
    Month

    Could you please specify what they are?

    Thank you for your help in this matter.

  • pink

    I don’t see the data after I close the connection. Please help.

  • Kate

    Hi

    I’m doing something slightly different and after some advice. We have a web service(BI services) connection using QWAAS in Xcelsius. Our data updates every 5 minutes and so I want xcelsius to as well. Is there a well to do this, at the moment the refresh every…minutes isn’t working.

    thanks
    Kate

  • jaywant

    hi,

    One thing to concern as a question, can we have the latest Instance option in QaaWS, like in Live office and BI Services.

    If it is Possible then would be gratefull for the Help for briefing the steps to do as so.

    thanks