OK, I don’t get XML… can anybody tell me in easy-to-understand terms how to accomplish this, if it’s possible at all:
I have data in an excel spreadsheet that I need to display on a dashboard. The data doesn’t change very often and the users would prefer to be able to enter/change it in spreadsheet format. But I don’t want the users putting data directly into xcelsius as that would involve regenerating SWF and WAR files.
So, having managed to generate XML files via aspx code for other applications, I thought it couldn’t be too hard to keep my data in excel and then, when the data changes, to export the data as an XML file - then the dashboard would pick it up with all the XMLs I use.
But I can’t get it to work. Excel keeps telling me I need XML maps and I can’t work out how to generate one. The actual data is simple - a dozen columns and maybe 50-60 simple rows of data. How can I set up something easy - so when the users change the data, I can just click something to export it as an XML.
So you’ve already done the hard part an generate the XML data you need right?
If you open Excel (not in Xcelsius) and do (I’ve not got Office 2003 in front of me so maybe slightly out on the menu names) Data > XML, XML Maps you should be able to point to your XML data and Excel will then create a Map of it.
You can then import this into Xcelsius and use the Data Manager to set the refresh options of the XML Map.
I have an example I sent to an earlier question on this in another forum, I’ll dig it out this evening and upload it.
No, sorry, I’m obviously not explaining very well. I have simple row/column data in an excel spreadsheet. What I can’t work out is how to generate an XML file from this. I naively thought I could just do a Save As, but nothing is ever that easy!
Ok, No it’s not that easy, and I’m don’t think that you can.
Sounds like you need a VBA solution, or as you’ve done this within the .asp environment maybe the updated could be done on an asp page and remove the Excel part altogether?
Charles, my users like their excel spreadsheets! They format them with merged cells upwards and sideways, blank rows (so it looks better printed out, of course) and all manner of fiddly bits. It took me two hours to strip out the formatting last time and they just went right back and did it again…
I started out exporting to Access and using that to create a one-table database in SQL Server, then writing aspx code to get the data from that. But it seems like using a sledgehammer to crack a nut and then I have all the hassle of trying to get a “new database” past our IT dept.
Thanks for your ideas. Willhave to mull this over for a bit.
I now have what appears to be a normal spreadsheet (although it contains hidden clever bits now). The users can change their data and add new rows as required. When they are done, they simply save to an XML file and it generates the XML ready to import into xcelsius. Perfect.
No. I followed the excel example in the link I posted:
Created a basic xml schema with one row of data in notepad. Used this to automatically generate a schema in excel & mapped the elements. Added the rest of the data and made it user-friendly.
Saved the spreadsheet. Then saved it as xml data and opened the xml file in explorer to check the contents. So users can use the spreadsheet as they would a normal spreadsheet - they just need to remember to save it as xml when they want any changes reflected in the dashboard.
In xcelsius, copied the url of the xml file into a cell and referenced this in a XML MAPS data connection. Set the output to a range of cells. Created a grid component to test data and it displayed OK.
Saved xcelsius. Went back and made a change in my spreadsheet. Saved it to xml. Back into xcelsius, hit preview and the change was reflected there.
Took me quite a few attempts and I’m sure I don’t do things the easy way, but I got there in the end!
eric,
I am interested in your article but I cant seem to get to the website.
can you send it to me some other way. I am having serious trouble with a spreadsheet and my XML Map. I can’t get the data to dynamically populate into the next column!
Using Xcelsius 2008 (5.5.0.0). I have a simple spreadsheet I want to “import” into xcelsius. The data won’t change very often but the users need to be able to update it easily.
First problem. Why can’t I add an excel map connection to a dashboard? In a brand new dashboard, in the data manager (with no other connections), if I click Add, it says Existing Connections and lists Excel XML Maps and Live Office Connections beneath it. If I click on either, precisely nothing happens…
Second problem. OK, to get around that, I thought I’d just generate an XML file manually from my spreadsheet (using this process: http://www.excel-easy.com/examples/xml.html), but I can’t get the structure into a format that xcelsius will read. It’s a very simple spreadsheet - 4 rows and about six columns.
Hmm. That link is all about using a generic XML connection and fudging the actual external spreadsheet with VB code. seems awfully convoluted when the last tiem I tried all this a couple of years back, it was relatively simple to get excel to generate a map and xcelsius to read the map code with a map connection.
Oh, I get it. I think (talking to myself again…) Been reading the HELP files in Xcelsius itself and surprsingly they are rather helpful!
I need to generate a map in a separate spreadsheet - which I have already done and saved. Then I need to IMPORT this spreadsheet as the spreadsheet part of a new dashboard XLF file in order to get the map connection to “see” the map and generate the connection. Which would be fine except that I wanted to add this to an existing dashboard which already has lots of other connections running.
Debbie
ETA: Just tried to import an xml map while in xcelsius and it crashed. Twice…
How about creating a Dashboard purely for this xml data source and have a ‘View xml data’ (or something) toggle button on your current dashboard to control the dynamic visibility of your new xml dashboard, using the SWF Loader component?
Finally, it lets me import a map without crashing. My data is there. I can see the map via the SOURCE button on the DEVELOPER tab. I can display and verify the map. It looks fine.
But it still won’t let me create a map connection to feed the thing. Just doesn’t work!
Debbie
ETA: Close xcelsius, re-open and the map connection is now there. This product is more flaky than chocolate…
ETA2: Aaaand - it’s working! No idea why. I find kicking the pc helps sometimes. And coffee. Lots of coffee. Thanks for your help, Charles!
(BOB member since 2005-03-01)
(BOB member since 2002-06-20)