How to create a report based on data from an external Excel Spreadsheet - Support and Troubleshooting

How to create a report based on data from an external Excel Spreadsheet Issue The capability exists within the ServiceNow platform to create a report based directly on data from an external spreadsheet file (such as a spreadsheet file created in the Microsoft Excel application).

This article will describe the steps to create such a report in the system as well as the requirements, limitations, and stipulations that must be followed when doing so.

Procedure To begin, the spreadsheet file should be ready for import. The following are some requirements of this spreadsheet file:

The file must be in a .xlsx or similar file structure and have a .xlsx file extension. The maximum size of the spreadsheet file which is to be used as the data source is 2 MegaBytes. The import process is currently designed to import the first 10,000 rows (including a header row at the top) from the spreadsheet and use these rows in the resultant report. If more rows are included, the additional rows will be ignored. However, if the total number of rows on the spreadsheet is over 50,000 the import will fail. The maximum number of columns that can be included in the spreadsheet is 25. The first row should contain the individual headers for the data to be found in the remainder of that column. This column header is thus used to identify and label the column names in the displayed report. Similarly, the first column should contain the headers for the data in each row. This row header will thus be used to identify each row and this header will be used in the label of any resultant reports. Once the spreadsheet has been imported through the report import utility, no column level changes can be made to the external data source (columnar joins, application of formulae, etc). Thus, the spreadsheet data should be in a format ready for import at the time of running the import utility. Only the first page or tab of the spreadsheet will be considered during the import process. Other pages or tabs will be ignored. A report based on an external spreadsheet data source can only be created from the new report Designer UI. Once the spreadsheet file is in the proper configuration and contains the data which is intended to be used for the finished report, it can then be used as an import report source into the instance. The following steps will show how this might be done:

In order to use this functionality of the system, the Performance Analytics - Premium plugin must be activated on the instance.

Log into the ServiceNow instance. Ensure to use an account that includes the admin, sys_admin, report_admin, pa_admin, or pa_power_user roles.

Once logged into the instance, browse to the following location: Reports -> Create New .

As in any report, in the Report Name field provide a descriptive name for this report.

In the Source type field, select the option from the pull-down menu titled External import .

Click the Upload file icon to import the spreadsheet data.

Note that if you already have imported the spreadsheet as a data source you can instead select, in the External import field, an existing data source for the report.

Use the Browse files button to browse to and select the spreadsheet file you intend to use as the source of data for this report. Note that you can also simply drag the spreadsheet file directly onto the file drop zone in the import data source window that is currently displayed.

In this same window, in the Name field, add a Name to identify this import dataset within the ServiceNow instance.

In the Expire field, use the pull-down to select the appropriate expiry period for this imported data set. After the period of time specified in this Expire field elapses, the internal data source associated with the external data source will be deleted from within ServiceNow and this or any other reports that are using that imported data will no longer be accessible on the instance.

In the Visible to field, select the individuals or groups to who you want to grant access. The selection can be just you (the importer, all users, or specific groups, users, or roles).

Note that if the Custom option is selected in the Visible to section, the dialog window will change showing an interface for which specific individuals, groups, and roles can be selected who will have permission for viewing of this report.

After filling out the fields on the import dialog box and selecting the file that will be the source of the report, click the Upload button.

After the upload completes, click the Done button.

Click the Next button on the report import dialog box.

From the various report types listed, select the type of report you intend to create (such as List, Bar, Pie, Donut, Pareto, etc).

An example of how the report will appear will be displayed.

Select the Configure tab to configure various aspects of the specific report type as selected previously for this report as well as provide any additional filtering that might be needed to further limit the data to be displayed in the report.

Similarly, on the Style tab configure any additional settings to adjust the colors, labeling, and other visual adornments for the report.

To complete the creation of the report and display the final result, click the Save button.

Note that since the data source is a static spreadsheet which has been imported into the instance, modifications to the spreadsheet itself will not modify or change the report as displayed in the instance. However, the report itself can be modified and a newer version of the spreadsheet can be redownloaded and updated as the data source for the report. After doing so, the report would update to show the data from the current copy of the spreadsheet which has been downloaded as the data source for this report.

Example Since these steps may not necessarily be intuitive, a simple example will be very helpful in illustrating this process.

In this example, we will be creating a basic report of the newest retail clients for a hypothetical furniture manufacturer.

To begin, we will first examine the spreadsheet from which the data to be used for the final report will be extracted.

The source spreadsheet can be thought of as a primitive database with each row corresponding to a record in a table and each column a specific field in that table. The top row consists of the name of that "field". For our report, the end result is to be a report showing a count of the number of these new companies that are in each Company Location (State Name).

This procedure for creating a report in ServiceNow directly from data in a spreadsheet will only work effectively with a spreadsheet that is configured in this way. For more complex spreadsheet data sources, the normal data import and mapping procedures should be used to import the data from the spreadsheet into ServiceNow.

After reviewing the spreadsheet, we are then ready to create the report based on this data. We will next log into the system with an account that is a member of the report_admin group in the instance.

Using the Menu Navigator on the instance, we browse to Reports then Create New to begin the creation of a new report.

We provide a Report Name for the report and for the Source type External import is selected.

We then select the Upload a file icon to select our spreadsheet data source.

Clicking the icon will display the Upload external source dialog window. We click the Browse files button which then displays a file browser dialog we can use to browse to and select the spreadsheet file which we intend to use as our data source for the report.

Once a file is selected the Upload external source dialog box appears.

We provide a Name for this data source (which will identify it in the instance) and select an Expire time from the pull-down menu. The election in this field will determine how long this data source will remain active and available on the instance. After this time has been exceeded (starting with the day the file import and report is created) the imported data and the associated reports will no longer be accessible on the instance.

Lastly, on this form, in the Visible to field select the radio button which corresponds to the user base who should have access to the final report. In this case, we prefer that all user of the instance are able to view the report so we select the Everyone option.

If we had selected the Custom option, another window would appear prompting for the addition of the Users, Groups, or Role membership which would be required for viewership of this report.

When the fields on the dialog box have been filled out as necessary, we click the Upload button to complete the upload.

After a few moments the upload has completed and the Import Successful screen appears. Clicking the Done button on the screen returns us to the Create a report form.

A preliminary, view of the report will appear using the default report type and options. In this case, we want to display the data in a Bar chart format, so we click the Type tab and select the vertical Bar graph option.

After selecting the graph type, we select the Configure tab to set the grouping and aggregation option. For this report, we want to Group by the Company Location field and Aggregate on the Count at each Location so we select these options in the fields.

Finally, we select the Style tab and configure various aspects of the appearance of the report.

When finished with the configuration of the report, we click the Save button to save the report on the instance as well as run the completed report.

Resolution Much of the information for this article has been derived from the following ServiceNow documentation: Create a report from an imported Microsoft Excel document