EXACT Reporting Views is a framework to enable SQL reporting on an EXACT ISAM database(s).

Install files for EXACT Reporting Views are here: https://hsone.myget.org/feed/corporates/package/nuget/exact-urv

Pre-requisite - SQL Server & SQL Management Studio need to be installed first:

NOTE: SQL Lite/Express is NOT Supported.

How to install EXACT Reporting Views

You can install the EXACT Reporting Views from the command line easily using Chocolatey.

You can install the latest version available, or choose an older version. Once a version is selected, simply click on the Chocolatey option then the Copy to clipboard link:

Paste this command into an Elevated (Run as Administrator) CMD Window, and press enter
-then press A, then enter to accept all and start the EXACT Reporting Views install:

Manual Install of EXACT Reporting Views

Double Click on the ReportingViewsInstaller.exe

Follow the on-screen prompt(s):

Enter the name of the SQLServer the Reporting Views db should be added to:

Enter Install path - Leave as default:

SSRS Config:

Click Next, Install will begin:

EXACT Reporting Views (URV) Installation should then Complete:

You can open and check SSMS to verify that the SOEI_Reporting db was created successfully:

SoE-Admin Tool Installation

To install SOE-ADMIN tool separately, visit this page, You can install the latest version available, or choose an older version: https://hsone.myget.org/feed/corporates/package/nuget/soe-admin

Once a version is selected, simply click on the Chocolatey option then the Copy to clipboard link.

Paste this command into an Elevated (Run as Administrator) CMD Window, and press enter to start the SoE-Admin installation. NOTE: This only copies binaries, does not install anything under Start>Programs.

Upsizing an EXACT Database - Technical Info

There are a fair few technical steps required in order to transform an ISAM db into an SQL db.

SQL Server (2008 or later) needs to be installed, in order to hold the SQL server databases created from the ISAM datasets.

Express/Lite versions are not supported. SQL server must use Windows Authentication. Named Pipes and TCP/IP should be enabled.

The EXACT Reporting Views program needs to be installed.

The deployment package contains the folder ‘EXACT Reporting Views\ReportingViewsInstaller.exe.
This has an installer that is used to set up various programs and stored procedures related to Reporting Views, as well as the Reporting Configuration Database, which is used to manage the reporting/upsized databases in the configuration.

Once the URV tool has been installed, for each ISAM database TWO SQL databases will need to be created:

Upsized Database: Also known as the ‘staging’ database, which is used to hold the data converted to SQL from the EXACT ISAM data files;
Reporting Database: this is the database which follows the reporting database schema, which can be used to generate reports.

These databases need to be created using the executable ReportingViewsDeployDb.exe, which is found in the EXACT Reporting Views directory for the Reporting Views package.

IMPORTANT: Enable Pipe and TCP/IP options within SQL

Go To: Start \ SQL Server \ Configuration Tools \ SQL Server Configuration Manager

Make sure 'Protocols for MSSQLSERVERName' Pipe & TCP/IP are enabled.

NB: The SQL Windows Service will need to be restarted:

EXACT Reporting Views Database Creation

After installing Reporting Views, for each ISAM database you need to create TWO corresponding SQL databases.

These databases need to be created using the executable ReportingViewsDeployDb.exe which is found in the EXACT Reporting Views directory for the Reporting Views package.

Default Install Path: C:\Program Files (x86)\Software of Excellence\EXACT Reporting Views

Launch a command prompt with administrative privileges.
Using the Change Directory 'CD' command switch & Navigate to %REPORTING VIEWS INSTALL FOLDER%.

For each ISAM database, the following will need to be executed to setup the SQL databases:

ReportingViewsDeployDb.exe /ReportingConfigDBServer <SQLSvrConfig> /Action CREATE_RPT_DB /ReportingDbServer <SQLSvr> /ReportingDbName <ReportingDBName> /StagingDbName <UpsizedDBName>

<SQLSvrConfig> = SQL server that holds the Reporting Views configuration database. <SQLSvr> = SQL Server instance that is to hold these reporting/upsized databases

<ReportingDBName> = The reporting database name. <StagingDBName> = The database for the EXACT upsized database.

Example: For the ISAM db called CelebsNZ and a SQL Server named SHABBABOX the command would be:

ReportingViewsDeployDb.exe /ReportingConfigDBServer SHABBABOX /Action CREATE_RPT_DB /ReportingDbServer SHABBABOX /ReportingDbName CelebsNZ_Reporting /StagingDbName CelebsNZ_upsized

After running this command, the newly created reporting/upsized databases will be linked into this reporting configuration, and referenced in the Reporting Configuration database; SOEI_ReportingConfig (assuming you did not choose a different name when installing).

