Unfortunately Xcelsius does not have a native mechanism to send data back to the database, as a dashboard’s main purpose to read data and not write back to the database.  However, I do see the value in being able to persist comments so that we can go back later and be able to look at documentation on why certain things happened, etc.

In this article I will give step by step instructions on how to use web services and Xcelsius to accomplish the task of inserting comments.  However you will have to write your own web service code

Preparation

In order to facilitate the ability to create comments from Xcelsius we must have a mechanism to do so and a place to persist the comments.

You will need to store the comments in a database.

Thus you will need the following:

• Database
• Database table that will house the comments and additional parameters such as user, dashboard, etc

To allow Xcelsius to send comments to the database, we will need to create a Web Service and then call that Web Service from Xcelsius while sending in the appropriate parameters (comments, user, dashboard, etc).

To facilitate the ability to use Web Services, you must have a .NET or Java application server available.

Pseudo steps to create the Web Service

1. Start creating your web service using the programming language of choice (VB .NET or Java)
2. Create a method that will house your “insert comment” code
3. Method will need to have input parameters that contains your comment text and any other parameters such as user, dashboard, etc

       Steps in the method

1. Log into database
2. Begin Transaction
3. Create insert statement that will insert the input parameters (comment text, etc)
4. Make sure that there is some sort of rollback mechanism to handle errors
5. End transaction
6. Close database connection

When you are finished coding, you will then need to deploy the Web Service onto your .NET or Java application server.

Useful Resources to help you code your Web Service

1.  Introduction on what a Web Service is

http://en.wikipedia.org/wiki/Web_service

http://www.w3schools.com/webservices/ws_intro.asp

2.  Example of creating a simple VB . NET Web Service for a .NET application server

http://www.w3schools.com/webservices/ws_example.asp
3.  Example of creating a simple java based Web Service that runs on a Java server

http://www.roseindia.net/webservices/buildingsimplewebservice.shtml

4.  Java code to connect to a DB, create a table, insert into table, and retrieve from a table (you are only interested in inserting) Start on step 22 if you are interested in the code

http://www.java2s.com/Code/Java/Database-SQL-JDBC/AccessMySQLDatabaseopenconnectioncreatetableinsertandretrieve.htm

Xcelsius Steps

In the Xcelsius example, we will walk through the steps on creating an input/output comment box; and creating a web service connection that inserts a user’s comment into the database.

1.  Drag an Input Text Area and Label component onto the canvas.  Users will enter their comments into the input text area and the label component contain existing comments.

2.  Bind the Input Text Area to the cell highlighted in yellow.  The web service connection will use this as one of the inputs.

3.  Now let’s setup our Web Service Connection.  On the data manager, add a Web Service Connection and enter the WSDL url into the WSDL URL: input box.

4.  Bind the input values to the cells of your spreadsheet.  (In our example we bind to 3 input values. Note that the output value is optional so we will skip that part.  In addition we will skip creating a Web Service Connection that retreives prior comments since it uses the same concept of what we are doing here)

5.  Insert a Connection Refresh Button component onto the canvas.  Make sure the Web Service Connection is checked.

Dynamic Recipients are often used with Crystal Reports that are not connected to a Universe and do not have BOBJ users associated with the reports.

In our example we will create a publication using the webi report from the previous post and distribute it to a dynamic recipient list.  Our list will contain a list of stores and their associated state and email.  They will receive an email containing the pdf report filtered according to their state.

Step 1:

Let’s re-use the report that we created in the previous post

Step 2:

Now let’s create our dynamic recipient list as a new Web Intelligence document.

Note: I’ve created a dummy email address object in the Universe, but in your case you would use a real email address according to whomever the report will be distributed to.

In our report we will select Store Name, State and Email Address.

Great! Now you’ve created your dynamic recipient list whom you’ll be distributing reports based on their state to.

Step 3:

Let’s proceed to create our publication.
In this post we will only go through what is needed for Dynamic Recipients so if you want detailed instructions on how to create a publication, please visit my previous post on bursting reports in Business Objects.

Instead of setting up the Enterprise Recipients list, we will setup the Dynamic Recipients list instead.
The Store Name object will be mapped to the Recipient Identifier and Full Name fields.  The Email object will be mapped to the Email field.

Make sure Use entire list is checked.  We can uncheck it, if you want to manually select which stores in the dynamic recipient list to include
and exclude, but in this example we want to keep it totally dynamic.

Note: If we wanted, we can also send to Enterprise Recipients but in this example we only want to distribute to the dynamic recipients.

In the Personalization category, go to Local Profiles and select State from the Report Field.  On the Dynamic Recipient dropdown, select State.

In the Format category, select Adobe Acrobat as the Output Format

In the Destinations category, select Email.

