Skip to main content

Spreadsheet Server Suite

This article contains information specific to installing and configuring the Spreadsheet Server Suite.

Download and install Spreadsheet Server Suite

If you have not already completed the basic installation steps and installed the Spreadsheet Server, Application Configurator, and Cloud Connector applications, you will need to do so before proceeding.

The Spreadsheet Server Epicor install package with Epicor content, including profile files, documentation, and sample reports, can be downloaded at: https://download.insightsoftware.com/installation/spreadsheet-server/epicor/.

Installation

    1. Unzip the Spreadsheet Server [version] Epicor.zip package and open Spreadsheet Server Suite.exe.
    2. Select all setup types (Spreadsheet Server, Application Configurator, and Cloud Connector), and click Next.
    3. Accept the Software License Agreement and proceed with the installation.

      4. Note: You must scroll all the way through the agreement to select I Agree.

After successful installation you will find the applications in the Windows start menu.

Configure Cloud Connector

The following are the set up steps required for Spreadsheet Server with Cloud Connector. If using an on-premise version, skip this section).

Configure the SaaS URL

Open the application and log in using your replication URL:

  • Host: your replication URL
  • Port: 443
  • SSL: On
  • Trust Store: leave blank

    • To obtain configuration access, you must log in using your email and the password you created during the MFA process.

Configure the Source

  1. Click Edit Sources.

  2. Click Add New Source.
  3. Click the Connection Source drop-down list, and select Epicor ERP Cloud.
  4. Enter the following connection details:
    • Source Name:
      • A label for the source. This may be any name you choose.
      • It is recommended to include the Epicor Kinetic Cloud environment type in the source name (for example, Production, Pilot, Third, etc.).
    • User Name: the user name of the Epicor Kinetic Cloud Security Manager.
    • Password: the Security Manager user’s password.
    • Service URL:
      • The service URL of the Epicor Kinetic Cloud instance.
    • Company:
      • Any active company code from Epicor Kinetic Cloud may be used.
      • Regardless of the company code used, data will be replicated for all active companies the Security Manager user has access to.
    • API Key: the API key generated in Epicor Kinetic Cloud.
  5. Click Save.

Test the Replication

After configuring the SaaS URL and Source in Cloud Connector, a default replication task will be automatically loaded into Cloud Connector. To test execute the replication task, click Run All Tasks.

  • Cloud Connector will run the tasks to replicate data from Epicor to the Cloud Connector database.
  • The time needed for tasks to run depends on several factors, including: database size, table size, the number of tables replicating, and how much data has been added since the last replication task was run.
  • The first replication task (and force rebuilds) will be a larger data pull and should be expected to take longer to complete (up to several hours).
  • After the first replication, only incremental changes will be replicated, and the process should occur much quicker.

  • For more information, see Appendix A: Editing Tasks.

Schedule the Replication Task

Once a task has been configured, you can schedule the replication task to run on a recurring schedule:

    1. Click Edit.
    2. Click the Schedule tab.
    3. To enable scheduling, click On.

      4. Note: The default replication is hourly, but this can be adjusted as desired.

    4. Click Save.

Configure Application Configurator

