Knack Operations
  • TABLE OF CONTENTS
  • Knack Operations Read-Only
  • Knack
    • Platform Overview
    • Functionality Guide
    • Style Guide
    • Banner Logos
    • Rounding Knack Costs Correctly
    • URL Parameters to populate a form
    • Importing Records
    • Copy an Application
  • Knack Security
    • Single Sign On (SSO)
      • Configuring a new Knack App with certificates
      • Enabling SSO in Knack
      • Configuring new certificates for all Knack Apps - When certs expire!
        • SSL Certificate Creation
  • Knack Integrations
    • Open Data Portal - Socrata Integration
    • ArcGIS Online Integration
    • CSR configuration prep in Knack
      • Creating CSR Page components
  • Knack Code
    • Code Version Control
    • GitHub Code Workflow
    • CODE: Login Buttons/App Setup
    • App Specific Custom Code
      • CODE: Vision Zero in Action
    • CODE: Knack Functionality
      • Big Buttons
      • Save Knack Record ID
      • Redirect Blank Menu Pages
      • Disable Trigger Buttons
      • Disable Modal Background Click/Touch
      • Disable Breadcrumb Navigation Links
      • Remove Back Links
      • Back Link Buttons
      • Return to Previous Page Button
      • Auto Input Validation Form Fields
      • Conditionally Hide/Show Form Fields
      • Return to Top Button
      • Refresh View Button
      • Detect IE
      • Set Map iFrame Source URL
    • CODE: Knack Look & Feel
      • Multipage Step Indicator
      • Modal Close Styling
      • Relabel Attachment Links
      • Navigation Dropdown Menu Buttons
      • In-Form Dropdown Menu Buttons
      • Trigger Buttons
      • Hyperlink a Form Field Label
      • Remove Table Header
      • Modify Table Summary Rows
      • Convert Values to UPPERCASE (Option A)
      • Convert Values to UPPERCASE (Option B)
      • Highlight Details View Label Body & Table Value
      • Report Headers
      • Report Filters Container Size
      • Report Element Colors
      • HighCharts Report Styling
      • Font Awesome Icons
    • CODE: Mobile Optimization
      • Detail Labels
    • CODE: Retrieve builder URL of Email Rules
    • CODE: Knack Print Page
      • Print Header with Page Break/Number
      • Print without overlapping views
      • Print using Menu Button
      • Print using Trigger Button
      • Show Character Limit of Input
      • Add fixed Footer on Print
  • AMD | DATA TRACKER
    • AMD | Data Tracker
    • Asset Changes
    • CSR | Flex Note Archiving
    • Cameras
    • Fix Broken CCTV Device Status Page Links
    • Performance Measures
    • Builder Notes
      • Configure New AMD Asset in Data Tracker
      • Data Tracker | Annual Signal and PHB Request Ranking
      • Page Rules (TMC) CSR
      • NOTIFICATION | Construction Status Email
      • NOTIFICATION | One Day Signal Engineer Due Date
      • DETAILS | Preventative Maintenance (PM)
      • DETAILS | Signal Detectors Object Tables, Reports, misc..
      • DETAILS | Signals
      • Signals Work Order | EDIT FORMS
      • Signals Work Order | FORM LOCATIONS
      • Editing Signals Work Order | NEW FORMS
    • Warehouse Inventory - Updating Journal Vouchers (JV) information
    • Cancel Transactions & Replenishments
    • Adjustment Transaction, Unit Cost Adjustments & Re-Stock
    • DTS | Adminstrative Action
  • DTS | Data & Technology Services Portal
    • DTS | Data & Technology Services Portal
    • Dataset inventory
    • Application Inventory
  • Finance & Purchasing
    • Finance & Purchasing Portal
    • Finance & Purchasing
      • Training, Quiz, Account Access Process
      • Purchase Request Statuses
      • List of emails generated
      • Re/Un-assigning Approver of a PR
      • Change PR Statuses (backend)
      • Invoice: FAQs
    • Warehouse Inventory
      • Adding a new inventory item
  • HR | Human Resources
    • HR | Human Resources Portal
    • Integrations TPW HR Portal
      • Integration HR Portal -> TPW Hire?
    • How to add a new HR Form
  • TPW Hire
    • TPW HiRe | Hiring Resource
  • PEP | Parking Enterprise Portal
    • PEP | Parking Enterprise Portal
    • Dispatch Resource Tracking Log Archiving
    • Update Parking Citation Report by Fiscal Year (FY)
  • ROW | Right of Way Portal
    • ROW | Right of Way Portal
    • COS JV Update
  • RPP | Residential Parking Permit Portal
    • RPP | Residential Parking Permits
    • Residential Parking Permits
    • RPP Migration
  • SBO | Street & Bridge Operations
    • SBO | Street & Bridge Operations
  • SMD | Signs & Markings Operations
    • SMD | Signs & Markings Operations
    • Table of Contents
    • Workflow
    • Markings | Work Orders
      • Process & Module Functionality
    • Markings | Service Requests
      • Process & Module Functionality
    • Signs | Work Orders
      • Process & Module Functionality
    • Signs | Service Requests
      • Process & Functionality
    • Contractor | Work Orders
      • Process & Module Functionality
    • Sign Fabrication Work Orders
      • Process and Functionality
    • Special Functions/Codes
      • Work Order Attachments
  • SMO | Smart Mobility Operations
    • SMO | Smart Mobility Portal
  • SMS | Shared Mobility Services
    • SMS | Shared Mobility Portal
    • License & Permit Management
    • Sources for Monthly Trip Records
  • STREET BANNER PROGRAM
    • Street Banner Program
    • Street Banner Program
  • TDS | Transportation Development Services
    • TDS | Transportation Development Services Portal
    • Color Palette
    • Role Permissions Guide
    • App Logic
      • Calculating Date Values for Reporting
      • Incrementing Review Cycle Automatically
      • Calculating Review Time & On Time Percentage
      • Calculating Cycle Completion Percentage
      • Calculating an Expiration Date
      • Automatic Due Date with Manual Adjustment
      • Roll up Child Object Conditionals
      • Converting an Entered Percentage to a Percentage Value
      • Create a Connection Link Field to View a Searched Record
      • Extracting Location Coordinates to add a Map Marker Map
    • TDR | Development Reviews
    • TIA | Traffic Impact Analysis
    • TDA | Transportation Development Assessments
  • Traffic Register
    • Traffic Register
    • Table of Contents
    • Workflow
    • Modules
    • Special Functions/Code
    • Permissions
  • TPW Forms
    • TPW Forms
  • Urban Forestry
  • VZA | Vision Zero in Action
    • VZA | Vision Zero in Action
    • Batch Copy Process for VZA Assignments
    • Calculate School Closures
    • Login Process for Officers
    • Officer Records
    • Update reports in Power BI
    • Use Tasks for Creating Child Records
    • Use Tasks for Notifications
  • O365 Resources
    • Resources
