Technics:CoPlanner-Server

A folder, referenced to as "CoPlanner Server directory", has to be configured on the system which will act as the server. The folder contains some required sub folders, application and configuration files - which have to be configured accurately.

SysCommands.xml

This file is located in the CoPlanner Server directory. It contains information about system settings for the CoPlanner server instance. Please note in which language the SQL Server has been installed. If the German version is installed, then no further change is required. If the English version was used, then the file „SysCommands.xml“ has to be deleted or renamed (for example to „SysCommands_DeSql.xml“) and the file „SysCommands_EnSql.xml“ (within the same folder) has to be renamed to „SysCommands.xml“.

SvrConfig.xml

This file is located in the CoPlanner Server directory and defines several parameters of the CoPlanner Server instance.

<?xml version= "1.0" standalone="yes"?>
<SvrConfig.xml>
   <CopSettings>
      <Version>4</Version>
      <ConnectionString>Server=CopServer;Initial Catalog=COP_BIKE;Integrated Security=SSPI;pooling=false</ConnectionString>
      <DataBaseName>COP_BIKE</DataBaseName>
      <FiscalYear>0</FiscalYear>
      <ServiceName>copService</ServiceName>
      <TelnetDebugPort>6900</TelnetDebugPort>
      <DefaultLanguage>german</DefaultLanguage>
      <BackupPath>C:\temp\</BackupPath>
      <BackupRestoreLimitinMB>50</BackupRestoreLimitinMB>
      <WebAuthenticateUrl>http://copsupport.coplanner.com/licence/get_cop_lic_v3.php</WebAuthenticateUrl>
      <LicenceID>123</LicenceID>
      <SSPIPort>11000-11100</SSPIPort>
      <ClientConnectionTimeOut>1440</ClientConnectionTimeOut>
      <EntitySecurity>true</EntitySecurity>
      <AllowNonPowerUserDeleteDimElementsWithData>false</AllowNonPowerUserDeleteDimElementsWithData>
      <EventLogEntryLevel>ERROR</EventLogEntryLevel>
      <ServerLogPath></ServerLogPath>
      <MultiThreadCommand>true</MultiThreadCommand>
      <ProActiveCaching>true</ProActiveCaching>
      <SkipImportHistory>true</SkipImportHistory>
      <SecurityProxyLoad>false</SecurityProxyLoad>
      <CopSysIDStartOffset>0</CopSysIDStartOffset>
      <DynamicMode>false</DynamicMode>
      <MaxRows>300000</MaxRows>
      <MaxSvrLogFileSize>0</MaxSvrLogFileSize>
      <DebugMode>false</DebugMode>
      <StoredProcedureTimeout>3600</StoredProcedureTimeout>
      <ShowLostDBConnectionWaruning>true</ShowLostDBConnectionWaruning>
      <IISUserName></IISUserName>
      <TransportUserName></TransportUserName>
      <PowerUserModeViaADOnly>false</PowerUserModeViaADOnly>
      <ADGroupPowerUser></ADGroupPowerUser>
      <ADGroupPartnerUser></ADGroupPartnerUser>
      <ADGroupManufacturerUser></ADGroupManufacturerUser>
      <LoadAllEntityDimensionEntriesToClient>false</LoadAllEntityDimensionEntriesToClient>
      <ReportServerUrl>https://servername:4445/coplanner/reporting</ReportServerUrl>
      <DoVersionCheck>true</DoVersionCheck>
      <UseNewDBSync>true</UseNewDBSync>
      <DBSyncMaxThreads>10</DBSyncMaxThreads>
      <DBCommandTimeout>600</DBCommandTimeout>
      <UseNewCaching>false</UseNewCaching>
      <CacheSizeFactor>0.5</CacheSizeFactor>
      <StartTabAdress></StartTabAdress>
      <LogonMaximumPerTimeframe>100</LogonMaximumPerTimeframe>
      <LogonMaximumTimeframeInSeconds>60</LogonMaximumTimeframeInSeconds> 
   </CopSettings>
   <SSASSettings>
      <Server>CopServer</Server>
      <DataBaseName>cop_olap</DataBaseName>
      <XmlaLoadActive>true</XmlaLoadActive>
      <UseRealElementIDs>true</UseRealElementIDs>
      <UseMultipleHierarchies>true</UseMultipleHierarchies>
      <MultiSelectFilterActive>false</MultiSelectFilterActive>
      <LoadMeasureFormatAndType>true</LoadMeasureFormatAndType>
      <ReplaceNullValues>false</ReplaceNullValues>
      <DimensionLoadMode>AsDimAndHierarchy</DimensionLoadMode>
      <LoadDimensionsByAlias>true</LoadDimensionsByAlias>
   </SSASSettings>
   <SSRSSettings>
      <ReportServerUrl>http://CopServer/reportserver</ReportServerUrl>
      <ReportWebServiceUrl>http://CopServer/reportserver/reportservice2005.asmx</ReportWebServiceUrl>
      <RootFolder>/CoPlanner/Reports</RootFolder>
      <UserFolder>/CoPlanner/User</UserFolder>
      <TempFolder>/CoPlanner/Temp</TempFolder>
      <SharedDataSourceReference>/CoPlanner/DataSources/CoPlanner</SharedDataSourceReference>
      <Parameters></Parameters>
   </SSRSSettings>
   <DBCon>
      <Stage>Server=CopServer;Initial Catalog=COP_STAGE;Integrated Security=SSPI</Stage>
   </DBCon>
   <SupportConfig>
      <SupportButton>True</SupportButton>
      <SendAsMail>True</SendAsMail>
      <EmailToSendTo>support@coplanner.com</EmailToSendTo>
      <IncludeSvrCommands>True</IncludeSvrCommands>
      <IncludeNavigation>True</IncludeNavigation>
      <IncludeSystemInfo>True</IncludeSystemInfo>
      <IncludeCPU>True</IncludeCPU>
      <IncludeMemory>True</IncludeMemory>
      <IncludeGraphics>True</IncludeGraphics>
      <IncludeScreenShot>True</IncludeScreenShot>
      <SendAsPost>True</SendAsPost>
      <DestUrlPost>http://copsupport.coplanner.com/coplanner/support/supportExtended.php</DestUrlPost>
      <SendAsGet>True</SendAsGet>
      <DestUrlGet>http://copsupport.coplanner.com/coplanner/support/{Culture}/support.php?UserInput={UserInput}&Version={Version}&ServerCommands={ServerCommands}&Navigation={Navigation}&SysInfo={SysInfo}&CPU={CPU}&Memory={Memory}&Graphics={Graphics}</DestUrlGet>
   </SupportConfig>
   <WebConfig>
      <WebAppUrl>https://+:4443/coplanner</WebAppUrl>
      <LogonLanguages>Deutsch|English</LogonLanguages>
      <LogonWithCoPlannerSecurity>True</LogonWithCoPlannerSecurity>
      <LogonWithSSPI>True</LogonWithSSPI>
      <CorsAllowedOrigins>https://servername:4443</CorsAllowedOrigins>
      <EnableSwagger>False</EnableSwagger>
      <EnableCommandEndpoints>True</EnableCommandEndpoints>
      <EnableExperimentalEndpoints>True</EnableExperimentalEndpoints>
      <EnableMssEndpoints>False</EnableMssEndpoints>
      <EnableAuthWithSessionId>True</EnableAuthWithSessionId>
   </WebConfig>