This can be seen by viewing the SOEI_ReportingDatabases table in the SQL configuration database.

When you have created the x2 SQL db's (Upsized and Reporting) you may need to tweak the db SQL settings:

In SQL Server Management Studio, right click on the newly created database and go into the properties, select file and change the initial size of the database to be the same size as the ISAM database being upsized and change the autogrowth to be grown by 10%.

Ensure you do the same for the log settings, for BOTH DATABASES.

ReportingViewsDeployDb.exe - available CMD Line Parameters

Once you have Installed EXACT Reporting Views, configured SQL Server & added Upsized/Reporting databases to transform the ISAM data into, you can then run an Upsize.

Performing an EXACT Reporting Views Upsize

SoeAdmin.exe, which is normally located under the %SOE_ADMIN_TOOL_DIR% is used to invoke the process of creating the Reporting Views database from the EXACT database.

This tool may be run in one of two modes:

Once an EXACT database(s) has/have been upsized, from ISAM to SQL, you will then be able to view the data within SQL Server Management Studio.

Command Line Mode

It is possible to run SoeAdmin for a single database by passing all required parameters on the command line.

See above for SoE-Admin Tool Installation instructions

Before the Upsize will work, in either mode, SQL Databases need to be created first - See Above.

An example of the SoeAdmin command and parameters:

SoeAdmin.exe AutoSQLUpsize <EXACT_Db_NAME> <EXACTSERVERNAME> <Upsized_Db_Name_in_SQL> <SQLSERVERNAME> <Reporting_Db_NAME> "<EXACT_Db_WINDOWS_LOCATION_PATH>"

Working Example:

SoeAdmin.exe AutoSQLUpsize CelebsNZ SHABBABOX CelebsNZ_upsized SHABBABOX CelebsNZ_Reporting "D:\Shabba\EXACT_Data\CelebsNZ\\"

NB: The EXACT_db_PATH needs to be enclosed in double quotes and end with a “\\”

Command Line Parameter List

SoeAdmin accepts the following command line parameter list:

AutoSQLUpsize	If a user wants to run SQL up-size automatically, the parameter has to be appended right after the string SoeAdmin.exe.
ISAM Database key	The name of the database being upsized
Server machine name	The name of the machine where the ISAM database is located
SQL database name	The SQL database name for the ‘upsized database’, as described above in section , Database Creation
SQL server name	The SQL server instance name
Reporting database	The reporting database name, as described above in section, Database Creation.
Dataset Path	The location of the ISAM dataset. The string should be wrapped in double quotes and end with “\\”. E.g. "C:\Source\Professional\Databases\Dataset1\data\\”
Checkdata [optional]	This is an optional parameter, where once added, the application will check the upsized data against the original data and log errors if there are any. Checks are recommended for the first upsize only since this check will extend the time required for the overall conversion process.
EnableUidCotMapping	This is an optional parameter which should be added to the command line after running the tool ‘Populate duplicate treatment plan mapping’ in EXACT (under Tools > Other Tools > ‘Populate duplicate treatment plan mapping’). This will then allow all charting and associated records to be included in the upsize process even if the uidcot was less than 1000

Configuration File (XML) Mode

In this mode, SoeAdmin is run as follows:

SoeAdmin AutoSQLUpsize <XMLConfigFile> [ <dataset name>] where <XMLConfigFile> is the full pathname of an appropriate XML configuration file, and <dataset name> is an optional parameter which refers to one of the datasets listed in the configuration file.

TIP: A sample XML configuration file is provided in %SOE_ADMIN_TOOL_DIR%, SoeAdminTemplateConfig.xml. This can be edited as appropriate.

The XML configuration file can contain as many ‘<database>’ elements as necessary, one for each EXACT ISAM database that is to be used.

For example, the following command could be used with a suitable XML configuration file:

SoeAdmin AutoSQLUpsize SoeAdminCfg.xml

This command will run the process on each of the databases listed in the configuration file, in the sequence they are given in the <databaselist> element.

A similar command:

SoeAdmin AutoSQLUpsize SoeAdminCfg.xml Dataset1

Would only run the process on the single dataset, ‘Dataset1’, even if there are several datasets given in the configuration file.

If present, the third parameter to SoeAdmin in configuration file mode must match the ‘<name>’ element of one of the databases listed in the file.

If the XML configuration file is not well-formed, an error will be shown in file %SOE_ADMIN_TOOL_DIR%\LogConfigParseSummary.html.

The ‘<parameters>’ section in the configuration file for each dataset is used to set the various parameter elements needed to run the upsize/reporting process on that dataset.

Note that the <SQLUpsizedDB> and <SQLReportingDB> elements should represent the actual SQL Server database names for the upsized and reporting databases, respectively.

