Compleat comes with a collection of template dashboards that give you a good idea of the kind of insights that dashboards can provide. Given these are out-of-the-box reports, they may not display the kind of data you're after. If this is the case, you can easily create custom reports to meet your own needs.

As a result of the database structure, a data migration occurs from the main database to your reporting database. This interval could prevent the latest data from being displayed in your dashboards. This interval can vary from customer to customer and will have been discussed during your implementation phase. If you're not aware what your time interval is, please contact client services at clientservices@compleatsoftware.com.

To create a custom dashboard, you need to:

• Create a query
• (Optional) Link additional data points
• Create a chart
• (Optional) Configure the chart
• Create a dashboard
• (Optional) Configure the dashboard layout
• (Optional) Set a prerequisite to filter dashboard data

Once you've created a custom dashboard, you can grant specific users access to it. For instance, you may want your heads of departments to have access to a dashboard containing all outstanding transactions specifically coded to their individual departments.

If you're comfortable creating dashboards, you can head straight to Give your users dashboard access.

Read on to see how to create your own custom dashboard.

1. Create a query

A query is an instruction to Compleat to collect a specific set of data, known as a data point. Your query will form the basis of your dashboard. You can create a query in the Query Designer.

To create a query, follow these steps.

1. In Admin Home, go to Reporting, and click Query Designer.
2. Click Add a new query.
3. The Data Point Editor opens.

   Your data point will form the source data for your query. Once you've chosen a data point, you can select specific fields from that data point to appear in your dashboard.

4. Select the data source you require from the drop-down.

   In this example we're selecting Invoice Header.

5. A list of predefined fields that make up the Invoice Header are now available to select.

   In the Output column, tick the check boxes beside the fields you want to include in your report.

6. When you're happy with your output choices, click OK.

7. The query now displays the data point containing your desired data.
8. Enter a name for your query.
9. Click Save. 

10. Nice job!

    You've successfully created and saved a query in Query Designer.

There are some additional configurations you need to do if you want to link data into one query. Continue to the section below to learn about linking data points.

(Optional) Link additional data points

Certain data points in Compleat can be linked together within a query to collect a more detailed insight. In our example, we want to capture invoice line data alongside header data in the query we just designed.

To do this, we simply need to create a second data point for invoice line data within our query, and then link it the invoice header data point that we've already created.

To create a new data point and link it to your existing data point, follow these steps.

1. With your query open, click New Data Point.

2. A popup appears asking you to select an existing data point to link the new one with.

   From the drop-down, click the data point that exists in your query, and click OK.

   We're selecting our existing Invoice Header data point.

   

3. It's time to decide how the two data points will link together.

   You have three join types to choose from: an inner join, a left outer join, or a union.

   We're choosing inner join, meaning that only records in our first data point will be returned if they match to a corresponding record in the new data point we're about to create.

   

4. From the drop-down, click the type of new data point to add, along with the field in which to join the data.

   We're selecting Join to Invoice Lines, which will match by the unique Invoice Ref field.

   Depending on the data points you're joining, varied matching options will display in the drop-down.

   
5. Click OK.

6. The Data Point Editor opens.

   Tick the checkboxes next to the fields you require under the Output column, and click OK.

   
7. The linked data points are now in your query.

   Click Save.

8. At this point, it's a good idea to click Test to make sure the query returns the data you need.

   You might then want to go back and make some alterations to your data points and how they join to make sure you're getting the correct data.

   If you're getting the correct data you require, click OK to save your changes.

9. You've successfully linked two data points!

   Below, you can read about some advanced settings for data points that might come in handy.

Data Point Advanced Settings

To view the advanced settings for a data point, click on a data point window to re-open the editor. Here's a quick explanation of the advanced settings columns available to you.

Alias
This column lets you rename the description of a field in the dashboard. If left blank, the field description in the Data Item column will be used.

Function
You can use functions to convert the raw data in the text field to return a range of values, such as:

• aggregate values (min, max, sum, count, average)
• text translations (eg. return a numerical value as a description)
• date transform functions (eg. days from now, days until now, current month, current year)

Order
Use this column to sort the dashboard by a specific field. To do this, select Asc or Desc against the field. If you'd like to sort the dashboard by multiple fields, you can choose a number to dictate the order that the sort will be applied.

Filtering
To filter the source data to appear in the report, click Edit Filters at the bottom of the data point editor. Any field that's part of the data point can be filtered, and it doesn't have to be a field output in the query.

Here's how to filter:

Select a condition against the field you want to filter.

In the next field, select the type of data to be filtered:

• Value – The most common filter. In the next field, enter a value (eg. a department code)
• Tag – Used in drilldown reports to filter by a value clicked in a linked summary report, and when using pre-requisites.
• Function – Used to filter on date or text functions (eg. where invoice date is since the company year start date)
• Field – Used to filter based on the value in another field (eg. where two date field values match)

The final column contains the actual data to be filtered by. 

And/or filtering
Filters can be applied to multiple fields in the field list. In the example below, the filters behave as an AND condition, meaning that both criteria have to be met in order for the data to be returned. For example, an invoice will only display if it was created in 2020 and was coded to the Training department. 

Alternatively, filters can be separated to form an OR condition. To do this, click New Filter at the top of the window.

The Date created filter can be added to filter 1, and the Department filter can be added to filter 2, meaning that either criteria have to be met for the data to be returned. For example, an invoice will display if it was created in 2020 or was coded to the Training department. 

You can copy an existing query to quickly create a new one without having to start from scratch. To do this, open an existing query, and click Make a copy. Then, give your new query a name.

2. Create a chart

Nearly there! Now that you've created a query, the next stage is to create a chart to present your query data.

To create a chart, follow these steps.