</SvrConfig.xml>

CopSettings

The following parameters are defined in the CopSettings area:

Inscription

Description

ConnectionString

This parameter describes a typical "connection string" used to connect to a Microsoft SQL Server instance.

Server	Name of SQL Server database server (if the default instance is used) or name\instance, if a named SQL Server database instance is used. It is recommended to use the fully qualified domain name (FQDN) as name to avoid issues to connect with the database server from different domains/networks (e.g. if the server is named "myserver" and is installed in the domain "mycompany.internal" the name would be myserver.mycompany.internal). Additional information about SQL Server database instances: It is possible to configure more than one SQL Server database instance per server (an instance is a completely separate environment - one instance can contain several databases). The first or only instance per server is referenced to as "default instance". If only a default instance is configured or only the first instance should be adressed, then using the server name, only, would be sufficient. To identify exactly the used instance, use "name\instance" (e.g. myserver\instance01 or as FQDN myserver.mycompany.internal\instance01).
Initial Catalog	Name of the main CoPlanner database
Integrated Security	If a domain account is used to connect to the database server (recommended), then the CoPlanner Server service has to be configured to use the domain account to execute the service (to "run as"). In this case, specify "Integrated Security=SSPI" to use integrated Windows authentication (between CoPlanner Server service and the database server) - the parameters "User id" and "Password" are NOT specified. Just for information: "SSPI" stands for "Security Support Provider Interface" and is a Microsoft term to describe an authentication layer (here: the integrated Windows authentication).
User id	If no domain account/integrated Windows authentication is used, "User id" has to be specified and the name of the user to connect to the SQL Server database by the CoPlanner Server instance has to be specified. By default, the SQL server default account „sa“ should already exist and may be used. But as the "sa" account has major permissions on the SQL server instance it is recommended to create a separate SQL server account to connect to the database. Please contact CoPlanner about the required permissions.
Password	If no domain account/integrated Windows authentication is used, "Password" has to be specified and contains the password of the SQL server user used to connect to the database server.
Pooling	Set to false. This provides the possibility to use the Restore Database feature (integrated in the development menu).

