Telework dashboard: create your own

Use the template and instructions to create your department’s telework dashboard.

Before you get started

Here is a list of the minimum information and applications necessary to create a dashboard for your department.

  1. Power BI Desktop version 2.8 or later
        • The telework dashboard template is a Power BI report file. You can download Microsoft Power BI Desktop for free. With the Power BI Desktop, you can visually explore your data through a free-form drag-and-drop canvas, a broad range of modern data visualizations, and an easy-to-use report authoring experience.
  2. Esri ArcGIS Online or Microsoft Bing Maps
        • Esri ArcGIS Online (AGOL) requires a subscription. See ArcGIS Online to learn more. This method will also require Esri Credits. An AGOL subscription allows for more automation than Bing Maps.
        • Microsoft Bing Maps API key is available from the California Department of Technology (CDT). Request an API key from CDT by emailing oetdataservices@state.ca.gov. For more information, see CDT GIS Services.
  3. Access to employee information
        • Information needed includes classification, home address, work address, and teleworking status. This information is likely found in your department’s Human Resources system.
Collect the data

Download the employee’s telework details Excel file template and review the minimum data needed.

Data can be collected using several methods. Departments may automatically calculate commute data from the work and home addresses registered in their Human Resources system, or manually collect employee data using an automated system such as Salesforce, ServiceNow, Microsoft Forms, or a survey tool. 

The dataset needed is the same as the dataset that must be submitted to DGS to participate in the Statewide Telework Dashboard. Review those instructions and data dictionary here.

Compute commute time and distance

Using Esri ArcGIS Online (subscription required)

  1. Export employees’ home and work addresses to a CSV file.
  2. Use these instructions provided in GitHub.

Using Microsoft Bing Maps (API key from CDT required)

  1. Export employees’ home and work addresses to a CSV file.
  2. Go to GitHub / Excel with Bing Map API.
  3. Click on “CommuteCalculation.xlsm”
  4. Click on the Download button.
  5. Open the downloaded Excel file and follow the instructions.
Assemble the data

Ensure all data are assembled into one excel file. Refer to the employee’s telework details Excel file template.

Create the dashboard with Power BI template

Download the Power BI telework dashboard template.pbix file and the employee’s telework details Excel file template from GitHub.

  1. Open the telework dashboard template with Power BI Desktop.
      • If using Power BI Pro, instructions may vary.
      • When prompted “Report layout differences might exist,” click Close.
      • When prompted “No R Installation Detected,” click OK. R is not required and does not need to be installed.
  2. Under “Transform Data” or “Edit Queries” (depending on your Power BI Desktop version) select “Edit Parameters.”
      • Add average one-way commute miles. Update as necessary.
      • Add average one-way commute minutes. Update as necessary.
      • Set the commute outlier limit. The Statewide Telework Dashboard uses 100 miles.
      • Edit the “Telework Data File” to point to where you saved your spreadsheet. For example, “C:TeleworkDataFolderEmployeeTeleworkDetails.xlsx”
      • Click OK.
  3. Click the “Apply changes” button at the top of the screen.
  4. Click on the Refresh button, located on the Home ribbon under External data.
      • If prompted to “Access Web content,” click Connect.
      • Verify that the report’s visuals have updated.
Publish the dashboard

There are two deployment options:

  1. Publish to Power BI SSRS using your SQL Server Enterprise with Power BI SSRS.
  2. Publish to Power BI Service using Power BI Pro subscription.

Note: if you’ve collected any personally identifiable information (PII), ensure you are in compliance with all applicable privacy policies.

Help us improve this website