Automated Report Imports

After you have the automated report set up, you can import the automated reports to automatically and seamlessly manage your portfolio data. To set up the ARI we use report builder, automated reports, and automated imports to automate business rules without the assistance of our technical team. Some clients have used ARIs to automate the business rules, for example:

Team - delete empty teams
Team - match team workpoint allocation to blocking
Workpoint - match workpoint blocking to occupant's cost center

Your Serraview Portfolio can be updated in this process:

A custom report will extracts the data from the portfolio.
An automated report is used as an import to update your portfolio.

Automated a Report to Import into Serraview

Pre-requisite Knowledge

Basic JSON

If you need help with setting up the ARI, then contact the your Customer Success Manager or the Serraview Support team and specify the following in the request:

Purpose of the ARI so we understand what are you trying to achieve with this data.
Import that is used to update the data.
Report ID which extracts and possibly manipulates your data.
How often this report will be generated and imported.

Step 1. Define the ARI

The ARI can be used to complete different tasks, it is important to clearly define what you want the Automated Import Report to do and understand the outcome.

Below are examples for Team Imports and Workpoint Imports and the listed columns are the minimum required.

Team Imports

Import

Query

Columns

Match Team Allocation to Blocking

Teams.Where(OrgUnit.CostCentre != '' and StackedWorkpoints != BlockedWorkpoints)
.Output(
  Building = Building.Name,
  Floor = Floor.Name,
  CostCentre = OrgUnit.CostCentre,
  Workpoints = BlockedWorkpoints
)
.OrderBy(Building).ThenBy(Floor)

Building
Floor (format as text)
CostCentre (format as text)
Workpoints (format as text)

Delete Empty Teams

Teams.Where(TotalWorkpoints = 0 and TotalPeople = 0)
.Output(
  Building = Building.Name,
  Floor = Floor.Name,
  CostCentre = OrgUnit.CostCentre,
  _delete = 'True'
)

Building
Floor (format as text)
CostCentre (format as text)
_delete (note that you'll need to rename the column after adding it as it will remove the "_")

Workpoint Imports

Import

Query

Columns

Match Workpoint Blocking to Occupant CostCentre
(single occupant)

HomeLocations.Where(
  Space.Workpoints > 0 
  and (Space.OrgUnit.CostCentre is null or Space.OrgUnit.CostCentre != Person.OrgUnit.CostCentre) 
  and Person.OrgUnit.CostCentre != 'UNK')
.Output(
  Building = Space.Building.Name,
  Floor = Space.Floor.Name,
  Workpoint = Space.Name,
  CostCentre = Person.OrgUnit.CostCentre
)

Building
Floor (format as text)
Workpoint (format as text)
CostCentre (format as text)

Match Workpoint Blocking to Occupant CostCentre
(multiple occupants)

Spaces.Where(
  Workpoints > 0 
  and Occupants.Where(Person.OrgUnit.CostCentre != 'UNK').Count() > 0 
  and (OrgUnitID is null or Occupants.Count(Person.OrgUnitID = SpaceOrTeam.OrgUnitID) = 0))
.Output(
  Building = Building.Name,
  Floor = Floor.Name,
  Workpoint = Name,
  CostCentre = Occupants.OrderBy(PersonLocationID).First().Person.OrgUnit.CostCentre
)

Building
Floor (format as text)
Workpoint (format as text)
CostCentre (format as text)

Unblock Unoccupied Workpoints

Spaces.Where(
  Workpoints > 0 
  and Occupants.Count() = 0 
  and OrgUnit.CostCentre != ''
  and ContainerRetentionStatuses.count() = 0)
.Output(
 Building = Building.Name,
 Floor = Floor.Name,
 Workpoint = Name,
 CostCentre = ''
)

Building
Floor (format as text)
Workpoint (format as text)
CostCentre (format as text)

Step 2. Create the Custom Report

From Report Builder create the query and specify the report columns.

Step 3. Test the Custom Report

From Report Builder test the report.

Step 4. Update the ImportUser role

When you create a report a secure action is created for the report, for example, Report [report_number] Name (View). You will need to add the report's secured action to the ImportUser role.

Step 5. Create the Report Schema

The ARI uses the same Report Schema as the Automated Report but in the schema the import and (optional) the importComment parameters are used. Refer to the Automate Report for details on how to complete the configuration and the JSON code.

Extra Notes about renameFile in ARI

The file must be renamed to include one of the following import types in its name:

Asset
Building
Floor
Hood
Occupancy
OrgUnit
OrgUnitv2
Person
Region
Team
Workpoint

Original Case

You can force the import to keep the original case, for example in Person Imports where people have multiple upper case characters in their name.

Add the -keepOriginalCase to the end of the file name in the "renameFile".

"renameFile": "Person Import-keepOriginalCase.csv"

Step 6. Test the Report Schema in the UAT Instance

Complete the above steps on the UAT instance and set the automated report to run on the next quarter past the hour.

After the automated report has run then you can check the Recent Imports, refer to View Recent Imports.

Step 7. Set up for use in the PROD Instance

When you are confident that the results in the UAT Instance are correct. Then you can set up the report and JSON schema in the PROD Instance, and note that the ReportID may differ from UAT to PROD.