Powered by GitBook
On this page
  • Purpose
  • Permissions
  • Timeline
  • Process to update reports in Power BI
  • Preparatory Steps
  • Access reports in Brazos
  • Access Vision Zero officer assignments
  • Access Notes
  • Join VZA assignments and Brazos citations/warnings
  • Process to create reports in Power BI
  • For VZA Assignments 1
  • For Citation Audit by Step
  • Create the relationship between the two datasets

Was this helpful?

  1. VZA | Vision Zero in Action

Update reports in Power BI

This document will guide you through the steps of updating reports on officer assignments from the Vision Zero in Action (VZA) application and citations/warning from Brazos.

PreviousOfficer RecordsNextUse Tasks for Creating Child Records

Last updated 2 years ago

Was this helpful?

Purpose

To update reports in Power BI related to VZA assignments and citations/warnings recorded in Brazos by officers to support invoicing and data analysis.

Permissions

You will need access to the section in the VZA application and will need an account set up to access . Brazos is administered by the Austin Police Department.

Timeline

Right now, the process is run manually each week. The need to do this process depends on officers having signed up for assignments in the previous weeks. If no officers signed up for assignments in the prior week, there is no need to update the reports.

Another factor related to time to bear in mind is that officers don't always sync their devices at the cadence required for weekly updates to the reports. Sometimes the officers might have issued citations and warning during an assignment, but those do not get recorded in Brazos till they sync their device. So reports need to be renewed completely instead of adding snapshots over time.

Process to update reports in Power BI

Preparatory Steps

These steps can be carried out either on a PC or in a web browser.

  1. Open Vision Zero Datasets on One Drive. You will need to be given permission to access this location on One Drive.

  2. Add a suffix to Citation Audit by STEP, Marched_Assignments_Citations_Warnings, notes, vzaofficerassignments to denote the datasets are from previous weeks and can be archived.

3. Archive these datasets - Your One Drive - Vision Zero Datasets - Archive.

Access reports in Brazos

  1. Open the Audit by STEP report

3. This will open a screen that looks like the screenshot below. It might take some time.

4. Put in your date range, select All for STEP, and select all for Officers.

5. Click Finish.

6. Once the report loads, click on the icon in the top right corner. Select View in Excel options - View in CSV format.

7. This table will be downloaded as Citation Audit by STEP, open it in Excel and save it as a CSV. Copy the CSV to the Vision Zero Datasets folder in One Drive.

