Using Xcelsius 2008 to communicate with Web Intelligence and Crystal Reports

Nov 19th, 2008
by Jim.

“Jim is Back”. In this post – Jim Brogden, a BI Consultant from Daugherty Business Solutions gives Step-by-Step Instructions on “How to use Xcelsius 2008 to communicate with Web Intelligence and Crystal Reports.”

Utilize the new Interportlet Communication (IPC) feature in Dashboard Builder 3.1 (and within BusinessObjects Enterprise XI 3.1) to pass data between Xcelsius 2008, Web Intelligence documents and Crystal Reports.

Through the use of “Content Linking”, this type of communication can now take place between the products listed above – all within the same Dashboard Builder portlet. The steps below will walk you through the incredibly simple process of linking an Xcelsius model (used as the source) to two Web Intelligence documents as targets.

Not only can Xcelsius be used as the source analytic but it can be the target as well. Content Linking and IPC allow for linking to take place between two or more Xcelsius models.

Linking Xcelsius and Web Intelligence

Let’s start by creating a prompted Web Intelligence report. In this example, I will be using the Islands Resort Marketing Universe (that comes packaged with BOE XI 3.1) to create the report. The screenshot below is from a simple Web Intelligence report with a filter added for the Country field and set to Prompt for input. The name of the filter (or text of the prompt) has been changed to “COUNTRY”.

Prompted Webi Report

Prompted Webi Report

In the next step, I will create a very simple Xcelsius model that features a ListBox loaded with countries. When one of the listbox items is selected, I will pass the filtered row value to the destination cell A10.

Defining the destination location

Defining the destination location

The next step is to setup the query parameters to allow Xcelsius to pass data to another reporting tool. From the main menu, click Data -> Connections. Click the Add drop-down and select the FS Command connection type.

In the Command box, enter the single word: queryparams. In the Parameters box, point to a cell that concatenates the lsS[Name] parameter with the value to be passed to it. Refer to the screenshot below.

A. In my example, the name of the prompted filter is COUNTRY. This means the parameter name that will be placed in your spreadsheet is lsSCOUNTRY.

B. Assign the destination cell to the parameter name, similar to: lsSCOUNTRY=A10.

C. Finally, to pass the parameter with the assigned value to the report, add an ampersand symbol to the beginning of the parameter name. &lsSCOUNTRY=

D. The screenshot below contains the following value in cell A9. “&lsSCOUNTRY=”&A10. This formula concatenates a literal parameter name to the dynamic value selected in the Xcelsius model. The assignment of the value is made within the Web Intelligence document.

Setting query_params

Setting query_params

It’s now time to add both of these report objects to a dashboard – or more specifically, to a Dashboard Builder 3.1 portlet. Either select (Create Corporate Dashboard) or mouse-over Corporate Dashboards to select an existing dashboard to edit.

Create or edit the dashboard

Create or edit the dashboard

I will be editing an existing dashboard. If you have the proper permissions, you will see Edit Dashboard in the upper-right corner of the screen when a dashboard has been selected.

Edit the dashboard

Edit the dashboard

If you select Edit Dashboard, the Analytic Toolbox will open and appear on the top of your screen. The Corporate Analytics folder contains all of your published BOE XI 3.1 report objects (that you have permission to access).

You’ll see below that a Web Intelligence report called “Country – prompted” and an Xcelsius dashboard called “ListBox” have been circled. These are the two objects used in our example.

Once “ListBox” and “Country – prompted” have been placed on the dashboard, click Content Linking to enable Interportlet Communication.

Adding objects to the dashboard

Adding objects to the dashboard

Below is the screen you will see after clicking Content Linking.

A drop-down menu is provided to allow the Source Analytic to be selected and the remaining items become available in the Target Analytics section. Two identical Web Intelligence documents were added to the dashboard, COU and Country – prompted.

Both Web Intelligence documents have been selected as the Target Analytics. By setting up Content Linking, every time an item in the Xcelsius listbox is selected, the value is passed to the prompted Web Intelligence filter as “&lsSCOUNTRY=[clicked item]”. After receiving the parameter(s), the Web Intelligence documents (or Crystal Reports) are immediately refreshed.

Content Linking definition

Content Linking definition

The screenshot below shows both Web Intelligence documents being refreshed at the same time. The event will take place every time a new country from the Xcelsius ListBox is clicked.

An Xcelsius selection kicks-off the Webi refreshes

An Xcelsius selection kicks-off the Webi refreshes

Both reports are refreshed and contain France data. If the Web Intelligence reports were created from an aggregate universe or highly optimized fact table, the reports will refresh very quickly ensuring user interest and acceptance.

