Update data used in an imported report data source - Support and Troubleshooting

Update data used in an imported report data source Issue  Description Because the data associated with an external spreadsheet is static and has an expiry date associated with it, it will probably be necessary to update a report that is based on this external data such as to contain the current data and modify the expiration date. This article will detail the steps on how this can be done.

Procedure Note that a report based on an external data source can only be edited by the user who originally created that report.

Once the report record is open for editing, click the Data tab.

On the Data tab, click the Edit external import icon.

The Edit external import window will appear, populated with the previous data.

If you want to update or change the source spreadsheet file, click the button titled Change file .

A file browser window will appear. Browse to the location of the modified or new file and double click that file name to select it as the new file source for the report.

Note that only files with an .xlsx extension may be selected for import. Also, if the file is not of the expected mime type format, the file will fail to import giving an import failed message. In that case the spreadsheet file will need to be modified to ensure it conforms to a format that the system can use.

In addition, ensue the Expire field is set as needed and that the Visible to field is configured for the correct viewership permissions for the report.

If the spreadsheet used to load the new data is not of the same format (including the column headings) as the previous spreadsheet used in this report, you will probably need to update any grouping, stacking or filtering options as selected for the report, as those fields were populated based on dynamic internal field "names" the system generated at the time of the previous spreadsheet data import. The best way to perform this is to Save the report, exit the report and re-open the same report record for editing. After re-opening verify that these settings were retained, and if not, reconfigure these options as needed. Clicking the Run button will allow the report to be viewed before saving.

Once any necessary changes have been made to the report and clicking Run displays the report as expected, click Save to save all changes to the report.

A few things to keep in mind in working with externally imported spreadsheets:

These files are static once imported into the instance. Thus, if the data is changed on the source spreadsheet and the report should reflect these changes, the procedures detailed above will need to be performed in order to re-import the updated data into the ServiceNow instance. Only the new Report Designer User interface can be used to create and update a report based on an externally and temporarily loaded spreadsheet data source. The report is limited to calculations based on the first 10,000 rows in the spreadsheet. Any additional rows will be ignored and not considered in the report data. If the spreadsheet contains more than 50,000 rows by default or 25 total columns, the spreadsheet data import will fail. Once the external data source exceeds the Expire date as specified in the record, the imported data source will be removed from the instance and any reports based on that data source will be inaccessible. Thus, a newer version of the spreadsheet data source will need to imported or the Expires field on the imported data record will need to be modified before the data source expires. Example As with the initial creation of such a report, the process can probably best be understood using an example. Thus, the following steps will detail the step-by-step process used in updating a report based on an external spreadsheet data source.

Perhaps we had a basic report in the system based on a spreadsheet which was showing a graphical display of the number of accounts based on location, as follows:

Since this report is designed to display the "new" clients, the previous data will become stale after a period of time and we will want to update the report with the new, appropriate data. Thus, the first step is to update the source spreadsheet that will be used for the data is updated with the new data. This can be either updating the data in the spreadsheet previously used for the report or creating an entirely new spreadsheet containing the newest version of the data. In this case, we will want to show the location counts for the newest clients (in the last month). Thus, we have created a new spreadsheet containing the data pertaining to the newest clients, as follows:

After all updates to the external source spreadsheet have been completed and the spreadsheet saved, we login to the ServiceNow instance with an account having report_admin right on that instance.

Open the report record for editing that you intend to update with the new data from the updated spreadsheet. Note that if a report based on an external data source has exceeded the expiry date, the report will no longer be found on the instance.

We then select the Data tab of the report record and click the Edit external import icon to the right of the External import field.

At the Edit external import dialog window, we click the Change files button in order to associate a new file with this report. We also ensure the Expiration is set to expire in one year and modify the Data Source name to reflect the new import file.

After updating the form as needed, we click the Submit button, which will return to the report record.

In order to ensure the report is still configured as needed, we close the report and re-open it. Checking the Group by field we see that it has cleared that field, indicating that when the new spreadsheet was imported it used another internal name representation. Thus, we again select the option to Group by Company Location. After doing so, clicking Run displays the new report as expected.

Once satisfied with the report and it's format click the Save button to save the changes to the report and which will also display the new version of the report.

Additional Information For information regarding the initial creation of a report using an external spreadsheet file, please see the following article:

KB0722420 - How to create a report based on data from an external Excel Spreadsheet

The following article describes the process to update the Expires date for an externally imported spreadsheet data source:

KB0722490 - Update the Expiry date for an externalliy imported spreadsheet report data source