Import custom data parameters

Available with Business Analyst license.

A Statistical Data Collection in ArcGIS Business Analyst Pro can contain hundreds of variables, depending on the level of detail in your statistical data. For example, if you are working with the sales data for 20 products over 12 quarters, that's 240 variables. You can set up the properties of all these variables using the ArcGIS Business Analyst Pro editor window directly or using a list of variables in an Excel spreadsheet.

This help topic describes the Excel spreadsheet method. To import custom data parameters, you will set up all required properties in the spreadsheet by adding and populating the required columns. You can then use the Import Parameters functionality to edit all variables at once.

Tip:

You can edit properties for multiple variables at once in Excel. Select multiple variables by clicking their names in the VARIABLE column, right-click the selection, and click Properties. Any updates made on the Change in Properties dialog box are applied to all selected variables.

Importing parameters from a table allows you to assign values for multiple custom data variables and properties simultaneously. The variable properties can be stored in an Excel spreadsheet, or any other tabular format, and used for multiple Statistical Data Collection files. It is recommended that you use Excel tables to set up the parameters, as it allows you to edit multiple rows at once. A single Excel workbook file can have multiple spreadsheets in it, and each spreadsheet is shown as a separate table on the Import Parameters dialog box.

To import custom data parameters from a spreadsheet, do the following:

1. On the View tab, click the Catalog Pane button .

   The Catalog pane appears.

2. On the Project tab, browse to the folder containing the Statistical Data Collection used in the project. Right-click the Statistical Data Collection, then click Edit.

   The Statistical Data Collection editor window appears.

3. Click the Variables tab.

   The variables table appears with the variable parameters and their values.

4. Click the Import Parameters button.

   The Import Parameters window appears.

5. Browse to your table, select it, and click OK.

   Values from the imported table populate the respective parameter columns.

Requirements for the parameters table

A parameters table contains the following fields:

Field name	Parameter
VARIABLE	Variable name—Each parameters table must have a column named VARIABLE. This column identifies the variable receiving parameter settings. Values in the VARIABLE column must be unique.
CATEGORY	Category—The category to which the variable is assigned. Categories are displayed in the data browser.
ALIAS	Alias—An alternative name specified for a variable that is more descriptive and user-friendly than the variable name.
SUMMARYTYPE	Summary type—Sum, Script, or Average. This specifies whether the variable represents a number (sum), a formula for a calculated variable (script), or an average.
BASETYPE	Apportionment method—An apportionment layer is a point feature layer containing a weight field that is used in Statistical Data Collections to estimate and aggregate data to other layers. For a complete list of apportionment methods, see Apportionment layers.
DECIMALS	Decimals—The number of decimal places to the right of the decimal point in a variable's value.
FORMULA	Script/Formula—Contains the formula for calculated variables, such as sums of other variables in the spreadsheet.
WEIGHTFIELD	Weight—Indicates the field used for calculating an average. For example, if the variable represents the average sales per customer, the weight field is the total number of customers. If the summary type is Average (Avg), then the weight parameter must be populated.

Additional considerations

When creating a parameters table, it is critical that you define the fields correctly. The explanations and examples below offer further information for some types of variables.

Calculated variables

For calculated variables, the value in the VARIABLE column will be created and added to the bottom of the list, if missing. For example, if your Statistical Data Collection variable already contains variables named SALES_Q1_2023 and SALES_Q2_2023, and you want to add a calculated variable that will sum these, your spreadsheet must include the following three rows:

VARIABLE	ALIAS	SUMMARYTYPE	FORMULA
SALES_Q1_2023	Sales Q1 2023	Sum
SALES_Q2_2023	Sales Q2 2023	Sum
SALES_H1_2023	Sales H1 2023	Script	!SALES_Q1_2023! + !SALES_Q2_2023!

Averages

To create a variable representing an average—such as average sales per customer—you can use different methods. Either of the following will work:

Note:

An average variable must include a value in the WEIGHTFIELD column.

• If you have the averaged variable in your data already, set SUMMARYTYPE as Avg and set WEIGHTFIELD as the field that was used to calculate the average.

  VARIABLE	ALIAS	SUMMARYTYPE	WEIGHTFIELD
  TOT_CUSTOMERS	Total Customers	Sum
  AVG_SALES	Average Sales per Customer	Avg	TOT_CUSTOMERS

• If you want to calculate the averaged variable using data in the spreadsheet, set SUMMARYTYPE as Script, use the FORMULA field to provide the calculation, and set WEIGHTFIELD as the field used to calculate the average.

  VARIABLE	ALIAS	SUMMARYTYPE	FORMULA	WEIGHTFIELD
  TOT_CUSTOMERS	Total Customers	Sum
  TOT_SALES	Total Sales	Sum
  AVG_SALES	Average Sales per Customer	Script	!TOT_SALES! / !TOT_CUSTOMERS!	TOT_CUSTOMERS

Parameters table example

You can download, modify, and import an example parameters table using the spreadsheet below:

• Custom data parameters table (Excel file)