DataBaseName

Name of the SQL server database used as the main CoPlanner database (usually identical with the content of "Initial Catalog" paramater from above)

PortNumber

Before CoP 10 HF 1.2: Default: 8963. Permitted and unused TCP/IP port number used for the communication between CoPlanner Server instance and the CoPlanner clients. For a list of commonly used (and to be avoided) port numbers see Wikipedia (external). Additionally, other services on the same server and firewalls may limit the usable port numbers.

The port number can be changed later, but the same port has to be configured both on the server and on the clients („ClientConfig.xml“).

FiscalYear

Determines posting of financial year of the company to calendar year.

It is calculated based on January.

0: none posting of financial year
-1: start of financial year in December of previous year
1: start of financial year in February of the same year

TcpFormatter

Before CoP 10 HF 1.2: Default: Native. Determines the mode of transmission between client and server. Possible values: „Bin“ for binary transmission, „Text“ for text transmission and "Native" for native transmission with the highest compression.

Hint "Native" is recommended. The same mode has to be configured both on the server and on the clients („ClientConfig.xml“).

ServiceName

Name of the CoPlanner server service (as it should appear after installation as Windows service).

TelnetDebugPort

Default: 6900. It is possible to establish a viewing window to CoPlanner Server with the aid of telnet. The parameter has to contain a permitted and unused TCP/IP port number used as telnet server port. For a list of commonly used (and to be avoided) port numbers see Wikipedia (external). Additionally, other services on the same server and firewalls may limit the usable port numbers.

To start the telnet session, open a DOS prompt in Windows (Start Run cmd), type "telnet hostname port" (without quotation marks, e.g. telnet myserver 6900) and press the enter/return key.

DefaultLanguage

The default language of the server is adjustable with this setting (ML routine CoPlanner). The default setting is German. If for example English is used, in the actual tables those entries are stored, which are seized by means of „English Client Logon“. Settings for example in German are stored in the ML system table. All available languages are available in the documentation of the ML routine.

BackupPath

Determines the (local) path to a directory on the server which provides temporary disk space for a database backup (which can be requested by a client). The SQL Server database server account has to have write permissions to the specified directory.

BackupRestoreLimitinMB

The maximum size of the database backup file in MB.

WebAuthenticateUrl

CoPlanner licence validation url.

LicenceID

License ID as provided by CoPlanner. The CoPlanner Server instance cannot be started without a Licence ID.

SSPIPort

Before CoP 10 HF 1.2: Port for SSPI-Login communication.

ClientConnectionTimeOut

after the expiration the client session on the server is deleted.

EntitySecurity

with this inscription the company security can be deactivated. If there is no entry the default is set to true.

AllowNonPowerUserDeleteDimElementsWithData

defines whether non PowerUser can delete elements with data.

SSPIServicePrincipalName

Before CoP 10 HF 1.2: Service Principal Name: Can be empty per default. Only in use in case of SSPI and double hop P.ex.: "cop/mycomputer.mydomain.com"

EventLogEntryLevel

it steers, which inscriptions are written in event log, it can be distinguished after severity code of errors

