The processing platform in reports & analytics contains some rollup functionality. However, the number of report suites and allowed tables limits it. By configuring data requests in report builder, high-level data with many report suites can be made available. It involves the following steps:
- Obtaining a list of report suite IDs
- Preparing the workbook for report builder requests
- Creating and configuring a data request
- Copying data requests
- Using Excel's SUM function
If there are only several report suites you'd like to roll up, the easiest way to obtain them is by going to the Star Menu > Admin Tools > Report Suites. Each report suite's friendly name and its associated ID is available; copy and paste each into your workbook.
If the number of desired report suites runs into the hundreds, have one of your organization's supported users contact Customer Care. There are specific internal tools available that provide the ability to gain a list of all RSIDs within a login company.
Once the list of report suite IDs is obtained, place them all in column A within your workbook.
When you have the list of report suite IDs you want to roll up in column A, do the following:
- Highlight all of columns B and C, right-click, and choose Format Cells. Set the type to Date, and click OK.
- Place the start date of the data you want to retrieve in cell B1, and the end date in cell C1.
- In cell B2, input the value
=C1likewise in cell C2. B2 and C2 now reference the cells immediately above them.
- Highlight cell B2, and drag the lower-right corner of the cell to the bottom of the list. All of column B now references the same date. Do the same for cell C2.
Note: Dragging cells B1 or C1 causes the date ranges to linearly increase, which is not desired results.
- If done right, changing cell B1 or C1 changes its entire corresponding column.
So far your workbook is expected to have the following:
- Column A: A list of all report suite IDs to include in the rollup
- Column B: The start date
- Column C: The end date
Log in to ReportBuilder, and create a request. Use the following settings for step 1 of the Request wizard:
- Report suite references
- Select Dates from Cell references
'Sheet1'!$C$1(Cells B1 and C1)
Step 2 of the wizard looks similar to the following:
- Insert location is
- Format Options set to None
- Metric Headers are hidden (click the icon next to the red X)
- A single metric under Metrics
Now that one request is complete and configured correctly, copy it across all rows:
- Right-click cell D1, and click Copy Request.
- Highlight cells D2 through the end of the list.
- Right-click anywhere in the selection, and click Paste Request > Use Relative Input Cell.
- Give it a minute or two to copy the request across all rows. Once complete, the data from the first request is copied: this copying is expected.
- Under the Add-ins tab, click Refresh. The workbook refreshes all requests according to the report suite in column A. This refresh can take anywhere between a couple minutes with a dozen report suites to an hour or more with hundreds of report suites.
Note: Do not click Manage unless your workbook has less than 100 requests. ReportBuilder's request manager times out with hundreds of requests.
When all data is successfully refreshed, simply sum each column to obtain the desired data:
- Locate any empty cell, and type
- Without losing focus on the cell input, select all cells in column D. Close the parenthesis and press Enter.