This article describes how to use the Excel template report generator, provided by etz to agencies who are subscribed to etzInsights, in order to easily access data stored in your cloud server.
Firstly, download the 'GenericReportGeneratorTemplate' file from the etz online repository and open it up, navigating if necessary to the ‘Connection settings’ tab within the document. It should look as in the image below.
Enter the connection data for your cloud server in the first four blank cells – this information can be found on the etzInsights page for your server, as in the image below.
Username: this is made up of 3 parts:
1. the alphanumeric string that is given next to the ‘User Name’ heading on the server configuration page
2. followed by an ‘@’ symbol
3. then the alphanumeric text up until the first period next to the ‘Server Name’ heading.
In this example, the username would be: X81KCAZX81@dzvn01dbza. The same username should be used by all agency staff wishing to access the server.
Password: exactly as provided next to ‘Password’ on the server config page (e.g. Md2&Q&-91).
Database: again, this is exactly as listed next to ‘Database Name’ within the config page (e.g. A458_EtzKn). The database name is the name of the specific database that you wish to access on the cloud server. For most agencies, they will only have one database to choose from. It is, however, possible for a customer to have more than one database on the same etzInsights server – for example, a collection of agencies within the same agency group may choose to use a single server.
Server: the server name is constructed of 2 parts:
1. the text ‘tcp:’
2. followed by the text next to the ‘Server Name’ heading on the config page.
Here, this would be: tcp: dzvn01dbza.database.windows.net
After entering these pieces of information, your template file should look something like the image below.
This should be all the information that the template needs in order to make a connection to your server.
Note: in order to make sure that your computer has access to the server, you need to ensure that your IP address is on the list of accepted IP's. For more information on this, please see the KB article Configuring Data Warehouse Firewall Access
Next, you need to provide the server with a request for information. This request should be inserted in the empty cell next to ‘Data query’ and should be written in T-SQL language. This programming language is an industry standard for extracting information held in relational databases. If you do not have the expertise in-house to write T-SQL queries, there are many freelance consultants who offer this as a service.
Also in the etz online repository are a number of example reports, with the T-SQL commands already inserted in the ‘Data query’ cell in order to generate the relevant data. This example will use the ‘Assignment Summary’ report.
The last setting on the “Connection settings” tab is called “Key query” and is the T-SQL that uses your database to populate the key in the ‘Filters’ tab.
The ‘Filters’ tab is where various parameters can be easily set and changed to control the data that is returned from the server – for example; start date, end date, assignment type.
Any parameters that you wish to change on a regular basis should exist in the T-SQL code as a placeholder, and follow the format:
DECLARE <parameter_name> <datatype> = <default_value>;
To pull out these parameters from the code into the Excel file, click the ‘Populate Parameters’ button. This should insert the parameters in the top half of the screen, as in the image below. A key should also be populated in the lower half of the screen, showing how to filter on fields that require an ID. For example, to filter by consultant you will need to use their consultant ID – a list of all consultant names in your database, along with their ID’s, should be listed in the key for convenient reference.
Once you have finished updating the filters, click the ‘Update Data’ button. This will insert the filter values into the T-SQL code within the ‘Connection settings’ tab and send it off to the server. All data that is returned by the server is placed in the ‘Data’ tab, as in the image below.
Please note: any errors in the code of a custom query may cause no data to be returned. Since there are no error messages that can used for diagnosis in this instance, it is suggested that you troubleshoot the problem by submitting the query in specialised database software, such as SQL Server Management Studio, which gives much more detailed feedback about any problems with the code.