Warning: Warnings and errors (red and yellow Cop-Cubes)
Error: Only errors(red Cop-Cubes)
None: No entries

ServerLogPath

if this option isn't available or empty, the CoPlanner server folder will be used to store the file "Serverlog.txt". If a valid folder is entered, this folder will be used to write log file. If the folder isn't available, a message will be displayed and writing log file will be deactivated. The value "NoLog" deactivates writing log file too.

IsAliveClientTypes

Before CoP 10 HF 1.2: entered client types send periodically "AliveCommands" to CoPlanner server. If the client sends no "AliceCommands" to the CoPlanner server for an extended period of time, the session will be deactivated. Following client types are valid: Excel, Windows, Web and from version 9.5 HF 1.5 also Word and PowerPoint. The types must be separeted by a pipe. It is recommended to set the clienttypes.

MultiThreadCommand

with this option the parallel access of Clients can be activated.

ProActiveCaching

with this option the "Tupel Caching" of CoPlanner cubes can be activated. This means, that interim results will be cashed.

SkipImportHistory

is this inscription on true, error messages will not be written to the history table.

SecurityProxyLoad

is this inscription on true, the security will be processed at the logon for the user.

NTLogonTypes

Before CoP 10 HF 1.4: all listed NT logon types will be used in case of NT authentication. Following types are available: INTERACTIVE, NETWORK, BATCH

CopSysIDStartOffset

this offset will be added to the first created CopSysID. If there are several models in use, you avoid double CopSysIDs.

DynamicMode

activates dynamic loading.

MaxRows

maximum number of datasets send to the client.

MaxSvrLogFileSize

maximum size of serverlog-files. If the file will be greater it will be compressed. Zero means no compression. The size is defined in KB.

DebugMode

Ist dieser Eintrag auf true wird am Server detailierter gelogged.

SSPITimeOut

Before CoP 10 HF 1.2: defines the timeout for the SSPI LogOn communication between the Active Directory and the Coplanner Server.

SSPITimeToKeepReservedPorts

Before CoP 10 HF 1.2: defines the time in seconds, how long ports are reserved. The reservation starts by click on Logon.

StoredProcedureTimeout

optional defines the timeout in seconds after which the sql procedure will be cancelled. By default it is set to 3600 seconds.

ShowLostDBConnectionWarning

on true, the information that the server lost the connection to the database is shown at the client.

IISUserName

This entry is necessary for the login via SSPI via web client. The IISUserName is the identity of the application pool. For example IIS APPPOOL\.NET v2.0.

TransportUserName

necessary for the automated transport feature. Please note that that user has manufacturer rights also when logged on with a client. This user has to be a fully qualified domain user.

PowerUserModeViaADOnly

defines, if it is necessary to be member of the following AD groups to be able to log on as manufacturer, partner or PowerUser.

ADGroupPowerUser

defines the AD group for the access as PowerUser.

ADGroupPartnerUser

defines the AD group for the access as Partner.

ADGroupManufacturerUser

defines the AD group for the access as manufacturer.

LoadAllEntityDimensionEntriesToClient

defines whether the filtering of entity dimensions is made on the server or on the client. False is the filtering on the server.

ReportServerURL

defines the URL for the communication with the Reportserver. Only necessary when the web reporting is in use.

DoVersionCheck

defines, if a version check between clients and server takes place or not

UseNewDBSync

defines, whether new DB Sync is used or not. True means that the new DB Sync is active.

DBSyncMaxThreads

Defines the number of threads that can be used to write to the database when new DB Sync is active. If no value is entered, two threads are used. If the system utilization is to high, reduce the number of threads.

SSLCertificate

Before CoP 10 HF 1.2: Is empty if the communication between server and clients is not encrypted. If you want to encrypt the communication you have to set this entry to the fingerprint of your certificate. The ClientConfig.xml of all clients in this case also have to be updated. Set the SSLCommonName to the CN of the certificate.

DBCommandTimeout

Database command timeout for certain commands. This timeout is used e.g. for the data processing of imports.

UseNewCaching

defines, whether the new caching is used or not.

CacheSizeFaktor

Has only effects when UseNewCaching is true. The new caching prepares the data in a column-oriented cache for showing in masks. To avoid that the memory usage gets to high, you can define the CacheSizeFactor. The CacheSizeFactor limits the relation from the cache size to the table size. 0.5 means that the cache is allowed to have half of the data of the table in the cache.

