Tag Archives: dashboarding

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/