The To: field will need to be the email address variable that we set in the Dynamic Recipients section.  To select the Email address variable, just click on the Add placeholder dropdown and select Email Address.  Fill out the From:, Subject:, and Message: sections appropriately.

Save and Close your publication.

Step 4:

Schedule your publication to run now and verify that the reports have been distributed to each user correctly.

In our example we can see that we generated a report for each state that will be distributed to each store on the dynamic recipient list.

The ability to publish and schedule reports according to one’s profile is an important feature that we have in Business Objects.  For example, if I am a plant manager for a the West Region and want to receive a daily report on work quality, it would be very beneficial if I can receive a report in my inbox daily that only contains results for the West Region.  This would save me the hassle of manually running a report each day and manually entering in my parameters, which is cumbersome.

In this tutorial, we will walk through step by step on how to schedule and burst a Web Intelligence report as well as creating user/group profiles.

Part 1

Let’s create a simple cross-tabbed report using the eFashion Universe

As you can see we use the months as columns, the states as rows and Sales Revenue as the measure.
In addition we will insert a Year Query prompt.  I wanted to pop in the Year prompt to highlight 2 very important points that you must be aware of.

1. On our profiles, we can only filter based on selected dimensions.  Remember that you cannot create a profile parameter based on a query prompt. Many people forget this and wonder why their profile parameter isn’t working.  When we reach the profile creation step I’ll remind you again.
2. Unfortunately, scheduled reports cannot insert dynamic values into the query prompt.  Hopefully SAP reads this so they can fulfill this long awaited enhancement, since many people I know complain about this limitation.  There are ways to work around this issue but is out of our current scope.

Part 2

This stage involves setting up the profiles that we will use in our publication.
Basically what we want to accomplish here is the following:

2 groups:
US West Sales Team -> filters report on Western States
US East Sales Team -> filters report on Eastern States

3 Users:
Users will be inside either US West or US East and will filter again depending on what months they should be able to see.
Month1-6East
Month7-12East
Month1-12West

I’ve already setup the users and groups in this example so if you want more information on setting up user/group rights and content management please read the following posts:
Understanding Business Objects Access Levels
Content Management planning in Business Objects Enterprise

First let’s create a State profile that filters on your state object.

In the CMS, go to the profiles section and click on the Create new profile button.  Name the profile “State Profile”

Right click on your newly created profile,
Select Profile Targets
Click the Add… button
Select the appropriate Efashion Universe and the State object

Now we will create the 2 region groups.
First click on Profile Values and then click on the Add… button

Next click on the Choose button beside the User/Group to select the appropriate user group

On the Group selection screen, select “US East Sales Team”, press the “>” button.  Then press the “OK” button.

We can now add all the Eastern States.  To do this, type the State name and press the Add button, one at a time until you have the list of all Eastern States built. When you are finished, press the OK button.

Then we need to build the US West Sales Team.  Click on the Add button and follow the same steps as what we did for the US East Sales Team group.  When we are finished adding the US West Sales Team Profile values, it should look like the image below.

Next create a Month profile and set it up the same way as the State profile.

Create your Profile Target and add the 3 users and setup the Profile values appropriately

At the end, the Profile Targets and Profile Values for the Month Profile should look like the following screenshots.

 Part 3

The following section involves setting up the publication.
You can either create a publication in CMS or Infoview.  In this example we’ll create it in the CMS.

First we’ll navigate to the folder where I’m going to create the publication. It’s best practice to come up with a organized publication structure if you have many different types of publications.

Right click on the folder where you want to create the publication. Select New -> Publication

Name your publication

Select the WEBI that we created in Step 1 as the Source Document

For the Enterprise Recipients we want to add the US West and US East groups
Select the 2 groups and then press the > button

We can skip the Dynamic Recipients section as this is used mainly for Crystal Reports, which will be covered in a future post.

On the Personalization section we want to add the profiles that we created in Step 2 as part of the Global Profiles.

Note: We don’t use Local Profiles here, however if we do want to use Local Profiles, you can setup a profile that only has Profile Values and no Profile Target, and use
those values here instead.

Also Note: Important reminder that profiles can only filter on dimensions existing in the report and not the Year query filter prompt that we created!

In the Format section we can select the type of format we want to distribute the report in.  In our example we will distribute the report to our users in PDF format.

There are a variety of destinations that we can burst the report to.  The most popular options are Business Objects Inbox and Email.  In our example we will burst to the Business Objects Inbox.

Now we can setup the scheduling portion by going to the Additional Options section.

On the Prompts section we will select 2004 for the Year.  Remember that this can only be a static value unfortunately.

Next we will set our report to burst daily at 6am.

The last 3 items we will skip, so just click Save & Close.
Note: From the advanced section I will be writing a post on Profile Resolution in a later post.

