Use Report Builder functions with Microsoft Excel

Last update: 2024-10-17
  • Created for:
  • User
    Admin
IMPORTANT

A new and streamlined Report Builder was released on October 16, 2024. It is supported on Mac, Windows, and web browsers.
This Legacy Report Builder add-in version still works. You can convert your legacy workbooks to the new Report Builder.

You can use Report Builder functions to access functionality without accessing the Report Builder user interface.

For example, to automatically refresh Report Builder requests with input filters based on data pulled into Excel from other sources, use the string RefreshRequestsInCellsRange(…) function. All calls are asynchronous and they return immediately and do not wait to fully execute.

Requirements

  • Report Builder 5.0 (or later) is required.

The following table lists the exposed functions.

Function Name Type Description
AsyncRefreshAll() string Refreshes all Report Builder requests present in a workbook.
AsyncRefreshRange(string rangeAddressInA1Format) string Refreshes all Report Builder requests present in the specified cell range address (a string expression representing a range of cell in A1 format, for example “Sheet1!A2:A10”).
AsyncRefreshRangeAltTextParam() string Refreshes all Report Builder requests present in the specified cell range that is passed through the Alternative Text of the Ms Form Control.
AsyncRefreshActiveWorksheet() string Refreshes all Report Builder requests present in the active worksheet.
AsyncRefreshWorksheet(string worksheetName) string Refreshes all Report Builder requests present in the specified worksheet (the worksheet name as it appears on the tab.)
AsyncRefreshWorksheetAltTextParam(); string Refreshes all Report Builder requests present in the specific worksheet name that was passed through the Alternative Text of the Ms Form Control
tring GetLastRunStatus() string Returns a string that describes the status of the last run.

To access the Report Builder functions, go to Formulas > Insert Function. Use the search field to search for a function or select a category to list the functions in that category.

Screenshot showing the Insert Function window with the category list expanded.

Example

The following example shows If the value in cell P5 is text or is blank, refresh the range that is in cell P9.

=IF(OR(ISTEXT(P5),ISBLANK(P5)),AsyncRefreshRange("P9"),"")

Use Report Builder functions with format control

You can assign a macro to a control you created and that control can be a function that refreshes a workbook request. For example, the function AsyncRefreshActiveWorksheet will refresh all requests in a worksheet. Sometimes, though, you may want to refresh only certain requests.

  1. Set the macro parameter.
  2. Right-click the control and select Assign Macro.
  3. Enter the Report Builder function name (no parameters or parentheses.)

Screenshot showing the Assign Macro window.

Pass parameters to Report Builder functions using format control

Two functions that take a parameter can be used with Format Control. You must use the Alternative text: field:

  • AsyncRefreshRange(string rangeAddressInA1Format)
  • AsyncRefreshWorksheet(string worksheetName)

To pass parameters to Report Builder functions using format control

  1. Right-click the control and select Format Control.

    Screenshot showing Format Control selected.

  2. Click the Alt Text tab.

    Screenshot showing the Alt Text tab and Alternative text: field.

  3. Under Alternative text, enter the cell range that you want refreshed.

  4. Open the list of Report Builder parameters under Formulas > Insert Function> Adobe.ReportBuilder.Bridge.

  5. Pick one of the two functions that end with AltTextParam and click OK.

On this page