WebI: Report Creators - Using an Excel spreadsheet as a data provider

Report creators can use an Excel spreadsheet as an "external data provider" for WebI reports. There are two kinds of Excel external data providers:

1. Managed
   • A "Managed" data provider resides within the BI LaunchPad
2. Unmanaged
   • An "Unmanaged" data provider resides outside of the BI Launchpad.

This article discusses the use of "Managed" data providers, and provides instructions for bringing an Excel spreadsheet into the BI LaunchPad, refreshing the spreadsheet, and using it as a data provider for a WebI report. If you need to connect a WebI report to an "Unmanaged" data provider, please contact the Center for DATA WebI team at https://td.usnh.edu/TDClient/Requests/ServiceDet?ID=142, or by calling the IT Service Desk at 862-4242.

Instructions:

Excel can be used as a data provider for a WebI report, but you must follow the best practices below, otherwise you could lose a report you have created:

• Your Excel workbook must be stored in WebI in your "My Favorites" folder, or in a "My Favorites" subfolder, or it can be in a published reports folder.
• Once you have used an Excel workbook as a data provider for a report, do not delete the Excel workbook from WebI BI Launchpad. If you delete the workbook, your report will not work and it will not be recoverable. You can move the Excel workbook to another WebI folder, or rename it, but do not delete it.
• Do not restructure the data in your Excel Workbook.
  • You can add rows as needed.
  • You can add columns , but do not insert them in front of other data.
  • You can add tabs.
  • Do not delete, rename, or reorganize existing columns and tabs.

We recommend you test the steps below and make sure you are comfortable with the procedures before developing a critical report.

A) Adding an Excel Workbook to the BI Launchpad

Below are the steps for importing a workbook into your WebI account. Each time you need to update the workbook, you must follow the steps in Section B, "Refreshing the Data in the BI Launchpad's Excel Workbook".

1. Within the Excel workbook, your column header titles should start in Column A, Row 1. Column titles should be short and descriptive (i.e. L_NAME, F_NAME). The second row will be your first row of data. Enter all of your data into the Excel workbook.
2. Save the Excel workbook to a network drive. You can also save to BOX as long as your BOX folder is available in your File Explorer. See the Box Drive Knowledge Base article for more information.
3. Then, within WebI's BI Launchpad:
      a) Navigate to the Documents tab
      b) Click the My Documents drawer
      c) Right-click in a sub-folder, and select New, and then select Local Document
      d) Browse to the Excel workbook and click Open
      e) Enter the title of the Excel workbook. This is what you will see in your WebI folder. You can also enter a report description and keywords; however, keywords are not currently searchable.
      f) Click Add in the bottom right

B) Refreshing the Data in the BI Launchpad's Excel Workbook

You cannot maintain the Excel workbook within the BI LaunchPad. You must update your workbook's data in its current location (network drive or BOX folder), then refresh the BI LaunchPad's version of the workbook, using the steps below:

1. Open the Excel workbook from the network drive or BOX folder
2. Update your data as needed. Do not rename, move, insert, or delete columns or tabs. You may add columns to the end of the spreadsheet and tabs at the end.
3. Save the workbook in a location where you will find it.
4. In WebI's BI Launchpad, select the folder that contains the Excel Workbook. Right-click the workbook, and from the shortcut menu, select Organize, and then select Replace File.
5. Browse to the new version of the Excel workbook and click "Open". In the bottom-right of the dialog box, click "Replace".
6. You will get a message "Replacing this file will overwrite the existing file. Do you want to continue?" Click "OK".

C. Using an Excel Workbook as a Data Provider for a WebI Report

1. Select Web Intelligence from the "Applications" menu at the top of your window (to the right of your name).
2. Click the New report icon (which looks like a sheet of paper) in the upper left
   
3. The "Create a Document" dialog box will display. Select Excel and click OK.
   
4. Under File, browse to the the Excel workbook that you stored in WebI BI Launchpad and click "Open". The Custom Data Provider Dialog Box will display. If you have have not imported the workbook into the repository, close the window and follow the steps above, and then return to these steps.
   
5. Select a Sheet Name from the drop-down menu. You can select only one. If you need to use more than one, then you must create a report that uses more than one data provider.
6. "All Fields" is the recommended radio button to select in the Fields Selection area. Selecting a range definition will cause you issues if you add rows or columns later and is not recommended. Selecting a named range is fine, but you will need to set up the named range in the original Excel workbook AND remember to change it whenever you update your rows and columns.
7. Leave the box for "First row contains column names" checked.
8. Click OK. The Query Panel for Excel Data Providers displays.
   
9. There are three panels:
      a) On the left is the Object Properties panel:
           - In the top section, you can select a result object to change its name, qualification, type and aggregation. You can also set the object as a dimension related to another object.
           - Under Query properties, you can rename the query or change its source path. If you don't want the query to be refreshable, uncheck the "Refreshable" box, though this is usually not needed.
           - Query Definition allows you to change the Excel spreadsheet (tab) and the range of the data.
      b) The top right panel displays the columns on your selected spreadsheet. These objects can only be removed and updated by modifying your original Excel workbook and then refreshing the WebI workbook.
      c) After clicking the "refresh" arrows, the bottom right panel displays a sample of your data.
10. You can only filter data rows or columns either within the source Excel file or by using report filters and input controls. You cannot filter the data or create prompts in the Query Panel.
11. Click Run Query. A report tab with a table of your data will display. From here, you can design your report as you would any other WebI report.

*Need help?*
If you need help, please contact the Center for DATA WebI team at https://td.usnh.edu/TDClient/Requests/ServiceDet?ID=142, or by calling the IT Service Desk at 862-4242.