Skip to content
English
❔
Log into Projectworks
  • There are no suggestions because the search field is empty.

Building Custom Reports using Metabase

This article discusses how to create reports using Metabase, a tool that makes it easy to visualize, summarize, and understand crucial business data.

Getting started

Building Reports

Example report - Invoiced by User

Example report - Revenue by Type of Service using Custom Field

Example Report - Weighted Forecast

Example Report - Sum of Invoiced Amounts per Budget/Timecode

Example Report - Expense Overview

Further resources

Getting started

To build reports, you MUST have the ‘can edit’ permission added to your access level.

The ‘can edit’ permission gives access to all reportable data, so we recommend that you limit this permission to only those employees that should see sensitive information such as margin and staff cost.

  1. Click on the ‘Add Custom Report’ button on the Reports (Beta) screen and you will be taken to the New Report page.

dbfafa2e-009a-439f-b90b-cd58a7cd0c51

You can choose to start with your Company data OR a Saved Question.

 

Building a Report from Company data

Select the entity that you want to report on;


f04b5a05-82e9-4629-bfb0-f282a9e06fc3

  1. You can join the data to another entity, so that the information in the Report is more user friendly - e.g. Join the Time Entry data to the Person data will enable you to see the Person’s name against a Time Entry;

  2. You can add Custom Columns - e.g. You can concatenate a Persons FirstName and LastName in to a single “Name” column;

  3. You can add filters to narrow the information that is exposed in the Report - e.g. You can add a filter to display the Time Entries that have been reviewed, to restrict the Time Entries that are available to the User viewing the Report;

  4. You can summarize the data - e.g. You can summarize the Time Entries by the InvoicedAmount;c15cb698-08ec-4dc3-8f3d-1300179d8940

  5. You can select how to visualize the Report - e.g. a table vs a bar graph;b76d7368-38dc-47b0-ba57-559e2ee16f6c

Save the Report.

 

Example Report - Invoice by User

 

Given this report contains the sum of ALL time entries for it to be more useful, appropriate filters should be added on the Dashboard the report is part of.

Example report 2 - Revenue by Type of Service (Or any project Custom Field)

To build this report you MUST have custom field/s set on your projects with values

 

  1. Select '[Tenant Name] Sandbox' → BI → CustomFieldProject as your starting data.

  2. Next, join Invoice view from Reporting to the custom field data make sure to set the join on projectID on both data tables.

  3. Add a filter by CustomFieldProject - Label to make sure you are looking at the custom filed of your choice for this report.

  4. Then summarize by Sum of Invoiced Amount by Text. Text is what the actual Custom Field values are called in the system. This configuration combines custom fields with invoice data to generate a view on how much different types of projects have invoiced.Sales - CustomField1

  5. Save the new chart. Add a name that makes sense to you and select your personal collection.

Example report 3 - Weighted forecast

This is an advanced report using advanced functionality

 

A report using custom columns to create a point in time measure of the value of signed projects, committed projects and opportunities.

  1. Select ‘[Tenant Name] Sandbox' and pick ‘Budget’ as your starting data. This means the report is based on budgets recorded in the system.

  2. Next, join project data to the Budget so that we can easily see which project the budget belongs to - make sure to set the join on projectID on both data tables. This tells the system how the data is related across the tables

  3. Next create a custom column. Custom columns allow users to do math with the data available in the system without adding new fields on to Projectworks. Enter the following formula in the modal that opens and name the column ‘Budget Weighting’. This formula states that:

    1. if a Project status is ‘Signed’ we expect to get 90% of the total budget,

    2. if the project status is Committed, we expect to get 75% of the total value, and

    3. if the project status is Opportunity, we expect to get 50% of the total value'

      case([Project - ProjectID → ProjectStatus] = "Signed", 0.9, [Project - ProjectID → ProjectStatus] = "Unsigned (committed)", 0.75, [Project - ProjectID → ProjectStatus] = "Unsigned (opportunity)", 0.5)
       7eb85f0e-8f93-441b-9322-dcef12e23fc3

  4. Create a second custom column that uses the column we just created and multiplies it with the amount. Name it ‘Weighted Budget Amount’ . This calculates the budget value based on the weightings we’ve given. IF a project moves from one status to another, the weighting calculation will change: [Budget Weighting] * [Amount]

  5.  Add a filter to only look at projects that are Active. This means the report only looks at current and potential work.8756db4b-ccf4-4c18-bd54-ab845af3ae2f

  6. Summarize the Sum of ‘Weighted Budget Amount’ by Project Status590f2410-e2ed-42ef-8ed4-bba1e7d23425

  7. Select Visualize. You will see a new chart that shows the total sum of the weighted budgets as well as the weighted values of budget amounts per each project status.2531cdab-678c-4146-8de4-e1bc67b60f4c

  8. Save the new chart. Add a name that makes sense to you and select your personal collection e.g.

 

Example Report 4 - Sum of Invoiced Amounts per Budget/Timecode

 

 Example Report 5 - Expense Overview

Further resources

To learn more about how to join Reporting Views to build a report for your needs, read ‘Custom Reporting Data Model’ that gives a conceptual overview on how the Reporting Views are related to each other.

Metabase has more advanced features that allow you to craft even more complex reports. To read more about what is possible, visit https://www.metabase.com/learn/