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.

Share

2 thoughts on “BI: Using combo boxes to get things done in Xcelsius”

  1. Hi Mr Pickle!

    thanks for a great guide.
    My question is the following;
    can one have a combobox acting as a text input in a table?

    for example, there is a column with missing values (-) in our table which we would like to populate with a given value from the combo box.

    i.e train delay
    123 late
    456 –

    I would like to be able to have the combo box integrated(overlaying) the table above, and then write this entire row back to a db. The writeback however is not the issue here, mainly the insertion.

    Your help is greatly appreciated in this matter.

    Yours Sincerely,
    Bradley Coyne

    1. It has been a while since i’ve worked on dashboards, but if i understand you you want to use a combo box like a text box for input from the user, and then append what you type to a table in your database?

      I think combo boxes needed to be driven from a table, or can watch values and perform data movements. I’m not sure you can do what you want; I don’t recall being able to input data in a combo box. However, you could probably turn it around and enter data into a table and have a combo box reflect that information. Or, ‘dress up’ a single-cell table above your other table so it looks like a text box, and then use a combo box data insertion (or a simple button next to the “input box”) to move that data from the single-cell-table’s excel range over to the larger table’s excel range. You would get the effect of having an input box that way.

      One way i found to dress things up was to create an image that looks like what you actually want, and then overlay whatever Xcelsius element OVER that image in your dashboard to get the functionality you want. So for you, make an image that looks like your input box and overlay a transparent single-cell table on it in your dashboard (or as transparent as Xcelsius will let you get). I used this method, with a button and listbox to create what looked like a nicely formatted combo box with a little arrow on the button — like you see on webpages.

      The problem with the dashboard software is that it is just that — dashboard software. Getting it to act like a user interface is painful.

      Hope this helps or triggers some other ideas for you..

Leave a Reply

Your email address will not be published. Required fields are marked *