Both Webi reports have been refreshed

Both Webi reports have been refreshed

Please feel free to contact me with any questions or recommendations.  Thank you!

Click here to see Jim’s Profile.

Related Posts:

Posted in: Articles, Crystal Reports, Training, Xcelsius 2008.
Tagged: · ·

←
→
  • John S.

    Our company has just purchased the BOBJ suite with the intent of building manager dashboards and at this point we’re just getting off the ground, so some of these questions probably do nothing more the demonstrate my ignorance at this point.

    I’m guessing the scenario of selecting just a Country in Xcelsius to pass to 2 WebI rpts is just to demonstrate the IPC functionality, rather than trying to suggest this use Xcelsius as a tool for such a simple input mechanism. I could see where it could make sense to do this where you were using Xcelsius to pass a more complicated set of input values.

    I’m also curious to get a sense of where Dashboard Builder is going. I had sat thru a webinar that tried to explain what types of analytics were approriate for Xcelsius and where Dashboard Builder was appropriate, but had also seen information that suggested that Xcelsius was to be the tool to use going forward. However, this IPC seems like something that clearly maskes Dashboard Builder a major component of creating analytic solutions. So what do you see the roles of these two products to be going forward?

  • Bob Z.

    I’m not seeing a critical step in the example:

    The FS Command under USAGE needed a trigger cell when I tried this example. I bounded the “When Value Changes” to A10.

    Any thoughts on how we can get the default selected value in the list box to be selected on the dashboard page load?

  • Lauriane

    Bonjour
    J’ai suivi attentivement ce tuto très intéressant.
    Ma question est la suivante : peut-on réaliser la même chose en version BO XI R2 ? Précisément, j’en suis arrivée à l’étape du “Content linking” (lien contenu), que je ne trouve pas en version XI R2.
    Merci

  • Translated in English:

    “Hello
    I followed this tutorial very interesting.
    My question is: can we achieve the same thing in BO XI R2 version? Specifically, I have come to the stage of “Content linking” (link) that I do not XIR2 version.
    Thank you”

    Regards

    • Jim Smith

      No, you can’t do this with BO XI R2. This IPC capability that includes the “Content Linking” was added with BOE XI 3.1. So if you want to use it you have to upgrade to BOE XI 3.1.

  • Ram

    I have done everything that is mentioned.

    My prompt in the webI doc is D

    So I placed &lsSD= in the parameters cell.

    I then placed it in the dashboard and I did content linking.

    I just am not able to link the two reports.

    • Jim Brogden

      Ram,

      You need to add the following formula to a cell in Excel:
      =“&lsSD=”&A10

      This is assuming that A10 is the destination cell that your Xcelsius component will insert data into.

      Then in the Data Manager and your FS Command connection, point to the cell that includes: =“&lsSD=”&A10

      Also be sure to type the words queryparams in the Command box. If you add this formula in Excel: =“&lsSD=”&A10, you will see: &lsSD=. That is all you will see. But when the the Xcelsius document is executed and an item is selected from your Xcelsius component (that has a destination cell of A10), the cell will now read &lsSD=TEXAS (or something like that).

      Test it! Create a label and point to the cell that contains a formula like this: =“&lsSD=”&A10

      Hope that helps,
      Jim

  • Sam Maru

    Hello, This is really useful. Is there any limitation on the number of parameters passed.? Why do we have the name as “Queryparams”. Is there any specific reason. Thanks.

  • Harry Pinto

    i have on scenario to link a webi in webi and analytic to webi.

    1) what are the detail step
    2) using prompt and non prompt.
    3) when i use open document and open analytic links
    4) what are the steps to open documents links, where should i place this links in webi or analystic

  • ken

    hei..why i cann’t link the xcelsius value to webi. when i click the swf file,webi file didn’t do anything. Its like xcelsius and webi doesn’t have link.

    if i test put label component in xcelsius that access to the cell with function =”&lsSCOUNTRY=”&c2, it will show the result lsSCOUNTRY=France (if i clicked France)

    in xcelsius:
    – in data connection i create fs command
    name : Connection

    – i write =”&lsSCOUNTRY=”&c2

  • ken

    hei..why i cann’t link the xcelsius value to webi. when i click the swf file,webi file didn’t do anything. Its like xcelsius and webi doesn’t have link.

    if i test put label component in xcelsius that access to the cell with function =”&lsSCOUNTRY=”&c2, it will show the result lsSCOUNTRY=France (if i clicked France)

    in xcelsius:
    – in data connection i create fs command
    name : Connection
    command : queryparams
    parameters: Sheet1!$D$2 (excel cell that contain =”&lsSCOUNTRY=”&c2)
    – i write =”&lsSCOUNTRY=”&c2

    in webi:
    -create filter using country

    in dashboard:
    -set the content linking

    • Balu

      Hi Ken,

      is this working fine??

      i am getting some problem

      pls let me knw ..

      cheers,

  • Hello. Great job, if I wasn’t so busy with my school work I read your entire site. Thanks!

  • Elkin

    Thanks a lot,

    I was trying this for 6 hours, and I was only able to do it when I read your article. The Dashboard Builder Guide is not that clear on what should be put in the parameter option of the FS Command.

    Thanks,

    Elkin

  • Balu

    hi Jim,

    i did the same thing wht ever u mentioned,but im not getting ..the value not passing..i.e my report not been getting refresh…could you pls ans my issue pls..

    1.do i need to save report as “refresh on open”?

    in xcelsius.. created year in cell C1- =”&lsSYEAR=”&B1 . in cell B1 i dint mentioned any ..is it right…

    pls give me reply..

    thanks in advance

    Cheers,
    Balu

  • Mallik

    Hi,

    How far would the prformance of a dash board be affected, say if i am passing 50-60 parameters from xcelcius dashboard to a WEBI report.

    Assume that I have very limited data exchange between other components on dashboard.

  • Swati

    Hi Jim,

    Thank you for the detailed steps on using Content linking to link Xcelsius as a source with Web intelligence as target.I tried it out and it worked as expected! 🙂

    Please let me know if there is any way to implement Content linking in the reverse manner i.e. using Web intelligence as the Source and Xcelsius as the target. Have searched on forums and the PDFs but all state Content linking using Xcelsius as the source and Web intelligence as the target.

    What I am trying to achieve is the below:
    To be able to pass a parameter(s) from the Web intelligence report for which we would be using objects from the universe & accordingly the visualisation (Eg. charts) should change in Xcelsius.
    The idea is to be able to use data coming from an existing universe, passing this value as a parameter to Xcelsius(for which QaaWS queries are already written & pulled into Xcelsius) to be able to achieve the visualisations which Xcelsius offers.
    In short, to do away with writing Excel formulae in Xcelsius for data coming through QaaWS.

    Pls let me know if you have any idea about the above.

    Thanks,
    Swati

    • Katy Nelson

      To pass a value from the Universe to Xcelsius you need to create a query as a web service data connection in Xcelsius. That should allow you to pass a value into Xcelsius

    • Hi Daniel, I got Really Impressed witht eh Dashboard Design and Implementation for your Score cards, whihc makes me Curious to know how you have manegad the Dataflow in the Dashboards. I’ll be grateful to you if you can send me the Xlfs for the Dashboards.Thanks –

  • Hello,

    Could you please help me with values passing from one webi report to another webi report using IPC method (Content Linking method). I have tried with Xcelsius to webi, which works fine. I would like to know from webi to webi using content linking method. Thanks in Advance.

  • Miriam Graf

    I also spent over 4 hours trying to get this working with no success until I stumbled across your article. Worked like a charm.
    Is this written somewhere in the BOBJ documentation? I couldn’t find it.

  • David C.

    I followed the directions, but when I select a value from the Xcelsius model, the webi report does not refresh. We are on BO XI 3.1 (FP 2.5) and Xcelsius 2008.

    Thank you.

  • Raj

    Hi Jim,

    We followed the steps outlined in your blog, when we click on any of the countries then we are getting 404-/ull Error message.

    Do you know why this might be happening ?

    we are on BO XI 3.1 (FP 2.5) Xcelsius 2008.

    Thanks
    Raj

  • sahar

    hi

    I type “&lsSD=”&A10 in excel cell , but I get error and dont see &lsSD=. plz help me if i need another soft ware.

    tnaks
    sahar

  • Katy Nelson

    Thanks for the tutorial. When I select a value from Xcelsius the Webi report refreshes but it doesn’t use the parameter that I’ve just passed. Is there any way I can monitor what is being passed and what is being recieved (beyond taking a snapshot of the excel data)?

    thanks
    katy

  • emerson

    hi i would want know how you can have several prompt in xcelsius with a connection live office with webi.
    thank you

  • niru

    Hi All,

    i tried this but i am unable to get. Webi report is getting refreshed but the selected parameter is not passing to webi report. i have taken City as pompt from efashion database.

    Please anyone can provide me the step by step processes for efashion univese
    this is the urgent requiremet.

    Thanks in advance

  • niru

    Hi All,

    Please sent the .xlf file who were succeeded to open the webi report from dashboard.
    mailid: gnr2k11@gmail.com

    Thanks,

  • Pingback: Miriam graf | Groveplanet()