Application Configurator is the central point of administration for Spreadsheet Server. It is used to create and maintain license keys, profiles, and security settings for users, database connections, user IDs, user passwords, application settings, administrative passwords, and security controls.

  • Application Configurator does not require a license to use.
  • Anyone may use Application Configurator, though it is recommended that it be installed on one device only to limit use by multiple users. Having multiple users can cause inconsistent configuration changes or corrupt the DotNetSecurity.xml and DotNetConnections.xml key configuration files.
  • Most often, Application Configurator is installed on a Spreadsheet Server administrator's machine; this could be a Spreadsheet Server power user or an IT member, or installed on a server under a Windows administrator user, which multiple IT members may access as needed, while limiting to a single user accessing at any given time.
  • Application Configurator can be opened by clicking the Windows Start Menu > Spreadsheet Server Suite > Application Configurator.

  • When Application Configurator is launched, the Administrator Login is prompt appears.
  • The default User Name is Admin and the Password is gsi, unless later modified in the Application Configurator security settings.

  1. In the Users tab, click Import From Text and import the license key provided via email by insightsoftware.
  2. Double-click the license and assign it to the user’s Windows log in.
  3. In the Connections tab, click New and enter the following information:

    4. Epicor Kinetic Cloud

    • Connection Type: CloudConnectorSaaS
    • Connection Name: type a display name of your choice for the connection.
      EPICOR KINETIC CLOUD is recommended, however, any name may be set which could be helpful if you need to configure and distinguish between multiple connections.
    • Host Name/IP Address: type the Cloud Connector Replication Service URL provided by insightsoftware, that was also used in the Cloud Connector setup. For example, https://company-prod000-reporting.cc.insightsoftware.com.
    • Port: 443
    • SQL Syntax: PostgreSQL

    Epicor Kinetic On-Premise

    • Connection Type: SQL Server
    • Connection Name: type a display name of your choice for the connection.
      EPICOR KINETIC ONPREM is recommended, however, any name may be set which could be helpful if you need to configure and distinguish between multiple connections.
    • Host Name/IP Address: the SQL Server ServerName/InstanceName or IP Address/InstanceName where the Epicor Kinetic database is hosted.
    • Username and Password: type a SQL user name and password that has db_datareader rights to the Epicor database.

        Note: This SQL username and password will be used by all users’ Spreadsheet Server installations to access the Epicor database.

      • If SQL security is being administered differently for certain users, you may need to use a different authentication option.
      • Select the Use Windows Integrated Security check box to use Windows authentication for user connections to the Epicor database.
      • Select the Prompt for Input check box to prompt users for to enter the user name and password to connect to the Epicor database.
    • Database : the name of your Epicor database.
  4. Click Test, then check the Results to verify the connection succeeded. If the connection is unsuccessful, review all connection details entered in the previous step for accuracy.
  5. Epicor Kinetic Cloud: when testing, you may receive a pop-up window with the Cloud Connector sign in page if you have not authenticated in Cloud Connector recently. Complete the log in and the test will run.
    Epicor Kinetic On-Premise: verify the SQL or Windows user has been given db_datareader permissions to the Epicor database.

  6. Click Save to save the connection.
  7. When prompted, click Yes to push the changes to the network.

Deploy Epicor Kinetic Profile

Spreadsheet Server requires a set of Epicor Kinetic profile files deployed to the security folder to pull Epicor Kinetic data.

The exact process for this is varies slightly, depending if Application Configurator has been configured to use a local or network share location for the security folder, and if using Epicor Kinetic Cloud or On-Premise. Follow the steps below to deploy the Epicor Kinetic profile files:

Locate the Security Folder

  1. If Application Configurator was configured to use a network share for the security folder, the security folder location can be identified in the application. This is the recommended and most common configuration.
  2. Open Application Configurator, by clicking the Windows Start Menu > Spreadsheet Server Suite > Application Configurator.
  3. Click File > Settings.
  4. In the General tab, copy the folder location from the first setting in the Security File Network Folder field.
  5. Open Windows File Explorer and navigate to the security folder (if Application Configurator has been installed locally, the folder is located at: C:\Users\Public\Public Documents\Global Software Inc\Spreadsheet Server).
  6. Open a second instance of Windows File Explorer and locate the SpreadsheetServerEpicor[Date].zip package. All Epicor Kinetic profile files used in the next step are contained within this package.
  7. Deploy the Epicor Kinetic profile files:

    8. Copy the Epicor Kinetic Cloud or On-Premise profile files (corresponding to your Epicor system) to the subfolders within the security folder as detailed in Recommended Shared Folder Structure.

    Note: During Application Configurator setup, the Configuration Wizard will have already prompted for and deployed the Epicor Kinetic base profile file. However, this base profile file has been listed below again for reference, along with additional ad hoc and subquery files that need to be deployed manually.

Epicor Kinetic Cloud

  • CustomGL folder (Base Profile)
    • KINETIC_CLOUD.xml
    • KINETIC_CLOUD_Unposted.xml
  • AdHocQueries folder (Ad Hoc Queries)
    • KINETIC_CLOUD_Acct_Description.xml
    • KINETIC_CLOUD_Journals.xml
    • KINETIC_CLOUD_Unposted_Journals.xml
  • Query Designer\Queries folder (Subqueries)
    • KINETIC_CLOUD_QUERYNAME.edq

Epicor Kinetic On-Premise

  • CustomGL folder (Base Profile)
    • KINETIC_ONPREM.xml
    • KINETIC_ONPREM_Unposted.xml
  • AdHocQueries folder (Ad Hoc Queries)
    • KINETIC_ ONPREM_Acct_Description.xml
    • KINETIC_ ONPREM_Journals.xml
    • KINETIC_ ONPREM_Unposted_Journals.xml
  • Query Designer\Queries folder (Subqueries)
    • KINETIC_ ONPREM_QUERYNAME.edq

Recommended Shared Folder Structure

Configure the Epicor Kinetic Profile

The steps detailed below walk through configuring the Epicor Kinetic profile, including setting the connection, database (for on-premise), budget codes, and assigning the default profile.

Before beginning this process, the following prerequisites must be complete:

  • Spreadsheet Server must be installed for the user.
  • The user must be assigned a license in Application Configurator.
  • A connection must be configured in Application Configurator.

Important: If the profile files are stored in a network share security folder, this process only needs to be completed once by a single user and will then be applied for all other users connecting to the same network share (additional users only need to complete steps 1-8). If the profile files are stored in a local security folder, this process must be repeated by each user individually.

  1. Open a new blank worksheet in Excel.
  2. Click the Spreadsheet Server ribbon tab.
  3. Click Settings.

    4. Note: Steps 4-8 are only necessary if using a network share security folder. Skip to Navigate to the CustomGL folder where the profile files were previously deployed. Select profile.xml and click Open. if using a local security folder, as the network security and connection files will be configured to the local security folder by default.

  4. In the Settings window, click Browse.
  5. You will be prompted to enter the administrator password. The default password is gsi, unless later modified in the Application Configurator security settings. Type the password in the field and click OK.
  6. Navigate to the network share folder, select the DotNetSecurity.xml file, then click Open.
  7. Click the Designer GL tab in Settings, then Browse for GL Definition.
  8. You will be prompted to enter the administrator password (gsi, unless modified) once again. Type the password in the field and click OK.
  9. Navigate to the CustomGL folder where the profile files were previously deployed. Select profile.xml and click Open.
    • Network Share Security Folder: the CustomGL folder should be located within your network share folder.
    • Local Security Folder: the folder is located at: C:\Users\Public\Public Documents\Global Software Inc\Spreadsheet Server\CustomGL.C:\Users\Public\Public Documents\Global Software Inc\Spreadsheet Server\CustomGL.
  10. The profile details will then load in the Designer GL tab. Click the Connection Name drop-down list, and select the desired connection.
  11. Set the Key 1 Database values:
    • Kinetic Cloud: this field is not applicable in the Kinetic Cloud profile. Skip to the next step.
    • Kinetic On-Premise: click the Key 1 Data Validation field and replace the value with the exact Kinetic database name. If multiple databases need to be connected to (for example, production and pilot), separate the database names with a line break (press Shift + Enter).
  12. Click the Key 2 Data Validation field and type each Balance Type needed (such as budget codes) exactly as it is named within Epicor. Include a line break between each Balance Type.
  13. Click Save. A dialog will appear and confirm the save was successful. Click OK.
  14. An additional prompt will appear if the profile should be set as the Default Designer GL; see the option explanations below. This is a configuration choice, however, if you are uncertain, it is recommended to set the GLPeriodBal profile (the base profile) as the Default Designer GL, as this is generally used the most.
    • Yes: set the profile as the Default Designer GL when opening Formula Builder to create new formulas.
    • No: if another profile is already set as the Default Designer GL and you do not want to modify this.
  15. Repeat steps 9-15 for any additional profiles that need to be configured.

Update Epicor Kinetic Profile

Occasionally, it may be necessary to update the Epicor Kinetic profiles if a custom profile has been created, or if directed to do so by support to implement a bug fix. Follow the steps below to update the profile files.

Important: Some profile updates may have significant changes to the segments that will cause existing formulas created with the original profile to not be compatible with the new profile. As a result, existing workbooks need to be rebuilt with updated formulas using the new profile; insightsoftware support will communicate this to you or be able to help confirm if that is the case. If existing formulas are not compatible with the new profile, it is recommended to keep the original profile in place, and add the new profile alongside it (this may require renaming the profile File Name in the Designer GL settings to maintain two files with different names). By maintaining both the original profile and new profile, workbooks with existing formulas can be run with the original profile as a point of reference. New copies of the workbooks can then be created, the existing formulas deleted, and Formula Builder can be used to create new formulas compatible with the new profile. Once all workbooks have been rebuilt to be compatible with the new profile, it is recommended to create an Archive subfolder within the CustomGL folder and move the old profile into this folder.

  1. Follow the Deploy Epicor Kinetic Profile steps to deploy the new profile files provided by Epicor or insightsoftware support. Follow the guidance provided by support regarding which folder the profile files should be placed in.
  2. Follow the Configure the Epicor Kinetic Profile steps to configure the new profile. During steps 11 and 12, confirm that all database (on-premise only) and balance type values are set to match the original profile. New values may also be added if needed.

Open Excel and Enable Spreadsheet Server

At this stage you should be able to open Excel and enable Spreadsheet Server. After doing so, you can begin pulling data from your replicated server into Spreadsheet Server.

Was this article helpful?

We're sorry to hear that.

Powered by Zendesk