Access Vision Zero officer assignments

  1. Filter the Officer Assignments table for all assignments after January 19, 2021.

3. Export this table as a CSV. It will download as vzaofficerassignments.csv

4. Move this csv to the Vision Zero Datasets folder in One Drive.

Access Notes

  1. Filter the Time Logs - Notes table to show all notes after January 19, 2021.

  2. Export this table out as a CSV. The table will be downloaded as notes.csv

  3. Move this CSV to OneDrive - City of Austin\Vision Zero Datasets (You will need to be granted permission to access this location.

Join VZA assignments and Brazos citations/warnings

To update the Power BI report, click on Refresh in the top ribbon of the Power BI report in Power BI Desktop.

Process to create reports in Power BI

The process needed to get to this point is documented below in case any changes need to be made in the future.

For VZA Assignments 1

This datasource is used to connect to the citations/warning dataset from Brazos. The process needed to create a common key to connect to the citations/ warnings is documented below.

  1. Connect to the vzaofficerassignments csv in the Vision Zero folder in One Drive.

  2. Extract the length of the Officer Assignments Date Time field.

  3. Depending on if the assignment starts and ends on the same day, or over two days, the length of the string in that field will be 25 or 36 characters. (mm:dd:yyyy hh:mm:ss to hh:mm:ss or mm:dd:yyyy hh:mm:ss to mm:dd:yyyy hh:mm:ss). We will use this field to create a conditional column later in this process.

  4. Extract the text before the first space delimiter from the Officer Assignments Date Time field. Rename this field to Assignment Start Date 1

  5. Extract the text between the first space and second space delimiter from the Start of the input in the Officer Assignments Date Time field. Rename this field to Assignment Start Time 1.

  6. Extract the text before the first space delimiter from the end of the input in the Officer Assignments Date Time field. Rename this field to Assignment End Time.

  7. Extract the text after the ‘to’ delimiter. Rename this column Assignment End Date and Time 1.

  8. Create a custom column by concatenating Assignment Start Date and Assignment End Time to create Assignment End Date and Time 1.

  9. Create a conditional column Assignment End Date and Time with the condition if Length is 25, then point to Assignment End Date and Time 1, if not then point to Assignment End Date and Time 2.

  10. Create a custom column with Assignment Start Date 1 and Assignment Start Time 1. Rename it to Assignment Start Date and Time.

  11. Convert the Assignment Start Date and Time, and Assignment End Date and Time to Date and Time format.

  12. Create a custom field Assignment Duration 1 to calculate the hours in each assignment by subtracting Assignment Start Date and Time 1 from Assignment End Date and Time.

  13. Convert this field to Hours format.

  14. Create a custom field call Create List with this formula = List.DateTimes([Assignment Start Date and Time],[Hours],#duration(0,1,0,0))

  15. Explode the list by expanding to new rows.

  16. Copy the Create List field using the Duplicate Column function.

  17. Convert the field to a Date and Time field. While holding the field selected, click on Date in the model ribbon, select Date Only. Rename this field Assignment Date.Convert to a Text field.

  18. Copy the Create List field again using the Duplicate Column function.

  19. Convert the field to a Date and Time field. While holding the field selected, click on Time in the model ribbon and select Time Only. Rename this field Top of the Start Hour. Convert to a text field.

  20. Create a Custom Column by concatenating Badge Number Assignment Date and Top of the Start Hour. Rename this field Assignment Join ID.

  21. Run remove duplicates on the Assignment Join ID field.

For Citation Audit by Step

  1. Connect to the Citations Audit by STEP csv in One Drive.

  2. Change the field type for Unique ID to Text.

  3. Duplicate the Time field to create Time-Copy.

  4. Rename the Date field to Citation Date.

  5. Rename the Time field to Citation Time.

  6. Select the Time-Copy field and then navigate to Transform - Hour - Start of the Hour.

  7. Rename this field Top of the Start Hour.

  8. Duplicate the field Top of the Start Hour and convert it to a text field.

  9. Create a custom column with this formula - [Officer Badge No]&" "&[#"Date - Copy"]&" "&[#"Top of the Start Hour - Copy"].

  10. Rename the field Assignment Join ID.

Create the relationship between the two datasets

  1. Close and Apply in Power Query Editor to apply and save the queries and data transformations.

  2. Click on Modeling and then select Manage Relationships.

  3. Set a relationship between VZA Assignments 1 and Citation Audit by Step using the Assignment Join ID fields and set the cardinality as One to Many (one VZA Assignment record to many Citation Audit by STEP records).

Open and navigate to reports.

Open

Open

Brazos
https://atd.knack.com/vza#api-views-private/
https://atd.knack.com/vza#api-views-private/
Reports
reports in Brazos