Category Archives: business

BI: Using combo boxes to get things done in Xcelsius

I was on a project recently working with dashboarding in SAP Business Objects and I found a couple things that helped me out so i wanted to put them up here.  I’m heading back to the land of web development so i’ll document these things before I forget them.

This post is one about using combo boxes in Xcelsius to actually do some things.

Xcelsius is a dashboarding tool — not a UI tool.  I’m used to UI tools which let me use callbacks and events,etc.  Xcelsius is very simple and only lets you do simple actions. Everything is based on performing some action (e.g., press a button), change some value in the attached Excel spreadsheet, and (typically) hide or show some other things based on that value you just set.  So you see here that for a given action, you can only (easily) perform one data movement (called data insertion).  There are other components, like tables, that will sometimes let you move more data when you click or hover (your only two event options… and they cannot be used at the same time on the same component), but most let you do a single thing.

With combo boxes, you can do more.  I do not know if this is an intended feature or not, but you can set up hidden combo boxes to watch for that “single thing” that most components do, and then perform a lot of data insertions.  This, along with some nice excel functions, allows you to cross into the realm of making the dashboard act like a more complex UI.  I didn’t find any good write-ups so here is mine.

Anyway, here’s the idea:

A couple things to know to start…

  1. A combo box ONLY watches for the trigger and performs the data insertion when it is “visible”. This means that if it is hidden because of its Dynamic Visibility setting on the Behavior tab, OR if it is on a canvas that is not current visible, etc, then it WILL NOT RUN. This is GOOD. it allows additional control to help avoid getting into data insertion loops or having timing issues.
  2. While a combo box only runs while it is “Dynamically Visible,” any functions (like IF statements) that you put in the spreadsheet cells continue to run all the time.
  3. If you are using the combo box for data insertion only and do not want the user to interact with it, you need to hide it so it is not “physically” visible when it is set to be “dynamically visible” (ie, put it behind another component so it runs but isn’t seen.) You need to move it up or down in the Object Browser to get it to the right place for it to (a) run when you want it, and (b) not be seen if you don’t intend it to be.
  4. I have noticed that the combo boxes executed in the order they are placed from TOP TO BOTTOM in the object browser.

The settings you need to use it for Data Insertion…

When you add the combo box, it is very simple to set up. This is the way i use it for data insertion. If you know a new way, edit this document!

Behavior tab

  1. Set the TYPE to “Label” and the ITEM to the cell you want to WATCH.  This means the cell you are watching is your trigger cell. When this cell becomes equal to a value that is in your list of “labels” (that you will define on the General tab), then the data insertion will occur.

General tab

  1. Set the LABELS cell or range to values that are valid for kicking off this data insertion. These would be what are shown to the user in the combo box if it was visible.
  2. Set the “Clear destination when no selected item” if you want that… make sure to consider if you have other people (“components are people, too”) copying data to this destination under different circumstances.
  3. Set the radio button at the bottom to “Data change and interaction” — “Interaction only” doesn’t make sense for a hidden component.
  4. Finally, set up the data insertions!
    1. If you select Filtered Rows, then you can only have one data insertion. I’m not sure if the labels and the filtered rows have to match up or not for this. If you want to move multiple rows, like a whole table, then you need to use this one. I could not get a large block of data to move when using data insertion types other than this.
    2. Any of the other insertion types can have multiple data insertions in the queue. This is great… off of one trigger you can rearrange all kinds of stuff, and the source and destination do not have to have any relation at all to the labels or trigger cell. Examples are:
      1. Value: This will move ONLY a single value from one cell to another.

        Important Note: Per the Xcelsius 2008 user guide ( and my experience ), it is REQUIRED that the VALUE data insertion SOURCE range is equal in size to the LABEL range. This requirement can lead to some columns in the dashboard spreadsheet basically being hardcoded, repeated identical value columns if that is what you need.

      2. Row: This will move a single row… not multiple.. (at this is what i saw when testing).
      3. Column: This is like ‘row’ except it moves only a single column

Additional Note:

Avoiding selection memory in the combo box: You may want to set the Item field to a cell. This is on the Behavior/Common tab of the combo box component. If you are using the combo box behind the scenes, or if you have it visible with only one selectable item, then once a list item is selected you may not be able to select it again until a different item is selected (or not at all if you only have one item in the list). A solution for this is to set the Item field equal to the Insert Selected Item field, and then make sure that you clear that cell after a selection was made and the action performed (ie, make the last data insertion in the combo box move a blank value to that cell). By keeping that cell cleared after a selection is made, and by having these two fields equal, there will never be a “pre-selected” item in the list.

So that’s it.  You can use combo boxes to do more in Xcelsius.  Let me know if this helps you out!

I have two more write-ups that i will do when i have a chance:  One is an useful excel function that lets you take a column of blank and populated cells and compress them into a single column without spaces, and the other shows how to create a counter using the History component.

I’m not maintaining this blog here so commenting is closed… Please see this same post on my new site to ask questions: http://mpickell.com/blog/2012/02/08/bi-using-combo-boxes-to-get-things-done-in-xcelsius/


market research is hard

I took up a new project at work recently which is semi-related to software.  We are building a new system, and now we expanding our marketing efforts.  My new job for the last couple of weeks was to perform a market study of a niche market in Pennsylvania (Muncipal authorities — but not even, it is a subset of the authorities I’m researching) and determine how we are going to attack it!

Typically being a software developer (I am doing marketing because I also have an MBA), I assumed I would be able to find a nice and tidy set of websites that I could quickly consolidate and tweak in order to serve my purpose of data collection.  However, after the first four hours of looking at authority websites that look like they were created in 1995 (one even has blinking text!), I realized that this was not as easy a solution as I thought.

I took a step back and spent a day looking for associations and group sites.  This was a pretty good path, I found one site that listed a couple of hundred PA authorities websites.  I went with it and, not thinking I guess, started down the list going to each one and writing down email addresses.  By the end of the day, I had all of 31.

I quickly (compared to the rest of the day) put together an email questionaire template and fed the names and emails into it via mail merge (in MS Word).  Also, quickly, I received 4 or 5 replies.  It only took about an hour for them to come in!  And they were useful!  I learned my first lesson.

Just about all replies from email marketing come within 24 hours

No science or official theory behind that, but that was it.  I received those responses and no more.  And more, it seems to fit what I have always heard about replies: about 3-5% response from marketing sent out.

I wised up and remembered that technology could save me.  If I thought of a program that would be slightly useful, i knew someone else would have already written it.  And they did.  I downloaded (bought) a program called “email grabber.”  I took the websites I found, fed them into email grabber, and set it free.  It downloaded 3500 emails for me in about 20 minutes.

After sorting through those and finding the ones that actually went to people, I fed them back through my mail merge template, and guess what…  I got back 95% of my replies by the next afternoon, again at around a 3-5% rate.  This isn’t easy!  Now I’m on to contacting more people by phone.