Now instead of waiting until 6am next morning, right click on the publication and select Run Now.

Note: You’ll notice there’s an option to Subscribe and Unsubscribe from publications.  I’ll be covering the subscribe and unsubscribe conceps in a later post.

Verify that each user has the correct bursted report by going to each users’ inbox through the CMC as the Administrator.

A replay of the presentation can be found at the following link => http://bit.ly/rkBU1p

I’ve also attached the powerpoint presentation that you can download here

Unfortunately my previous tutorial at http://davidlai101.com/blog/2008/11/04/all-you-need-to-know-about-opendoc/ only works with Business Objects XI R2

Please note that the tutorial was taken from SAP BusinessObjects Dashboards 4.0 Cookbook so if you are interested in more useful Xcelsius recipes, be sure to purchase a copy

If you only need the Excel Template for setting up OpenDocument calls, you can download it here

If you are interested in getting detailed information on OpenDocument Parameters please download the official SAP OpenDocument documentation here

Otherwise continue reading for the Tutorial

Getting ready

A WEBI report with a prompt must be setup first.  In our example, the WEBI report will ask for a Region parameter.

You can download the example XLF source file from here

How to do it…

1.            Drag a Combo Box selector onto the canvas and bind the label values to the list of Regions on the Excel spreadsheet as shown below. Set Label as the Insertion Type and bind the Destination to the cell highlighted in yellow, which will be the input parameter passed to the opendoc call.

2.            Log into BI Launch Pad (Formerly Infoview), go to the location of where the Web Intelligence report is located. Right click and select properties.

3.            Copy the CUID

4.            On cell B14 shown below, an opendoc url is constructed. Paste the CUID from Step 3 onto Cell B23 highlighted in grey. Cell C23 equals the Selected Region cell C2 from Step 1.

5.            Drag a URL Button component onto the canvas from the Web Connectivity section of the Components window. In the URL Button properties change the Label text to Open WEBI report, and bind the URL to the opendoc url cell B14 from Step 2.

I’ve been given the opportunity to share my experiences with BI Web Services and how to build more efficient dashboards using BI Web Services in the upcoming SAP Inside Track Vancouver 2011.  Please mark your calendars for September 27, 2011 2:30pm -> 3:20pm PST!

In this presentation you’ll learn about BI Web Services and how it provides a hybrid of QaaWS and Live Office.  As well as some new powerful features that were not available before. We’ll also discuss some limitations and work arounds to prepare you for the road ahead.

For those who are attending, there will be a set of door prizes which include copies of my book SAP BusinessObjects Dashboards 4.0 Cookbook

For those who cannot physically attend, there will be a remote access link found in the wiki link below.

For more information on the event, please visit http://wiki.sdn.sap.com/wiki/display/events/SAP+Inside+Track+Vancouver+2011

Hope to see everyone there!

Well I did a quick test and yes there is a bug! So I wanted to warn everyone including SAP about this new bug.

I’ve tried both methods where I use the color code and the color itself, and both give the error.

Here are the steps shown to reproduce the problem
First try to bind a chart series color to a dynamic cell (In this case I tried cells with both the name and code)

As soon as you bind the cell, you will get the error as shown below. After that whenever the cell changes, the color will not be reflected appropriately on your chart.

I’d like to congratulate the 2 lucky tweeters who have won an eBook copy of SAP BusinessObjects Dashboards 4.0 Cookbook

They are ……..

rodri_m_d
montoyainigo

On Monday July 25, 2011 we will be randomly drawing 2 winners and grant them access to their free eBooks!

All you need to do to join the Tweetaway twitter contest is to send out a tweet with a link to the SAP BusinessObjects Dashboards Cookbook page.

The easiest way is to tweet by clicking on this link! or you can use the share buttons from the PacktPub SAP BusinessObjects Dashboards Cookbook page

Retweeting other tweets is also allowed and there are no limits to how many tweets you can tweet!

Good luck and happy tweeting!!

The following features were added

The new waterfall chart component saves developers the hassle of emulating one using the stacked bar chart and excel logic.  For those of you who haven’t upgraded to SP4 you can find a good example of completing waterfall charts @ http://www.femkekooij.nl/?p=325. To see how much easier and cleaner it is to create a waterfall chart using the waterfall component, check out the example @ http://myxcelsius.com/2011/06/15/xcelsius-2008-sp4-waterfall-chart/

The other major new feature that I consider very useful, is the ability to replace the scorecard component with spreadsheet table alerts.  Please read my post @ http://myxcelsius.com/2011/06/28/xcelsius-2008-sp4-spreadsheet-alerts/ to see how you can use the spreadsheet component to accomplish what you need from a scorecard component and more!

The combination charts having alert capabilities is also very important as we do not have to overlay multiple charts on top of each other anymore to accomplish alerting functionality.