Posts Tagged ‘web services’

Crystal Reports and Web Services

May 19th, 2009 21 comments

Quick Tutorial

Service Oriented Architectures are amazing.  Now more than ever companies are leveraging freely available web services to add value to their internal data.  One of the most common ways on combining data today is via Google Maps. A great example of this is housingmaps.comHousingmaps combines the power of Google Maps and CraigsList to allow you to view available housing in your area together with the power of Google Map navigation.

Today, all tools from SAP BusinessObjects can consume web services.  The most popular reporting tool in the BusinessObjects platform is Crystal Reports.  With the report creation wizard, we can be consuming a web service is seconds.  Let’s walk through it step by step.  We will connect to a web service that will provide us driving directions from Point A to Point B.

Step by Step

  1. Launch Crystal Reports 2008
  2. From the Start Page, choose Report wizard, this will launch the Standard Report Creation
  3. Next expand Create New Connection, and scroll down to XML and Web Services and expand the folder.
  4. The XML and Web Services data source  dialog box will appear.  Choose Use Web Service Data Source and choose Next>.
  5. In our case, we are going to use an HTTP Web Service, so choose Use HTTP(S) WSDL and type in: and choose Next>.
  6. This Web Service does not require a UserID/Password, so choose Next>.
  7. After a moment or two, the Web Service, Port and Method screen should appear.  You should see: driving, drivingSoap and GetDirections in the Service, Port and Method drop downs respectively.  (If this does not work, there may be a firewall issue at your location.  Check with your local IT.)  Choose Finish.
  8. After choosing Finish, you will see a ResultSet box under the data source.  (You may need to expand it).  Move the box over to the Selected Tables side by choosing the [>] button.   When you do, you will be prompted to enter some parameters.  So far so good.
  9. There are four input parameters to this:
      fromAddress – the starting address, e.g. 3734 Elvis Presley Boulevard Memphis, TN 38186
      toAddress – the destination address, e.g. 1600 Pennsylvania Ave NW, Washington, DC 20500
      distanceUnit – miles or km, default: miles
      expresswayEnabled – true or false, default:true
  10. After entering the values choose OK.
  11. Complete the Wizard by either choosing Finished or by walking through the remaining screens.
  12. Once the fields are added to the report, you should the directs Elvis would need to take to drive to the White House.

Here is your finished report:

Leveraging Web Services from within SAP BusinessObjects couldn’t be easier.  If you are interested in seeing the same types of capabilities from within other BusinessObjects tools drop me a line and I’ll add it to the list.  Enjoy.

«Good BI»

Xcelsius 2008 and Refresh On Load

September 29th, 2008 4 comments

Are you used to the new Xcelsius 2008 yet?  The release of SP1 has definately been a big improvement.  Like many others, I struggled with the new and improved Xcelsius.  There were a few too many quirks for me and until the release of SP1, I went back to Xcelsius 4.5.

Besides the fact that there are several “little things” that work differently between Xcelsius 2008 and 4.5, I did notice a couple of bigger things.  One in particular was the Refresh on Load feature.  I found that there was a big difference between setting the data connection to refresh on open and setting the Connection Refresh button to refresh on load.

Although I did discover a whitepaper that eventually explained it all to me, I ended up figuring this one out on my own.  I should have asked myself early on why there was a ‘refresh on open’ on both the data connection AND the connection refresh.

Data Connection – Refresh on Load

When the SWF file is launched, data is retrieved from the spreadsheet and any data connections, and then dashboard components are generated.  The Refresh On Load option forces the Data Connection to retrieve data from the source before the components are generated.

What’s the downside? The downside I discovered is that you cannot use values from the controls within the Xcelsius model to drive the prompts in your web services because in reality, the controls haven’t been ‘created’ yet.  I found that even though I had default values of “All” hard-coded in my model, I could NOT get my web services queries to fire.  Instead I would get the message:  All prompts are not filled.

In order to avoid this message, you can NOT refresh the data connection on open.  Instead you much create a connection refresh as an object on your Xcelsius model.

Connection Refresh – Refresh on Load

Connection Refresh works like it did in Xcelsius 4.5.  I wrongly assumed that I would no longer need to use a connection refresh in Xcelsius 2008, but due to the issue of not being able to set values for prompts, I found they were still necessary.

BUG?  I’m not sure if it’s a bug, but I also found that if I evoke the dynamic visibility on a Connection Refresh and the object is hidden, then the refresh on open does not work.

Here is an example (the connection refresh buttons are hidden)

Then the user clicks on the setup icon and they are exposed

From a demonstration perspective, I like to hide my Connection Refresh buttons and only display them once the user clicks in a specific area to view them.  In my case I was unable to get the refresh on open to fire on a Connection Refresh if at the time the model was opened, the object was hidden.

NOTE:  To work around this bug, I added a “transparent” button at the top of my model which was NOT hidden.  It was transparent the user could not see it.   When the model opened, the transparent CR button refreshed my web services.  This allowed me to keep my buttons “hidden” but get past the refresh bug.

Final Alternative

Creating an extra step in the lookup logic will make it possible for you to be able to use the data connection – refresh on load AND have the prompts work correctly.  Instead of having the prompt use the target cell of a selector (e.g. B2) , create a second cell that says:  =IF(ISBLANK(B2), “Default Value”, B2) and have the prompt use it instead.  This way, even if the selector value is blank, because the object has not yet been created, the prompt will always have a valid value.

Click here to download sample. BTW,I did not include the database and web service for this sample.

In Crystal Xcelsius 4.5, I was so accustomed to having my web service query prompts feed from the target cells, that it took me a little while to realized that this formula would fix the problem as well.


