Use Parameters in an Excel Data Extraction API Request

You can create a URI with a parameter instead of a specific value.

You can link a parameterized value to a cell so that the value updates when the worksheet is refreshed. To view multiple time periods for a report, you could create a single URL with a parameterized time period, so that Excel prompts you to change the time period value when you refresh the data.

1. Create the URL for the report you want. Specify the HTML format, and ensure that you have a valid start period and end period for the report (data may not exist for all time periods).
2. Follow the instructions in Import Data Into Excel.
3. Identify the value(s) in the URL that you want to change to a parameter. An example with the start_period value is shown below:
   https://myDomain.com/{version}/ReportService/profiles/CxoSsHxKlF6/reports/42df19b6d9f2/?start_period=2023m06d01&end_period=CURRENT_DAY-1&format=html&suppress_error_codes=true
4. Copy the URL, and edit the copy of the URL to replace the values with square brackets. For example:
   https://myDomain.com/{version}/ReportService/profiles/CxoSsHxKlF6/reports/42df19b6d9f2/?start_period=[]&end_period=CURRENT_DAY-1&format=html&suppress_error_codes=true
5. Select "Data" from the menu ribbon.
6. Click the text with the down arrow "Refresh All" and then click "Connection Properties".
7. Click on the Defintion tab.
8. Click "Edit Query".
9. Replace the URL in the Address bar with the address you edited.
10. On preview, an invalid page request error occurs. Ignore this and click Import.
11. Click "Parameters...".
12. Edit each parameter (in order of how they appear in the URL). Add a value under "Prompt for value using the following string:"
13. Click "OK".
14. Click "Refresh All"
15. A prompt appears
16. Provide a valid value for the parameter and click "OK". Your spreadsheet will show report data.