Data Sources and Queries
To create a new report go to the 'Business Intelligence' section on the left menu and click the 'Create New Dashboard' or 'Create New Report' button. This will open the report designer in a new tab in your browser. It's called the report designer, but the same tool is used to design reports and dashboards. The main difference is that some components available to reports are not available to dashboards and vice versa. The report designer is split into several sections as shown below.
Data Sources
Within the dictionary section are the 'data sources'. This controls where UniFi looks to get the underlying data for your report. From the 'New Item' option at the top of the dictionary you can select 'New Data Source' to create a connection to a data source and then select what information from that source you want to use in your dashboard. Data can come from UniFi or from a wide variety of other sources including databases such as Microsoft SQL server, Oracle, PostgreSQL or ODBC connections. It can also come from Excel, CSV, JSON or XML sources either on the web or imported, as well as various other options.
A data source comprises of two parts, the connection and the query. The connection specifies the technical part of how UniFi connect to the underlying data, which for databases means supplying a connection string (this includes information such as the host, ports, user name and password). The query then specifies what data you actually need to read from this database or other source in order to create your dashboard.
Once a data source has been created, you can edit the connection by right-clicking on the data source and selecting edit. If you want to amend the query this is essentially a new connection so you need to select 'New Data Source' but then select your existing connection from the 'Report Connections'. From here you can amend your query as necessary
Queries
When a new dashboard is created a connection to UniFi is also created which you can see by expanding the data sources section of the dictionary. However this is not yet pulling back data from any of the underlying tables. To do this you need to create the query. Select 'New Data Source' and then pick 'UniFi' from the 'Report Connections' section. This will open up a list of all the tables available within UniFi that can be used in the business intelligence tools. This will include all of your app and reference data.
Use the query to only bring back the data you need for your report, the less data you extract the better the performance of your report or dashboard will be.
The table naming format is usually the lower case version of whatever you have called your app or reference data, with spaces and 'special characters' such as &, / etc. replaced with underscores. So a 'Purchase Invoice' app will be called 'purchase_invoice'. If your apps contain separate tables then these will be separate tables on the database with the suffix _table_1, _table_2 etc. So if 'Purchase Invoice' has a table for the individual lines of the invoice this will be called 'purchase_invoice_table_1.
If the app has been renamed after its initial creation, the database table format will not have changed. It will still be based on whatever the original app name was.
Select what tables you want to bring back by ticking them or you can expand the table name and select individual fields of the table if thats more appropriate. You can then click OK and the selected tables will then appear in the data sources section of the dictionary under the 'UniFi' group. Next, right-click on a table and select 'edit'. From here you can see the 'Query Text' box which will have the SQL query that will be executed when this data source is used. This will look something like:
select * from public."purchase_invoice"
Remove the 'public' part of the statement (after the 'from' clause) and change this to your UniFi account name, this is the first part of the URL you use to access UniFi, so if you use:
https://mycompany.unifiplatform.com
Then the above select statement should be changed to:
select * from mycompany."purchase_invoice"
You will need to do this for each table added as part of the data connector creation.
Click the red exclamation mark above the query text box to test that the new statement is correct. The click on the 'Retrieve Columns' button in the columns and parameters section of the screen to bring back the columns the query returns in to this section. You are now ready to use this connection in a report or dashboard.
Calculated fields
Also in the 'Columns and parameters' section of this screen there is also an option to add new calculated columns (second icon from the left just below the header of this section). This allows you to incorporate fields into functions as shown in the dictionary. The when referring to fields in the table you should use the format table_name.field_name, so a formula to get the left 5 characters from the supplier_name field in the purchase invoice table would be:
Left(purchase_invoice.supplier_name,5)
Resources
Resources are data sources that are embedded in the report. In most cases they will be uploaded files and so will be effectively static, although they can be updated from time-to-time by editing the resource and updating. Right-click in the dictionary area and select 'New Resource'. Give it a name and either browse to your selected file, or drag and drop into the relevant area of the dialog box. Supported file formats for data include CSV, Excel, XML, JSON and DBF (dBase database format). Once added as a resource you can add it as a new data source in the same way as other data sources. Select the relevant file type for your connection and all the resources of that type should be available for selection.
Resources can also be image files - this can be useful if the same image is shared amongst multiple pages in a report (e.g. logos, watermarks) as it reduces the size and load times of a report, and means it only needs to be changed once to filter through to all affected pages. Rich text files can also be embedded, although the designer does also have its own rich text field type and editor.