Have you ever developed a “one-off” dashboard in Xcelsius only to have it upgraded (by the boss or client) a couple months later to “we need that dashboard updated every quarter”? Many of you have probably been faced with this situation and needed to find a way to maintain and support an Xcelsius dashboard containing static (but evolving) data.
Ideally, you would connect your dashboard to a Data Manager connection type that allows data to be dynamically inserted into the model. Commonly used connection types for loading data dynamically are: Query as a Web Service, SAP NetWeaver BW, Live Office, or a Web Service from Web Intelligence Rich Client in BOE XI 3.1 SP2. But unfortunately, these connection types are sometimes a luxury and not always available. Sometimes data in Excel is your only data source.
So what to do when you’ve developed a fairly extensive model containing a large number of formulas and need to update the data?
Create a template using Snapshot
- Begin by previewing your dashboard. (Click Preview)
- Click File>>Snapshot>>Current Excel Data
- Save the XLS file locally as a Document Template
The entire XLS portion of your dashboard will be exported, including the formulas!
This new template document can then be used as your new data source. Be sure to rename it, then add in all the new, most recent, and updated data. You may have to revisit certain formulas if references to cell ranges are no longer all-inclusive because the data size has increased. These types of changes are generally very easy and can be made quickly. The important fact is that your document still contains your original formulas and also includes the latest data.
Color Coding Dynamic Cells
Tip: When using formulas in your Xcelsius dashboard, be sure to color-code the cells. This will help will to validate accuracy if/when the model needs to be turned over to someone else to support/maintain, or when the data grows and you need to verify that the formulas are references all the data.
Inserting Your Updated Template
To insert your updated template into an existing Xcelsius dashboard, be sure to begin in development mode – (not preview mode). There are two ways to insert an Excel file.
- Click Data >> Import >> then select your latest Document Template
- Click the XLS icon (on the Standard Toolbar) >>Â then select your latest Document Template.
That’s it! Now you have a way of manually updating your static Xcelsius dashboards. Hopefully this style is only needed once in a while – ie. monthly/quarterly/yearly.
Thank you-
Jim Brogden
