EDDIE/Web Intelligence Rich Client - Using Excel as a Data Source
How to create a report using Excel as your data source.
Follow these steps if you have a list of values (e.g., UINs) in an Excel, and you would like to use this list as a query filter.
Prepare the Excel
- If your list is more than 1000 rows, you will need to break it down into individual worksheets of less than 1000 rows each. This is due to an Oracle limit.
- If your list contains UINs, format the cells as text in Excel prior to creating your query.
Create Query using Excel as Data Source
If you are using Web Intelligence Rich Client...
- In the Select a Data Source window, first select Local in the left pane, then select Excel in the right pane and click OK.
- Locate the file on your computer and click Open.
- In the Excel Data Provider window, you can select which worksheet to use and whether or not the first row contains column names.
- If your list was more than 1000 rows, and you separated it into multiple worksheets of less than 1000 rows each, repeat the previous steps to create separate queries for each worksheet to include.
If you are using EDDIE...
In order to use Excel files in EDDIE, they must first be uploaded to EDDIE.
- From the Home Screen, click on the Folders tile and navigate to the Personal folder where you want the file uploaded.
- Click the Create/Upload Objects icon and select Upload Document.
- Locate the file on your computer and click Add.
Once the file has been uploaded to EDDIE, you can use it as a data source in a Web Intelligence document.
- In the Select a Data Source window, leave SAP BI Platform Repository selected in the left pane, select Excel in the right pane and click OK.
- Locate the Excel file you just uploaded, and click Open.
- In the Excel Data Provider window, you can select which worksheet to use and whether or not the first row contains column names.
- If your list was more than 1000 rows, and you separated it into multiple worksheets of less than 1000 rows each, repeat the previous steps to create separate queries for each worksheet to include.
- In the Query Panel, you should see all of the columns in your Excel as objects in the left panel.
- Move any/all objects into the Result Objects panel to be able to use them in reports.
- In this example, there are no filters, because I want to bring over the entire list, but I could filter this list based on values in the spreadsheet as well.
- Click Run to run the query.
Create Second Query
- In the Query section of the Toolbar, click Edit.
- In the Query Panel, click Add Query in the top left corner.
- Select Universe from the Select a Data Source window, and then select the universe from the list.
- In the Query Panel, add whatever objects you'd like to the Result Objects panel, but be sure to include UIN.
- In the Query Filters panel, add any filters you'd like and be sure to add UIN as a query filter as well.
- If you have multiple worksheets of values, see the very last section in this article.
- In the UIN filter, change the operand to Result from another query.
- In the Available Objects window, select UIN and click OK.
- Click Run to run the queries.
Multiple Worksheets
- If you have multiple worksheets of values, you'll need to set up a nested filter.
- Easily create a nested filter by dragging the UIN object from the Result Objects panel and dropping it onto the existing UIN filter (like you are trying to replace it).
- This second UIN filter would be set up like the first, but would be the result from one of the other queries set up for another worksheet.
- Repeat these steps until all value/worksheets have been accounted for.