StartTabAdress

If empty, the Start.html will be loaded as before. Now have the possibilty to open the tiles or another webpage on the start tab. An example: @Session_WebAppUrl@/tiles/?menu=MyStartpage&SessionID=@Session_ID@

LogonMaximumPerTimeframe

Maximum count of login attempts per seconds, which are defined for LogonMaximumTimeframeInSeconds. 0 means no limitation.

LogonMaximumTimeframeInSeconds

Timeframe in seconds for maximum login attempts, which are defined for LogonMaximumPerTimeframe. 0 means no limitation.

SSASSettings

The following points have to be defined in area SSASSettings:

Inscription	Description
Server	name of Server, on which OLAP-Database is located. If ClientBasedOlap is in use, you have to insert the name of server or IP address. Following names are not valid: localhost, ., 127.0.0.1 or local.
DataBaseName	name of OLAP-Database
XmlaLoadActive	is this inscription on true, structure data will be loaded by XMLA.
UseRealElementIDs	is this inscription on true, real OLAP keys will be used, under the premise that they are numeric.
UseMultipleHierarchies	is this inscription on true, alternative hierarchies of OLAP dimensions will be loaded and can be used (since SSAS 2005).
MultiSelectFilterActive	is this inscription on true, dimensions, used as filter, support multiselect.
LoadMeasureFormatAndType	with this option it can be declared, if connectors (c3, combined …) should be loaded directly at the Server start. This is not advisable at any OLAP console, due to the first user does not have to await the loading time.
ReplaceNullValues	is this inscription on true, all null values will be replaces with 0 by the connector.
DimensionLoadMode	defines the load mode of OLAP dimensions. Following entries are valid: AsDimension (default), AsDimHierarchy and AsDimAndHierarchy. Further information: Excursion SSAS Settings
LoadDimensionsByAlias	OLAP dimensions will be loaded by alias. Further information: Excursion SSAS Settings

Important If the SSAS settings LoadDimensionByAlias or DimensionLoadMode will be changed afterwards all cubes which refer to OLAP must be rebuild.

SSRSSettings

The following points have to be defined in the area SSRSSettings:

Inscription	Description
ReportServerUrl	(required) The report server’s virtual directory.
ReportWebServiceUrl	(required) SOAP endpoint for managing objects in the report server.
RootFolder	(required) Root folder for reports on the reportserver.
UserFolder	(optional) Folder for reports which will be created using the report assistant in the BI-Center. If this folder is missing or empty the root folder will be used.
TempFolder	(optional) Folder for reports which will be created using the report assistant in the Analyzer. If this folder is missing or empty the root folder will be used.
SharedDataSourceReference	path and name to an existing data source on report server, which is used by the report assistant to create a new report. Is this tag missing, the report assistant creates an embedded data source. The report builder can show no preview for reports with an embedded data source.
Parameters	Not in use.

The following points have to be defined in the area DBCon:

Inscription

Description

Stage

Server	name of server, on which database is located
Initial Catalog	name of database
User id	name of the user, which is used to login on SQL-Server by CoPlanner Server (as standard, ID „sa“ should already exist). Alternatively, an user can or has to be set up with relevant rights on SQL-Server.
Password	password of the user
Pooling	to bring Restore Database (fix integrated in the development menu) into action, in Connectionstring Pooling has to be false there!

Further variables:
Variables can be used e.g. at imports. Import objects require a Connectionstring to establish e.g. a connection to a previous system database. It is recommended to define such Connectionstrings as variables. On this way some objects can be changed at once and changes can be carried out more quickly.

<add key="Variable name" value="Value or inscription of the variable"></add>

SupportConfig

The following points have to be defined in the area SupportConfig:

Inscription	Description
SupportButton	defines the visibility of the support button.
SendAsMail	send the information via email. Hidden parameters will be delivered as not provided. Please select a wise choice of information due to the text limits of emails. If information should be delivered only via email, please deactivate the options SendAsPost and SendAsGet.
EmailToSendTo	the email adress for the delivery of the mail.
IncluceSvrCommands	defines, if the server commands is shown and delivered or not.
IncludeNavigation	defines, if the last steps of the navigation should be shown and delivered or not.
IncludeSystemInfo	defines, if the system information is shown and delivered or not.
IncludeCPU	defines, if the information about the CPU is shown and delivered or not.
IncludeMemory	defines, if the information of the memory is shown and delivered or not.
IncludeGraphics	defines, if the information of the grafic card is shown and delivered or not.
IncludeScreenshot	defines, if the screenshot is shown and delivered or not
SendAsPost	true = delivery through post.
DestUrlPost	The data will be send to that URL. Hidden parameters will be delivered as not provided. Namen of the parameters: UserInput, Version, ServerCommands, Navigation, SysInfo, CPU, Memory und Graphics. If information should be delivered only via post, please deactivate the options SendAsMail and SendAsGet.
SendAsGet	true = delivery through get.
DestUrlGet	URL with the parameters to send the information to. Hidden parameters from above will be transfered as not provided. Please select a wise choice of information due to the limitation of URL from browsers from about 2000 characters. If information should be delivered only via get, please deactivate the options SendAsMail and SendAsPost.

WebConfig

The following points have to be defined in the area WebConfig:

Eintrag	Description
WebAppUrl	Defines the base url on which the internal webserver is available. Via this webserver the REST-API and some features are accessible. If you do not want to use the REST functionalities you can leave this entry empty. In case of use the steps which are available at Voraussetzungen für die Verwendung der REST-Funktionalitäten have to be executed.
ReportServerUrl	URL which is used to communicate with the reportserver. Only necessary if web reporting is used.
LogonLanguages	Defines which languages are available at the web client login.
LogonWithCoPlannerSecurity	Defines, if CoPlanner Security is available in the web client. If false, the option is not available.
LogonWithSSPI	Defines, if Single Sign-on is available in the web client. If false, the option is not available.
CorsAllowedOrigins	If you want to access the API from another host, that host must be defined here. E.g. the call is like https://servername:5000/anycall, here must be https://servername:5000 defined. With \| as separator you can use numerous entries.
EnableSwagger	Defines, if the swagger is available. If it is not necessary on the system the option should be false.
EnableCommandEndpoints	Must be true, when using Windowsclient, Reportserver or any CoPlanner Addin. If only the web client is used the option can be set to false.
EnableExperimentalEndpoints	Must be true, when using the Help, CSV-Upload, Cockpits anything else from the Prototype-App. If you don't use any of them the option should be set to false.
EnableMssEndpoints	Must be true, when using MSS masks. If you don't use this masks the option should be set to false.
EnableAuthWithSessionId	Necessary for the handover of the session-ID within the Windows Client to MSS-masks and content of the prototype. If you don't use any of them the option should be set to false.

Excursion SSAS Settings

CoPlanner server offers several possibilities to load SSAS-Dimensions. Following table shows valid settings.

DimensionLoadMode	LoadDimensionByAlias	Setting	Result
AsDimension	False
AsDimension	True		Error creating thumbnail: File missing
AsDimHierarchy	True		Error creating thumbnail: File missing
AsDimAndHierarchy	True		Error creating thumbnail: File missing

Important Follwing settings are applied by default: DimensionLoadMode = AsDimHierarchy and LoadDimensionByAlias = True.

Important If the SSAS settings LoadDimensionByAlias or DimensionLoadMode will be changed afterwards all cubes which refer to OLAP must be rebuild.

These are the allowed OLAP settings explained in detail

Entry	Description
XmlaLoadActive	Default value = true, this value will be set by CoPlanner if setting has not be done in the SvrConfig This setting defines with with method the OLAP dimension elements are loaded: - False: The DimensionsMembers are loaded with Level.GetMembers() - True: The DimensionsMembers are loaded from SchemaDataSet. (The ADOMD method GetSchemaDataSet(...) is used.)

Excursion FormulasUsePriorityQueue

There is a possibility in formula work to define a execution order for formulas. To use this order the parameter "FormulasUsePriorityQueue" in the svrconfig.xml file has to be set to true. By using this option for formula execution formulas are not executed by sequence as before where the formulas have been triggered by changes in tables but now by priority.

By using a simple example the old and new behavior will be compared: If sales changes in profit and loss then the undistributed net profit will be changed and the balance has to be recalculated.