Based upon these findings, here are my thoughts:

  • If you want the model to come up faster (before the data has been retrieved), then always use a connection refresh – refresh on open.
  • If you want to model to have the latest data at the time it opens, then always use a data connection – refresh on open.

In general, my personal preference is to use the connection refresh because I think it’s better to allow the user to see the model as soon as possible and then wait for the refresh of the data.  I also like it because it means i don’t have to update my old Xcelsius 4.5 models to use the interim calculation I describe above.

Again, here is the whitepaper just in case you want to read the full details.

Hope this helps you as much as it helped me.

«Good BI»

When using Xcelsius 2008

March 19th, 2008 11 comments

I’ve been spending a lot of time of late getting myself familiar with the new interface of Xcelsius 2008 and I like it so much better than the older Xcelsius 4.5 interface. The embedded Excel spreadsheet is real dream and the new Data Manager, which manages all the external connections make it easy to keep track of where data is coming from.

Xcelsius Data Manager

Xcelsius Error 2170 or 2048

There were a couple of things I learned during the transition that I thought would be good to share. The first is around security. With the release of Xcelsius 2008 we have moved to support for Flash 9. In Flash 9, they have tightened up security a bit more. Here is one of the first messages I got when trying to run a Xcelsius file with LiveOffice or Web Service connected data. My problem is a flash security issue. SWFs running locally (directly from a user’s computer) have additional restrictions imposed on them since version 8 of the Flash Player.

This error is coming from the Adobe Flash Viewer. If you see error messages like this in the future, you can look them up here. This has the complete list of ActionScript runtime errors.

To get around this error I need to tell my application server that it is okay to trust requests from other domains. This means I will need to create a crossdomain.xml file for your application server.

I have created a crossdomain.xml file that you can download here:

Here are the contents of the file:

<?xml version="1.0"?>
<!DOCTYPE cross-domain-policy SYSTEM "">
 <allow-http-request-headers-from domain="*" headers="*" secure="false" />
 <allow-access-from domain="*" secure="false" />

On Tomcat, you need to add this file to the /tomcat/webapps/ROOT directory. Each application server is different, so you may need to check the manual for your specific application server.

For More Information on crossdomain.xml

For more information on crossdomain.xml look here. Even more information about crossdomain.xml and Macromedia is found here.

Hope this help you achieve smooth sailing with Xcelsius 2008!

Categories: Dashboards Tags: , ,

Upgrading to Xcelsius 2008 Tips & Tricks

March 17th, 2008 7 comments

I recently starting moving my BusinessObjects Enterprise Xcelsius models from XIr2 to XI 3.0. I have found that you may need to modify some of the elements after your model has been upgraded. Namely you will need to:

  • Modify the URL of the web services
  • Modify the URL of the embedded opendocument calls
  • Modify the models

I can’t say that these changes are completely unexpected, but it unfortunately these changes will need to be part of a manual process. I think that part of the problem is that we haven’t integrated Xcelsius into any type of structured lifecycle management.

Modify the URL of the webservices

As I move my Xcelsius model from one physical machine to another, I had to go back into each Web Service and change the name of the machine it was pointing to. I also had an issue with my Universes. It appears that somehow, it lost track of exactly which universe I was pointed to (perhaps I changed the CUID during the migration to XI 3.0) so I had to manually edit each one and make sure it was working. In some cases, once I manually picked the right universe, the query still didn’t work. It would throw an error. So I discovered a special trick.

TIP: I found that when the system lost track of my universe I could click on the Show the option dialog… and it seems to fix everything. I think it realizes that the objects in the query are the same as the old universe and repoints the objects correctly.

Query as a Web Service

I also found that you can no longer just copy the URL from the QaaWS administration panel and paste it into Xcelsius 2008. You need to actually click on the which shows you the definition. From there you can click on the WSDL link and it will reveal the correct WSDL “url”. You can see that the URL below contains the end-notation, ?def. The WSDL will be exactly the same but say ?WSDL.

Modify the URL of the embedded opendocument calls

Ouch – what a pain. In XI 3.0, they changed the location of opendocument.jsp from http://<hostname>:<port>/businessobjects/enterprise115/desktoplaunch/opendoc/openDocument.jsp to http://<hostname>:<port>/OpenDocument/opendoc/openDocument.jsp

This means you need to either create your own redirector logic on the application server, or rebuild all your opendocument URLs. I can’t say I’m too surprised (it changed from CE10 to XI to XIr2). It’s just that we now see customer’s using opendocument.jsp so much more because it’s a great way to integrate Xcelsius dashboards with Crystal Reports and WebIntelligence documents.

Modify the Models

So far I’ve not had too much trouble in moving my dashboards from Xcelsius 4.5 to Xcelsius 2008, however there are clearly differences in the way the product works. For example, I had a dashboard that did some lookups and returned zeros. In Xcelsius 4.5, when I used the option Ignore cells at End-of-Range option, it would ignore a text cell with the value 0 and a numeric value of 0. Now, it will only ignore the values in the column if they are truly blank.

I’m not saying the old way was right or wrong, it’s just that now the interpretation of “blank cells” has changed. It used to include cells with 0’s but now it really means only blanks.

Therefore – fair warning. When moving to Xcelsius 2008 you need to make sure and test each and every model to make sure it is still working in exactly the same way.


Overall, I love the new features of Xcelsius 2008. I love the fact that the worksheet is now integrated into the model and the updates to the model take place in real time, but expect a few bumps along the road. Like the fact that when you switch between Preview mode and the Normal mode, Xcelsius can’t seem to remember the sizes of the properties window on the right and the worksheet on the bottom-middle.

Categories: Dashboards Tags: , ,