Dashboard based on external Excel file

[Former Member]

Hi!

I'm going to create new dashboard based on external excel file. I think the proper way is to create a universe based on ODBC datasource. But for some reason I can't create query in dashboard: in "Add query" after selecting my universe error XLS 000009 occurs.

I've tried both Excel 2010 and Excel 2003 file formats without success. With Excel 2010 I see message (sorry for russian)

With Excel 2003:

1. Any suggestions for solving this problem?

2. Any other approaches for externa Excel-sources?

[former_member194862]

Hi Yury,

First of all MS access database is not supported if you want to create Dashboard based on Universe directly using query browser.

Relational Databases like MS Sql or MySql,Oracle are supported for this.

Create 2 DSNs one 32 bit and one 64 bit both of the same name using ODBC drivers.Make sure that these two dsn should be of same name pointing to your database.

Now create a UNX universe using IDT Information design tool.

create a query in Dashboard using query browser

Regards,

Amit

[Former Member]

What exactly do you mean by external Excel source.

Do you want your dashboard to be linked to some Excel file that lives on a server, such that updating this Excel file automatically updates your dashboard?

Or is this external Excel file static, in which case you merely want to start a new Dashboard using this Excel spreadsheet?  (In which case, you could simply do Date > Import)

[Former Member]

First.

[Former Member]

Hi, Alex!

Could you please suggest any solution?

[Former Member]

Hi Yury,

There's probably a better solution than this one, but it's hard to say without having a better idea of what exactly your setup is like.

Instead of connecting to your Universe as you're doing now (and there's probably a simple way of making that work, I'm just not sure where the problem is), you can use an external XML file in place of the external Excel file.

Here's what you want to do:

1. Convert your Excel file to XML format (you should be able to google for a VBA macro to help you do this from Excel).  Make sure that the format of the XML file is exactly as Xcelsius wants it to be (hit 'Preview XML' in the XML data connection wizard).  Xcelsius is very picky about this.  Put this file somewhere on your server, or anywhere the Flash file will be able to reach it.

2. Add an XML Data connection, and map it to your cells.  This step should be obvious if you've used data connections before, but essentially you're mapping a block of cells from the Excel spreadsheet to a block of cells in the Xcelsius spreadsheet.

3. Everything should now work.  Whenever you want to update the Excel file, simply open the Excel file, change it, and export it to a new XML file on your server.  Xcelsius will now connect to this new file.