Setup of Reports Server

Introduction

Reports in r_keeper 7 manager station can only be viewed after the references server is licensed, and its connection to the external database under MS SQL Server 2012 control is configured. Therefore, a references server can function as a server for report generation or a reports server.

As the number of restaurants increases in the base up to dozens, the references server load increases as well. Therefore, r_keeper 7 allows creating intermediate (transit, subordinate) servers implemented via the reports server - rkReportServer.exe.

The reports server executable file is rkReportServer.exe, the default config file name is repserv.ini.

To install software from the distribution package (find the archive in ftp://ftp.ucs.ru/rk7/versions/7_05_03/STANDALONES/ folder), copy the REPSERV.INI file from \bin\win\ini\ folder to \bin\win\ working folder where rkReportServer.exe is located.

To install software via the installer, find a server with the config file in [installation path\instance name\Rk7Reports].

When is Reports Server Needed?

1. A report server is needed when it is necessary to divide reports and references info into separate tasks.
2. A report server is required to generate reports (with prior re-calculation of cubes) and to view them in the manager station (the license is needed, connection to the internal SQL database is set up);

In this case, there may be a Central Reports Server collecting data from all restaurants (or from local reports servers), and a Local Reports Server receiving data directly from a specific cash server (if the customer has several restaurants with cash servers).

1. A reports server is needed to reduce the load on existing servers when the number of restaurants is growing fast.
2. A reports server can be used as intermediate/transit server (without the license and setup of connection to SQL). Useful when there are hundreds of restaurants.

The values for some parameters, given in this article, are default and are not mandatory to specify.

The connection between the servers is shown below:

Licensing

Reports server licensing procedure is identical to references server licensing procedure described in the article on the References server.

Briefly, the procedure is as follows:

• Open Settings - OLAP reports - Report servers reference;
• Select the server;

Note

Executing the paragraph below is necessary if the server has already been licensed, i. e., existing license needs to be modified (prolongation, increasing number of stations, hardware change, etc.)

• Disconnect external DB if there is a connection (Connection to external DB settings -> empty, Cubes data source - switch to re-calculation with file: DB UDB (exchange via file); Save;

• Select protection key in License Info - Protection key - Physical number field;
• Generate license request (right-click on report server icon -> Actions -> Generate license request);
• Generate license for this request at l.ucs.ru site, and enter it to "License info -> License key" field;
• Save
• Configure connection to external DB if cube-based reports are required.

Launch

Launch report server as follows:

• Create server in manager station in "Settings - OLAP reports - Report servers", assign unique network ID, e. g., REPSRV3001, change status to Active, and save;
• License the server (see paragraph above); Otherwise, connection from manager station will not be possible;
• In "Synchronization" section, make sure all checks re set. Synchronization should be on.
• Create clean base in Management Studio. Configure connection to this DB using instruction in the article on connection to MS SQL.

Specify the following parameters in repserv.ini, in [Config] section:

Client = "REPSRV3001" ; report server network ID

RefServer = "RK7SRV3001" ; upper level server network ID (it can be both reference server and report server)

RefsBasePath = ".\BaseData\refsdata.udb" ; relative or absolute (recommended) path to reference DB

CheckBasePath = ".\BaseData\check_db.udb" ; relative or absolute (recommended) path to bill (cumulative) DB

WorkModsFile = ".\BaseData\wmodules.udb" ; relative or absolute (recommended) path to working modules DB

ReceivedPath = ".\Received\" ; relative or absolute (recommended) path to catalog with received closed shifts

ErrorLog = "repsserv.stk" ; relative or absolute (recommended) path to log file

UseSql = 0 ; do not connect to external DB under SQL Server 2012 control

• Launch the server.
• Stop the server, change UseSql=1 parameter value in repserv.ini config file
• Launch the server.

Important: the first launch should always be performed as an application (with the /desktop parameter). Also, make sure to perform the launch as administrator.

Note: default server config file is repserv.ini. If report server needs to be launched with other ini file, use the following command: "rkReportServer.exe /ininame: newname.ini /desktop".

As well as references server, report server can be launched as a service. For this purpose, use the /install parameter.

Upon server lunch as a service (rkReportsServer.exe/desktop) the following icon will appear in the task bar:

Repsserv.ini file description

Attention!

• Prior to report server launch, "read only" check is removed in its parent folder attributes (otherwise, report server will not be able to create its database).
• Also make sure the full path to folder with RKREPORTSSERVER.EXE file contains no spaces, and total number of path symbols does not exceed 200.

Copy repserv.ini file from RK7/BIN/WIN/ini folder if not done so yet, and place it besides RKREPORTSSERVER.EXE file, i. e., intoRK7/BIN/WIN/ folder.

[CONFIG]

Client = "REPSRV3001" ;; report server network name, same as corresponding report server in management station. See "Manager station settings" below)

RefServer = "RK7SRV_3001" ;; references server network name (or upper report server)

RefsBasePath = ".\BaseData\refsdata.udb" ;; full path to reference base, do not confuse with rk7.udb

CheckBasePath = ".\BaseData\check_db.udb" ;; full path to bills base, do not confuse with check.udb

WorkModsFile = ".\BaseData\wmodules.udb" ;; full path to working modules base

ReceivedPath = ".\Received\" ;; path to data collection folder

ErrorLog = "repsserv.stk" ;; log file name (add path if necessary). By default, log file is saved in the same folder where RKREPORTSSERVER.exe is placed

ErrorLogSize = 1048576 ;; error log size in bytes

OldRefsSyncMode = 0 ;; 1 - enable old synchronization mode necessary if PollEvents=1

PollEvents = 0 ;; enable periodic references server polling mode (in references server .ini file, fill EventCachePath= parameter), and set OldRefsSyncMode=1

RecalcAllCubesAt = 2015 ;; Time is specified without semicolon in "HHMM" format. Re-calculate all cubes that would be re-calculated by "re-calculate all cubes" item at manager station in 20:15. For versions 7.4.20.60 or higher.

CubesRecalc = 0 ;; disable cubes re-calculation, 1- enable cubes re-calculation, 2 - manual cubes re-calculation (For versions7.6.1.2 or higher!)

NoReports = 0 ;; disabling menu with manager station reports (For versions 7.6.1.2 or higher!)

ServPingTimeout = 5000 ;; 7.4.17.78 or higher

UseSql = 1 ;; 0 - if no connection to SQL, 1 - if connection to SQL is used

LoadThreadsCount = 4 ;; load closed shifts data to cumulative DB in parallel, "4" - number of files processed in parallel (recommended in case of big number of shift files arriving in parallel). Version shall not be lower than 7.5.2.328!

cubesBuildTool = "bldcubex.exe" ;; Enable bldcubex.exe utility for cubes generation at PC with 2+ GB memory (For versions7.6.2 or higher!)

[NETKERN]

Protocols = tcpsoc.dll ;; protocols list (comma separated), you must make sure these DLL files are in the folder with RKREPORTSSERVER.exe file

[TCPSOC]

PORT=3639 ;; port for server

[TCPDNS]

RK7SRV_3001=subway.rkeeper.com:3648

;;REP_CENTR4=subway.rkeeper.com:3644

;;RK7SRV=subway.rkeeper.com:3639

[UPDATE]

NoUpdate=0

; scheduler. only for 7.5.2.502+

[SYNCCONF]

; Enable scheduled synchronization

RefsSyncMode=1

; synchronization time in CRON format 50 10 * * * is equivalent to "always at 10:50", 45 13 * * * is equivalent to "always at13:45".

Format is as follows:

* * * * *

| | | | |

| | | | +----- Days of week (range: 1-7)

| | | +------- Months (range: 1-12)

| | +--------- Days of month (range: 1-31)

| +----------- Hours (range: 0-23)

+------------- Minutes (range: 0-59)

SyncSchedule=50 10 * * * ; synchronize references at report server loading

SyncOnReload=0

Report server update

Right-click the icon to open report server in browser. Default Http port can be assigned in manager station by specifying new port in report server properties (HTTP Server group -> HTTP Data Port group).

Upon report server launch, check its connection to upper server.

Report server update is identical to references server update.

Важно: Report server restart after update is mandatory.

Manager station connection to report server

Use the following methods to connect manager station to report server:

1. Create separate instance of manager station with new ini file and specify report server network ID in [REFEDIT] section therein.

• Server= REPSRV3001 ;; Your report server name
• Client=MANST-%RANDOM% ;; name of manager station that will be connected to report server

Therefore, there will be to icons for RK7MAN.exe on the desktop - one for connection to report server and references editing, another for connection to report server and reports viewing.

1. Launch manager station again, leaving ini file unchanged. When logging window appears, press "Ctrl+Alt+S" on the keyboard and enter report server ID in the poll that appears in bottom left corner.

Manager station will make necessary modifications in rk7man.ini, and you will have to select server by pressing "Ctrl+Alt+S" at the next launch.

Cubes re-calculation

Cubes are in Settings - OLAP reports - Cubes and reports reference

Status of cube and data thereof can be checked in cube properties, "Data processing" section. Each cube has list of report servers and cube re-calculation info (period, calculation time, last error, etc.).

You can re-calculate cube of interest manually (right-click - actions - re-calculate).

Starting from version 7.06.04.041: if server has over2 GB RAM, you can use new utility for cubes construction – bldcubex.exe. To do so, add the following settings to repserv.ini:

[CONFIG]

cubesBuildTool = "bldcubex.exe"

and restart report server. Then, you will have new rk7manex.exe manager station file. Use it for cube report generation.

It is desirable to create new user (role) with "cubes and reports only" rights and hide all other elements in manager station for this user (role). Then, create an icon for such manager station with a name like "Reports".

Starting from version 7.4.20.60 or higher, all cubes that would be re-calculated by "re-calculate all cubes" menu items at the manager station, can be re-calculated automatically at specified time by entering the following command in INI file:

RecalcAllCubesAt = "2015" (Time is specified without semicolon "HHMM") The value is easy to check via web interface.

Setup of report servers for restaurant chain

1. Example of report servers connection in chain restaurants

Big distribution networks (like SUBWAY, BURGERKING) use quite extensive chains consisting of multilevel connection trees or report servers with central report server (numbering in hundreds sometimes). Since the data (meals, prices, composition) is the same within one chain, it is enough to make modifications in manager station working with upper-level server (references server), and data synchronizes automatically with lower-level servers (intermediate), and then with local servers (in restaurant).

When manager station is connected to report server (other than central), some rights to edit references and object properties will be restricted!

Connection of report server to external DB is required if reports generation and viewing is needed when manager station is connected to this report server.

Connection diagram is shown in the figure below.

In this case, servers that will be used to view reports and process data, are subject to licensing. No. of stations is the number of different users that can generate reports at the same time.

If there are no active licenses in the base (a case with one references server without license and several licensed report servers), number of manager station connections to server is limited to 1. This situation may occur when all licenses expire in the license chain.

1. Configuration example for the network of 2 restaurants and "head office"

How it works:

Upon common shift closure at POS server 1 (midserver1) of restaurant #1, the shift data is processed at report server 1 and report server at "head office".

The same is valid for the second restaurant. The closed common shift is processed by "local" report server 2, and further in the head office.

2.1. Framework

• Head Office
• primary SQL base
• manager station
• references server (= primary report server)
• Restaurant 1
• report server 1
• local SQL base 1
• POS server 1 (midserver1)
• Restaurant 2
• POS server 2 (midserver2)
• local SQL base 2
• report server 2

2.2. SQL databases

• for Head office – ‘reports_HO’
• for restaurant 1 - ‘reports_rest1’
• for restaurant 2 - ‘reports_rest2’

2.3. Stations and devices

Create 2 restaurants and a POS server for each of them.

2.4. External DB settings

Create 3 settings as described in article, and configure connection to corresponding report servers (shall be created additionally).

This shall result in 3 configurations:

1. for Head office (‘reports_HO’ database);
2. for restaurant 1 (‘reports_rest1’ database);

for restaurant 2 (‘reports_rest2’ database).

2.5. Report server properties

Attention! Do not forget to acquire 3 licenses for 3 report servers.

2.6. Ini files

For report server 1:

[CONFIG]

Client = "repsrv_1"

RefServer = "RK7SRV_3001"

RefsBasePath = ".\BaseData1\refsdata.udb"

CheckBasePath = ".\BaseData1\check_db.udb"

ReceivedPath = ".\Received1\"

ErrorLog = "repsserv1.stk"

[NETKERN]

Protocols = tcpsoc.dll

For report server 2:

[CONFIG]

Client = "repsrv_2"

RefServer = "RK7SRV_3001"

RefsBasePath = ".\BaseData2\refsdata.udb"

CheckBasePath = ".\BaseData2\check_db.udb"

ReceivedPath = ".\Received2\"

ErrorLog = "repsserv2.stk"

[NETKERN]

Protocols = tcpsoc.dll

Manager station connected to references server (refserver):

[REFEDIT]

Server=RK7SRV_3001

Client=rk7man_ref

Manager station connected to report server 1:

[REFEDIT]

Server=repsrv_1 //network name of report server 1

Client=rk7man_rep1

Manager station connected to report server 2:

[REFEDIT]

Server=repsrv_2 //network name of report server 2

Client=rk7man_rep2

2.7. Employees and rights thereof

Add 2 employee groups:
* for report server 1
* for report server 2

Configure their right in properties:
* give all rights to view reports
* manager restrictions --> Report servers à mark corresponding report server

Set the following in employee properties:
* general --- current restaurant
* general --- account --- login and password

Using valid SSL certificates for report server in the domain

Default certificate (for rk7.local) is in BaseData\httpKeys\ folder, default.pem file.
To use your own certificate, rename it (own certificate) to mylocal.pem, and place it into the same catalog, then restart report server.

Certificate properties:

• Subj name – restaurant DNS name
• Alternative name not needed
• Certificate shall support server authentication.
• Key type – exchange or signature (default value is exchange). Exchange is data encryption certificate.
• Key size 2048 is supported.
• sha256 algorithm and sha256RSA hash algorithm are supported.
• Key export is possible. Certificate itself shall be in PEM format. Server key shall be located there as well. See example in default .pem file.
• No restrictions on crypto provider usage.

Please note

attempt to use wildcard certificate for FQDN machine with custom port and *.DomainName.local:8080 template failed. *.DomainName.local template worked.

Troubleshooting

1. "Cannot load cube data (cube data is empty or corrupted): Cannot open file "D:\UCS\RK7_5_1_34\bin\win\Cubes\00000022.blb". File not found". (see figure below)

Solution: Find cube corresponding to the report and re-calculate it!

1. In Windows 7 and higher OS, when report generation is attempted at manager station, "CubeDLL: Error reading Grid -> LICENSE_STR: Failed to set data for" error occurs. "CubeDLL:" error occurs in the course of viewing corresponding cube.
   

Solution: Assign administrator rights to Windows 7 user or run manager station as administrator.
Disable account control

1. "Item 0 was not found in external base config collection"error occurs during report generation.

Reason: this occurs if the role has no connection to SQL configured, and the user attempts to run existing or create new report that is generated via FR4 directly from SQL.

4 Connections limit exceeded

Reason - pre-installed report server is licensed in RK7, and you are trying to create one more report server

Solution - reset license data of extra report server, and create empty string "Setup of connection to external DB"

1. If the following error occurs while implementing internal or external base on your computer:
   
   check correctness of filling the following lines:

Reason: When "Connection to external DB" block is filled, "Reference logs" block is filled automatically

Solution: SQL username and password shall be the same in "Connection to external DB" and "Reference logs" blocks

 Installation of cash register station on Linux  Setup of service print