Hi…I have a excel sheet which has to be converted to XML data inorder to be pulled into my dashboard XML data connection. I learned to convert excel file to XML using ALTOVA XML editor. However, I’m not sure how to make this process automated as data would be updated everytime and that should reflect in my dashboard ultimately. Please let me know how to make this to work. Thanks.
rkie12 (BOB member since 2011-01-04)
Have a read of this post:
Contains a useful link to a web site which showed me how to create an excel map. This means that a user can update a spreadsheet as normal and save it as normal. If they also do a SAVE AS, it will send an xml version to the dashboard as well.
Debbie
Debbie 
(BOB member since 2005-03-01)
Hi Debbie…Thanks for your response. I checked the link you mentioned but it looks like there is manual process involved which I’m trying to avoid. My spreadsheet is an output of Crystal Reports which would then be used in a dashboard. Can you think of similar challenges you might have had before?
rkie12 (BOB member since 2011-01-04)
Not that I know of. If the spreadsheet is just an interim product from Crystal, then why not use live office, web services or straight querying of the database instead? All of those will give you automatic XML.
Debbie
Debbie (BOB member since 2005-03-01)
We have access to LO/QAAWS but there is some intial setup involved on our server and our admin is superbusy, so I’m exploring other data connections. Dashboard data is extension of CR, so querying DB would not be a desirable option from manager perspective. Anyway, thanks much for responding.
rkie12 (BOB member since 2011-01-04)
Thinking onto paper here…
I don’t use Crystal but I assume when you export to excel, it creates a new spreadsheet each time so any internal messing about would get overwritten on each export.
BUT, you could set up another spreadsheet with an xml map, like I did via the link I gave you. You could dymanically link it to a new spreadsheet - call it CRYSTALOUTPUT.XLS. So all your data in the xml map spreadsheet comes from this CRYSTALOUTPUT.XLS (eg: a1=crystaloutput.sheet1.a1 or however it’s written). Then make sure whenever you export from crystal, you export it to CRYSTALOUTPUT.XLS. It will overwrite the file, but there’s nothing in it so it doesn’t matter - your data will update automatically in the xml map spreadsheet. Stuff a macro in to save as xml every time the data changes and you shoudl have your automatic xml.
Never tried it but it might be worth a go?
debbie
Debbie (BOB member since 2005-03-01)
Thanks again Debbie. I will try and let you know. Do you mind sharing your email, so as to contact you if I need any help with XML as I see that you have been using the technique for a while. Cheers.
I tried to replicate steps you described above manually. Basically created a dynamic XML spreadsheet and made data from another spreadsheet to pass through. It worked fine when I changed data in the spreasheet that was referred to in the dynamic excel sheet. Here comes the problem, when I wanted see its response in Xcelsius manually like I did before, unfortunately dashboard is freezing with preview load sign, in another words it was taking forever to load the preview. This could be because of too much communication with in that Xcelsius is not able to respond to. Appreciate if you could try and let me know if you are facing the same problem. Thanks.
rkie12 (BOB member since 2011-01-04)
If you’ve just got an xml maps connection, there shouldn’t be that much communication going on. It’s not something I’ve played with extensively - I did a mock up as a demo for our HR people but they never came back to me. I suspect they decided that their excel graphs were less hassle!
I use straight SQL. It’s so much simpler and less reliant on other factors. I have VB code wrapped up in an aspx web page that connects to the database, runs a query and returns results as xml to the dashboard. This way I can pass parameters in and out of xcelsius so I can parameterise the query according to what the user selects on screen. It also means I can combine data from several different systems at once. Fast queries on aggregate tables in the data warehouse run in real-time. Slower fact-table queries are scheduled to run overnight and xcelsius grabs the pre-generated xml files instead. It’s taken me a long time to develop and fine-tune as I’m not a programmer but it works well.
Can’t do email I’m afraid, much as I’d love to help. I’m public-sector and I can just about get away with this site as “work” - after all, I’m learning too and I have had great advice and help off others here. But everything we do is monitored and they’d object to email!
Debbie
Debbie (BOB member since 2005-03-01)
No worries about the email as long as peers like you respond to our queries here 
. And yeah querying DB and getting data into dashboard is a familiar practice here too. I mean some of my friends who do not have access does that way. Again if you have time to try on sample data and see if it is working, that would be great. I will continue to try and hope it works. Thanks.
Debbie…Can you tell me how to add data to already created Excel XML Mapped spreadsheet? I created one row of data and mapped in Excel. Then remaining data I tried to get it through reference spreadsheet. What ever data that was not included as part of intially created XML is not shown on my dashboard. How can I add additional data by means you suggested yesterday to XML mapped Excel spreadsheet?
rkie12 (BOB member since 2011-01-04)
I was able to get through hurdle of changing my Excel data to XML :D…Now the only issue I have and working on is to automate Save As to XML file. I’ll update once I get through that as well.
rkie12 (BOB member since 2011-01-04)
I have data in sharepoint list and thru which I am linking data to MS Access. In MS Access I am doing some queries.
How can I bring data from MS Access to XCelsius automatically to create a dashboard? I tried to create XML using sharepoint list utility, but I am not sure how to link that XML in Xcelsius.
Please let me know any options of bringing live data to Xcelsius from sharepoint or MS Access. We don’t have sharepoint webpart for Xcelsius.
Thanks
nkpriya (BOB member since 2010-05-04)
You can use Access as any other database and query it with SQL - either via qaaws or via a query in an aspx page. There are lots of threads here dealing with both.
debbie
Debbie (BOB member since 2005-03-01)
Do I need to have Flynet for using Access or SQL? I read somewhere that QAAWS query can be used only with BO. Thanks
nkpriya (BOB member since 2010-05-04)
Sorry - no idea. I get my data by querying the database directly using VB code inside an aspx web page. This returns my data directly to the dashboard in XML format. This works with Access as I’ve done it.
Debbie
Debbie (BOB member since 2005-03-01)
you could try and create a schema of your xml file
dave1897 (BOB member since 2011-04-07)