Read By Excel Range

Reporting Design Cookbook Data Design
1

The recipe illustrates various ways of reading in an Excel Spreadsheet. An Excel DS can read in data from an Excel spreadsheet by controlling the range, either “=” or the Name (as defined in the xls file) can be used. In addition, the Excel DataSource Range field can be parameterized to control dynamic worksheet input.

Steps taken:

  1. Create an Excel DataSource (Store(Excel) – Range.ds). Define the URL which links to the Store-3D.xls file. Enter the following content into the “Range” field:${Range#choice(All,Canada,Mexico,USA)#All}
  2. Create an Excel DataSource (Store(Excel) – Read All.ds). Define the URL which links to the Store-3D.xls file. Enter the following content into the “Range” field:=
  3. Create an Excel DataSource (Store(Excel) – WS-USA.ds). Defien the URL which links to the Store-3D.xls file. Enter the following content into the “Range” field:=WS-USA
  4. Create an Excel DataSource (Store (Excel) - Worksheet.ds). Defien the URL which links to the Store-3D.xls file. Enter the following content into the “Range” field:=${Worksheet#choice(WS-Canada,WS-USA,WS-Mexico)#WS-Canda}

Observe the output respectively.

To download the necessary files for this recipe, refer to the attached ZIP package.
ReadByExcelRange.zip