Without adaption of the priority the undistributed net profit-/undistributed net loss for february will be adapted and the balance will be recalculated when sales in january are updated. Due to the change of the undistributed net profit-/undistributed net loss in february also the undistributed net profit-/undistributed net loss in march has to be recalculated. By writing this change another calculation of the balance will be triggered.

From a sales update a undistributed net profit-/undistributed net loss is a result which has to be written monthly and with every writing the balance needs to be recalculated.

It would make sense to finish calculation of profit and loss( to write the undistributed net profit until the end of the year) and to transmit afterwards data to the balance.

This can be achieved by using the FormulasUsePriorityQueue: if the formula for profit and loss has e.g. a priority of 1 and the formula which updates the balance a priority of 2 an additional calculation of profit and loss and balance will be added to the calculation queue.

In the priority queue the formula with priority 1 will be preferred and therefore the calculation of profit and loss will be finished before the calculation of the balance. Additionally the system checks that there is already a request to recalculate the balance in the queue and no additional request will be added to the queue.

Definition of order

The priority of the execution of a formula will be determined by the table which triggers the calculation of the formula. Therefore formulas with different priorities can be executed (e. g. variable costs: if the recalculation of variable costs will be triggered through an update in the variable costs planning the mask will wait for the result, but will the calculation be triggered by an update in the sales planning the calculation should be done in the background. That can be done by assigning different priorities to the tables net sales and variable costs in the formula designer)

If no sequence is given or no order can be determined (e.g. Formula will be called by a macro) the sequence will be set to 1.

Avoidance Queue Starvation

If there is the danger that a calculation request has to wait a long time in the queue, the system can see this and new requests will be added to the queue behind the affected request - independently of the given priority.

Masks: Wait for calculations on the first level

In matrix masks can be defined to wait for calculations by setting the flag "WaitForCalc". In the actual implementation of the determination of the sequence it will be waited for all calculations which are triggered directly by writing in a table(this will be marked by calculation level 1). Here will be waited for all formulas of calculation level 1 independent of who has started the calculation.

With the PriorityQueue this behavior can be controlled more in detail, it will be waited for the execution of all formulas which have been triggered whose sequence < 0 (independent of user). To wait for a formula which calculates not on the source table this formula has to be triggered by the source table and needs a priority(< 0). This could be an use case: In a sales table the sales tax rate will be added to the actual record and the sales tax will be calculated and with another formula the sales for a profit and loss calculation written. The mask will now wait for an update formula because sales tax and sales tax rate are showed to the user but to write it to the profit and loss will be waited.

Exceptional position of SAME-Formula

If the calculation of SAME-Formulas will be triggered by a data input, these formulas will be calculated synchronly when saving the data record (and not added to the list). The calculation is not done by the list with calculations to execute.

Notes

It is recommended not to use the PriorityQueue without testing the behavior of existings model extensively. The change of duplicates had to be adapted by the process of this new implementation as well; this can lead to a change of calculation order in existing model in exceptional cases.

Configuration Tool

To edit CoPlanner Server settings you can use the configuration tool "SvrConfig.exe" which you will find at CoPlanner/Server folder.

CoPlanner-Server as service

The CoPlanner server can also be installed as a service. It is important that you have administrator rights to run the installation. You can start it using the button "Install Service", which can be found under CopSettings. The service starts with the name that is listed under Service Name. To uninstall the CoPlanner server as a service, select "Uninstall service".

Install some CoPlanner Services on one computer

On a computer several CoPlanner Services can be installed. Therefore you only have to change the service name among CopSettings and to reinstall the service again. Then under Windows services 2 independent services are available.

CoPlanner Server as a console

Start of the Server as a console

The installation has finished by copying of Server-directory onto the computer. You only have to start file CopServer.exe. A console window appears. Here you can see which operations the CoPlanner Server is carrying out.

Installation of CoPlanner Server with HTTPS

For a secure communication between clients and the CoPlanner Server you should use HTTPS.

This has two advantages:

Encryption (it is not possible to read)
Signature check (ensures the communication with the right server)

To use HTTPS you need a certificate. A certificate can be created by yourself or from various suppliers. Both options have advantages and disadvanteges. The certificate has to be a "X.509 certificate" (mostly a PFX-file) and must be valid for the domain, on which the CoPlanner Server should run.