1. In Admin Home, go to Reporting, and click Chart Designer.
2. Click Add a new chart.

3. In the top-left of the header, enter a name for your chart, and tick the checkbox to make the chart active in your system.

   It's good practice to give your chart the same name as the query you're using.

   

4. From the drop-down in the top-right of the header, select the type of chart you want to display.

   We're selecting the Grid option.

   

5. Select your query from the drop-down.

   Your can type the name of your query to filter the drop-down.

   

6. Compleat may take a few seconds to pull the data from the query.

   After a few seconds, a Data Preview of your query appears to the left of the screen.

   To the right is the Chart Configuration window and the Chart Preview below, you might have to scroll down slightly to view this fully.

   
7. Click Save.

8. Well done!

   You've successfully created a chart from a query in Chart Designer.

There are some additional chart configuration options available. Here you can define your preferences further such as organising the column order and setting export options. Scroll down to learn more about the configuration settings, otherwise go to Create a dashboard.

You can copy an existing chart to quickly create a new one without having to start from scratch. To do this, open an existing chart, and click Make a copy. Then, give your new chart a name.

(Optional) Chart Configuration

Now that you have created a chart from your initial query, the Chart Configuration window is where you finalise the formatting and set the options available in the chart.

It's here here where you can set exporting options and make your chart downloadable to PDF or Excel files. You can also set the refresh interval to make sure the most recent data is being used and set the default page size for the chart data.

The options available will vary depending on the chart type you've selected.

The grid chart type is the most widely used chart choice. Here are the configuration options available to you for grid charts.

Grid Chart

When you've configured your chart, click Apply Properties To Chart Preview, and then click Save.

Nice one! You're over half way to creating a dashboard.

3. Create a dashboard

You're on the home straight!

Creating a dashboard from your chart is the final step in the process. You can bring multiple charts together to be displayed in one place to meet your data requirements.

The Dashboards area is where your final report will sit, and it's the dashboard, not your charts, that you'll access to get your report data.

To create your dashboard, follow these steps:

1. In Admin Home, go to Reporting, and click Dashboard Designer.
2. In the top right, click Add a new dashboard.

3. In the dashboard header, give your dashboard a relevant name.

   
4. The dashboard header also contains a number of checkboxes.

   Tick the checkbox to make your dashboard active.

   Also think about how the dashboard will be used:

   1. As a standalone dashboard, from the menu bar, or
   2. As an embedded dashboard within a transaction page
5. Click Layout at the bottom of the page to add your chart.
6. Select your chart from the drop-down.
7. Click OK.

8. Your chart will be displayed on screen.

   It is a good idea to click Save at this point.

Well done!

You've just created a dashboard and added a chart. The dashboard feature allows you to display multiple charts in one dashboard. Continue below for more information on Dashboard Layouts and how to bring multiple charts together.

(Optional) Dashboard layout configuration

The Dashboard Layout window is where you configure the charts in your dashboard. Here you can set charts side-by-side, or add rows to layer charts vertically.

Let's add another chart to our dashboard to display side-by-side.

1. In the Dashboard Designer click Layout.

2. The Dashboard Layout window appears.

   A chart is displayed in Row 1, Cell 1.

   It's possible to add cells to a row by clicking the [+] icon to the far right of the row.

   

3. Click the [+] icon.

   A new cell will appear with a drop-down to select another chart.

4. From the drop-down, select a new chart to be displayed in Cell 2.
5. Click OK.
6. Your two charts are now displayed side-by-side in the dashboard.

7. Now lets add another chart below the two we already have.

   Click Layout to open the Dashboard Layout window again.

8. Add a row by defining the number in the field box.

   Either type a number into the field or click the 'up/down' arrows to select the number of rows you want.

   

9. A new row appears with a drop-down list in a new cell.

   Select a chart to display.

   
10. Click OK.
11. Your new chart displays in a row beneath your existing charts.
12. Make sure to Save your changes.

Great work!

You've created a dashboard with multiple charts.

If you want to add further charts to your dashboard, repeat this process of adding additional cells horizontally or extra rows to display vertically depending on how you want your charts to be displayed.

Be mindful of keeping the number of charts in a dashboard to a minimum. Too many charts in one dashboard could affect system performance.

You can copy an existing dashboard to quickly create a new one without having to start from scratch. To do this, open an existing dashboard, and click Make a copy. Then, give your new dashboard a name. 

Another optional step in your dashboard is to prompt an end user to define a Prerequisite. Scroll down for more information.

(Optional) Set a prerequisite to your dashboard

Prerequisites can be used to prompt a user to define criteria before the report is run. For example, a user could be prompted to select a posting period or a department before the dashboard data is returned.

This requires just a little configuration in the Dashboard Designer and Query Designer.

To configure a pre-requisite for a dashboard, follow these steps.

1. On the Reporting tab in Dashboard Designer click Prerequisites.

2. A list of available fields appears.

   This list will vary based on the data that is populating the query.

   Select the field(s) that you want to defined as prerequisites. In this example we're selecting Department.
3. Click OK.
4. Now go to the Reporting tab and click Query Designer.
5. Search for your data point that is being used in the dashboard and click it to open the Data Point Editor.
6. Click Edit Filters.
7. Set the prerequisite field as filtered fields in Tag mode. 
8. Click OK and save your query.
9. Well done!

   Now, when a user opens the dashboard, they'll be prompted to choose the prerequisite before the data in the dashboard is displayed.

Great work!

You started with creating a simple query, and progressed all the way to displaying it in a dashboard.

If you want to make this dashboard accessible to your users, check out our guide on Granting users dashboard access.

You might also like...

• Customise your approval groups
• How Security works
• Sending invoices to Compleat