Customers ask me often to give them the list of the tips and tricks I mention during training. I thought to share it with you also.
- Regional Settings client PC: Location set to UK and number format to English or Dutch WITH decimal symbol as a , (comma) and digital grouping symbol as a . (dot) (Dutch number formatting does not always work in Xcelsius)
- When an Excel file does not open in Windows Explorer, change the DDE settings in Excel.
Open Excel, go to Excel options (click on left most icon/button, then bottom pop-up, Excel Options button) Tab Advanced, scroll down to general, select Ignore other applications that use Dynamic Data Exchange (DDE)
- Run both Xcelsius and Excel at the same time. This can be done, however if you do not use the Excel please close it, as the program is then more prone to instability. When you need to open both, first open Xcelsius and then secondly open Excel.
- Xcelsius does not have the ability to have 2 files open in the same program instance. Open a second instance of Xcelsius instead. However if not needed, then avoid it. (2 Xcelsius and 2 Excel processes are opened in this case)
- Select the Xcelsius Excel options (File – Preferences – Excel options) “Optimize Spreadsheet” and “Ignore Excel Formula Errors”. However these options are set locally for exporting purposes and are not saved in the xlf, remember to set these as a standard for all developers as you do not get a notification when you export the dashboard without these settings, as a result your dashboard will e.g. show the error values #DIV and #N/A and dependency on these cells having NULL will not work.
- Only enable Live Office Compatibility when having LO connections in your dashboards, otherwise de-activate it. (File – Preferences – Excel options)
Format / Template
- Best practice: Avoid 13 common mistakes in dashboard design
(Read my Dutch article: Dashboards zijn Hot!)
1. Exceeding the boundaries of a single screen
2. Supplying inadequate context for the data
3. Displaying excessive detail or precision
4. Choosing a deficient measure (indirect view)
5. Choosing inappropriate display media
6. Introducing meaningless variety
7. Using poorly designed display media
8. Encoding quantitative data inaccurately
9. Arranging the data poorly
10. Highlighting important data ineffectively or not at all
11. Cluttering the display with useless decoration
12. Misusing or overusing color
13. Designing an unattractive visual display
- Best practice: Conform to 6 design best practices
1. Remove yourself from the data (KPI Model)
2. Start with pen and paper (Layout)
3. Design a logical layout structure
4. Less is more
5. Use summarized data
6. Use alerts for exceptions
- Best practice: Before creating a lot of dashboards decide on the theme, color palet and font, as it will be time consuming to alter this after you finished a dashboard. Good practice is also to set the canvas width/height to the screen resolution used the most, keep in mind that increasing the flash in a browser is viewed better than decreasing.
- Best practice: Use colors in your spreadsheet to explain the function of certain cells (create also a legend). E.g. yellow background means destination cell from Xcelsius component, bold red number is used in lookup functions for column and row identification.
- Best practice: Use at least 2 sheets in your spreadsheet. One for titles and legends and one for data and component linking. If you have several data queries you might consider splitting data and component linking. Or you keep the data on 1 sheet and the linking to components on another. Or you keep the data query and the associated linked components together on a sheet. (thus per data query a separate sheet).
- Use preferably the 100% view of a picture, if you need to scale a picture, press Ctrl and Shift together and then use the mouse to alter the dimensions, this will keep the aspect ratio intact.
- Color palet is an xml file stored on client. You can delete or add these xml files to custom and default location.
default: C:/Program Files/Business Objects/Xcelsius/assets/themes/built-in
custom: C:/Documents and Settings/user name/Application Data/XcelsiuscustomThemes
- Best practice: Think your queries through before adding them to Xcelsius, each time the query changes, e.g. added/deleted columns, added/deleted prompts, you have to import the wsdl and link the input and output values again.
- Best practice: The dashboard as a whole performs faster when using more queries returning a small dataset then less queries with a large dataset.
- Best practice: Do not trigger all queries on opening dashboard before loading components. Only do this for those queries that do not have prompts and are necessary to fill the prompts of other queries. Likely the selector box queries are set on load before components are loaded (in data manager) and the chart data query are set to load after loading components (refresh connection button)
- QaaWS cannot add custom variables to a query like you can do in Web Intelligence documents. However avoid Live Office Connections when it is possible to alter the universe instead to contain the necessary variables.
Live Office Connection
- Best practice: do not transfer the format of the WebI reports to Excel, as these are not needed and will take up loading time.
- Best practice: use only for scheduled reports (thus without prompts) containing variables which cannot be placed in Universe and are to “heavy” for calculating in Xcelsius. As result the dataset will be larger than with QaaWS. (When possible use QaaWS.)
Excel / Spreadsheet
Be careful with using the following formulas:
Use these formulas ONLY on a (very) small dataset, as the calculating takes long to process. Perform aggregation preferably on database level and use Match and Index for the lookup formulas.
- Pivot Tables are partially supported by Xcelsius. The formula GetPivotData is NOT supported, the pivot table itself is transformed to a static table (note: the selectors are thus inoperable).
- Defined Names are supported by Xcelsius. However there is a restriction: the defined name must refer to a single cell or must use formulas which return a single value from a range of cells.
Defined Name: Sales (cells A1:A12)
Defined Name: Cost (cells B1:B12)
Defined Name: ExpenseCurrent (cell C1)
Defined Name: ExpensePrevious(cell C2)
Can be used: = Sum(Sales) – Sum(Cost) or = ExpenseCurrent – ExpensePrevious
Cannot be used: =Sales-Cost or =Sales
- VBScript and Macros are not supported by Xcelsius