Example: If the server should run under https://sample.copserver.mycompany:8080/coplanner the certificate must be valid for sample.copserver.mycompany.

Self signed certificates

Advantage: freely configurable, available at any time, undependent

Disadvantage: Not accepted by default of the most web browsers

If you want to use a self signed certificate, it has to be marked as secure on the PC of the user. This setting varies between browsers and operating systems. Generally the most browsers notice the settings for certificates of the operating systems (actually at least Chrome, Edge and Internet Exprorer). Firefox must be configurated seperatly (see diese Information of Mozilla).

It is possible,to distribute certificates via group policy Windows domains.

Certificate from a supplier

Advantage: accepted by default of the most web browsers

Disadvantage: some are with costs, different services per supplier

There are a lot suppliers for SSL certificates and the prices change day-to-day.

Examples for suppliers:

Let’s Encrypt: https://letsencrypt.org/de/

Free. Have to be renewed every three months.

Thawte: https://www.thawte.de/

With costs.

The process is different for each supplier. At the end you should have a certificate as file, which can be installed on the server.

Install of HTTPS on Windows Server

On Windows Server Http.sys, a component of Microsoft Windows, is used as hosting component for the CoPlanner server. This component handles HTTPS.

You must have the certificate as file to install it or it have to be already installed on the server. If the certificate is already installed, you need to know which certificate should be used und you need the thumbprint of the certificate otherwise you have to install it and you also need the thumbprint.

You also need to know for which address the certificate is valid. That must be the address the users use to access the CoPlanner Server. The WebAppUrl should also have that address instead of using a '+'.

Then you have to register the URL:

netsh http add urlacl url=<WebAppUrl> user=<DOMAIN\user>

On production systems there should not be used wildcards on the highest level. On subdomain level a use is okay.

Examples:

Unsecure: https://+:8080/coplanner

Okay: https://*.copserver.mycompany:8080/coplanner

Okay: https://sample.copserver.mycompany:8080/coplanner

As an example:

netsh http add urlacl url=https://sample.copserver.mycompany:8080/coplanner user=MY_DOMAIN\copServiceUser

You can also delete the registration:

netsh http delete urlacl url=<URL>

After registering the URL, the certificate have to be added with the following command:

netsh http add sslcert ipport=<IP>:<PORT> certhash=<THUMBPRINT> appid=<GUID>

<IP> IP address of the server,

<PORT> used port.

<THUMBPRINT> Thumbprint, of the used certificate

<GUID> different, whether CoPlanner Server runs as console or service:

Service: "{82880fad-ad78-4531-99ba-533f4c8e5840}"
Console: "{6ef7e594-5b5e-4626-904a-47b513e81e30}"

Example:

netsh http add sslcert ipport=0.0.0.0:4443 certhash=2170950415ef045f6cb314c92e124411dd0590bf appid={6ef7e594-5b5e-4626-904a-47b513e81e30}

If you want to remove the certificate for the IP address and port:

netsh http delete sslcert ipport=<IP>:<PORT>

If you use the CoPlanner Reportserver you have to do the same steps for it.

the GUIDs for the appid of the Reportserver for the netsh-command:

Service: "{d36681ea-e3d6-4909-94c3-93834beddb9d}"
Console: "{489b672b-5171-4aae-9e79-320427ab6c62}"

Installation of certificate for a file

This is possible with the Windows certificate administration.

Determination of the thumbprint of the certificate

The thumbprint of the certificate is shown in the properties:

Find the current used certificate on Windows Server

If you want to find out which certificate is used, you can do with the following steps:

This command will show you the information of the certificate:

netsh http show sslcert ipport=<IP>:<Port>

The thumbprint is the „certificatehash“. After that with the following commands you can get the detailed information of the certificate (for <THUMBPRINT> you need the value you got with the last command):

cd cert:

dir -recurse | where {$_.Thumbprint -eq "<THUMBPRINT>"} | Format-List -property *

If the certificate has a „FriendlyName“, you will see it in the Windows certificate administration.

Otherwise you have to search it by alternative properties.

Renew/Change certificate

To change a e.g. expired certificate you have to do the following:

Remove the binding of the expired certificate: