Use of Excel XML maps as connection for Dashboards

1)     1) Convert excel sources to XML files as mentioned below.

Ø  Open the excel source data and delete all rows above the desired range. The first row of the excel sheet should be the column header and below that, the data.

Ø  Replace all BLANK cells within the range with a value. In your XLF file, you can replace that value with BLANK’s by means of formulas. Below are the steps to Replace BLANK with a value, say 0,000.

o   Click on a blank cell

o   Press Ctrl+G

o   In the Go To wizard, click on Special and Select Blanks

o   Click Ok

o   Type 0,000

o   Click Ctrl+Enter.

o   Now there are no blank cells in the excel sheet. Save this sheet.

        Ø  On the Menu bar, click on Add-Ins->XML Tools->Convert a range to an XML list. Click and drag the range. Click Ok on the wizard and on the next wizard, click Use   existing formatting. Continue this for all the wizards.

        Ø  If you don’t find Add-Ins in your Menu bar, click on Office Button and select Excel Options. Click on Add-Ins, In the Manage Drop-down, select Excel Add-ins and click on Go. Select the Xmltools checkbox. Click OK.

        Ø  Click on Developer, Export and then export this to an XML file.

2) Export your XLF spreadsheet using Data->Export.

3) Open the spreadsheet in Step 2) and in Developer->Import, select the required XML file and paste in the required worksheet, cell.

4) Go to Data->Connections; a Workbook connection should be visible now.

5) Go to Developer->Source; an XML map should now be present.

6) Click on XML maps at the bottom, the XML map will be displayed, you can Rename it appropriately.

7) To add more XML sources, Go to Data->From Other Sources-> From XML Data Import and select the required XML file.

8) Repeat steps 4), 5), 6).

9) In one of the worksheets, use a internal logic to populate the XML file names or hard-code the same as ./XMLfilename.xml where XMLfilename is the name of the XML file.

10) Save the Spreadsheet.

11) Open the XLF file and import the Spreadsheet.

12) In the XLF, go to Data->Connections ->Excel XML maps.

13) All XML connections will be visible here. Click on every XML connection and point the XML URL to the cell in Step 9).

14) In the Usage tab, select Refresh components before they are loaded.

15) Pull the desired components to prepare your model using the mapped XML cells as source.

16) Save the XLF and export to Flash file.