Once an EXACT database(s) has/have been upsized, you will then be able to view the data within SQL Server Management Studio.

Log Files

After running the SoeAdmin command in either mode, a log file will be created, currently in the same directory as the SoeAdmin command, which indicates the results of running the process for a particular dataset. The log file names are of the form <datasetName>Log.html.

One log file will exist for each upsized/reporting database pair that has been processed by the command (e.g. if SoeAdmin was run using the configuration file mode, with no dataset specified, several upsized/reporting database pairs may be processed, depending on the contents of the XML config file).

Each log file contains information about the SQL upsize and reporting database population process.

Return Codes

In both modes, SoeAdmin returns a status code. A value of 0 indicates that the command ran successfully, and nonzero indicates an error.

In the case of the SoeAdmin command run in ‘Command Line Parameter Mode’, or ‘Configuration File Mode’ where a specific dataset has been specified, the nonzero error status code can be one of the following values:

Return Code	Information
1	Failed without extra information
2	Failed to connect to SQL database
4	Failed to locate the executable to launch the conversion
5	Failed to start the conversion process
6	Error while executing the spSOEI_RunMain stored procedure in the reporting database. Refer to the associated dataset logfile (<datasetName>Log.html) for more information.
7	Failed to start the reporting views stored procedure.
8	SQL Upsize process failed

If the SoeAdmin command is run in ‘Configuration File Mode’, but no specific dataset has been specified (i.e. all datasets given in the XML configuration file have been processed), the nonzero status code will indicate the number of datasets on which the process completed with an error.

Once an EXACT database(s) has/have been upsized, you will then be able to view the data within SQL Server Management Studio.

EXACT Reporting Views - Database Schema

The following describe the tables used for reporting in the EXACT reporting database.
Each table has a short description of its purpose and a field listing with descriptions.

SOEI_Adjustment

This table contains patient and payor transaction adjustments.

Column	DataType	Description	Foreign Key on Table	Primary key
adjustRecId	varchar(25)	Unique record identifier
adjustedTmsld	varchar(25)	The Invoice/Receipt number that was adjusted
patientId	varchar(25)	Record identifier from Patient file	SOEI_Patient.patientId
payorId	varchar(25)	Record identifier from Payor file	SOEI_Payor.payorId
locationId	varchar(11)	Record identifier from Location file	SOEI_Location.locationId
providerId	varchar(11)	Record identifier from Provider file	SOEI_Provider.providerId
adjustOtherId	int
adjustDate	datetime	The date the adjustment was made
adjustTypeId	varchar(50)	Type code for the adjustment
amount	numeric(18,4)	Amount of the adjustment
enteredBy	Varchar(11)	Logon id of the user creating this adjustment
adjustedItemId	varchar(25)	The id of the invoice or receipt that this adjusts
adjustedItemIdFormatted	varchar(25)	A formatted version of adjustedItemId
depositId	varchar(25)	Deposit that this adjustment belongs to, if any (NULL if not in a deposit).	SOEI_Deposit.depositId
gstAmount	numeric(18,4)	GST amount of the adjustment
referenceNumber	varchar(21)	Transaction number associated with third party payments

SOEI_Allocation

This table contains records of transaction allocations.

Column	DataType	Description	Foreign Key on Table	Primary key
allocId	varchar(25)	Unique record identifier		
allocDate	datetime	Date of allocation
allocSourceId	varchar(25)	Identifier of the source transaction	One of Invoice.invoiceId /receipt.receiptId /adjustment.adjustId
allocDestId	varchar(25)	Identifier of the recieving transaction	One of Invoice.invoiceId /receipt.receiptId /adjustment.adjustId
allocSource	varchar(25)	Verbose description of the source transaction type
allocDest	varchar(25)	Verbose description of the destination transaction type
coverType	int
amount	numeric(18,4)	Allocation amount
allocTypeId	int	Type of the allocation	SOEI_LOOKUP_AllocationType .allocation_typeid

SOEI_Appointment

This table contains patient appointment details.

Column	DataType	Description	Foreign Key on Table	Primary key
appointmentId	varchar(25)	Unique record identifier		
patientId	varchar(25)	Record identifier from Patient file	SOEI_Patient. patientId
locationId	varchar(10)	Record identifier from Location file	SOEI_Location.locationId
providerId	varchar(20)	Record identifier from Provider file	SOEI_Provider.providerId
roomId	varchar(40)	Record identifier from Room file	SOEI_Room.roomId
appointmentCreatedDateTime	datetime	Date and time that the appointment was created
appointmentDateTime	datetime	Date and time of the appointment
arrivalDateTime	datetime	Date/time patient arrived
seatedDateTime