Reports Required Documentation: National Traffic Collision (Dummy Data)
- Yazjae
- Dec 3
- 4 min read
This project showcases my work in designing reports requirement documents and building interactive Tableau dashboards and stories.
The dataset was created using formulas in Microsoft Excel and is included as part of the project. Please note that the data is illustrative and not based on real-world trends.
Do you need a random data generator in Excel? Use this as a starting point, if you'd like! Leverage Google and YouTube to understand and alter my formulas.
Download the document to update it with your ecosystem's information if you'd like - I don't mind! Or, if you'd like, just take a scroll below the Tableau Dashboard to read all of this example's content:
Navigate directly to Tableau Public to view the sheets, dashboards and stories found in the supplementary file - You can even download the file and practice making visualizations on your own! Or, just enjoy the view here:
Reports Requirements Document
Report: Traffic Collision Dashboard
Document ID: YAZ-JAE-001
Version: 1.0
Date: November 19, 2025
Author: Strategic Business Analyst
Status: Draft for Portfolio
1. Business Context
1.1 Report Purpose
The Traffic Collision Dashboard provides real-time and historical visibility into motor vehicle accidents across the United States. It enables transportation agencies, city planners, and public safety officials to monitor crash trends, identify high-risk zip codes, and evaluate contributing factors such as weather, time of day, and road conditions. The dashboard supports data-driven decision-making for traffic safety improvements and resource allocation.
1.2 Business Problem Being Solved
Currently, traffic safety teams rely on static reports or fragmented data sources to analyze accident trends. This limits their ability to respond proactively to emerging risks or evaluate the impact of safety interventions. The dashboard will centralize and visualize collision data, enabling faster insights and more effective planning.
1.3 Intended Users
User Role | Use Case | Access Level |
DOT Analyst | Analyze crash trends by location and time | Full access |
City Planner | Identify high-risk intersections and zones | Region-specific |
Public Safety Officer | Monitor recent severe crashes | Local access |
Policy Maker | Review trends and evaluate policy impact | Summary view |
Public Viewer | Explore general crash statistics | Read-only |
1.4 Usage Frequency
Daily: Analysts and safety officers monitor new incidents and trends.
Weekly: City planners and policy makers review summaries and export reports.
Monthly/Quarterly: Used for safety audits, grant applications, and public transparency.
2. Data Elements Required
2.1 Core Data Elements
Table 1: Identification & Location
Data Element | Data Type | Format | Required? |
Accident ID | String | A00001 | Yes |
City | String | Text (100 chars) | Yes |
State | String | 2-letter code | Yes |
ZIP Code | String | 5-digit | Yes |
Table 2: Timing & Severity
Data Element | Data Type | Format | Required? |
Start Time | DateTime | YYYY-MM-DD HH:MM:SS | Yes |
End Time | DateTime | YYYY-MM-DD HH:MM:SS | No |
Severity | Enum | 1–4 | Yes |
Table 3: Environmental Conditions
Data Element | Data Type | Format | Required? |
Weather Condition | String | Text (100 chars) | No |
Road Condition | String | Text (50 chars) | No |
Visibility (mi) | Decimal | XX.X | No |
Temperature (°F) | Decimal | XX.X | No |
Wind Speed (mph) | Decimal | XX.X | No |
Traffic Signal | Boolean | TRUE/FALSE | No |
2.2 Calculated Fields
Calculated Field | Logic |
Duration (Minutes) | End Time – Start Time (× 1440) |
Day of Week | Extracted from Start Time |
Time of Day | IF hour < 6 = Night, <12 = Morning, <18 = Afternoon, else = Evening |
Weather Impact | IF Weather ≠ "Clear" THEN "Yes" ELSE "No" |
Risk Score | Severity × Duration |
3. Data Sources and Transaction Mapping
3.1 Source Transactions
Transaction | When Executed | Data Elements Captured |
Collision Entry | Simulated/randomized | All core fields |
Environmental Data | Simulated/randomized | Weather, road, visibility, temperature, wind |
Derived Metrics | Calculated in Excel | Duration, Risk Score, Time of Day, etc. |
3.2 Data Flow
Randomized Generator → Excel Table → Tableau → Dashboard
3.3 Integration Points
Source System | Data Provided | Integration Method | Frequency |
Excel | Collision dataset | Manual/ETL import | On demand |
Power BI | Visualization layer | Direct import | On open |
4. Dashboard Specifications
4.1 Dashboard Layout
Section 1: KPIs (Top Row)
Total Accidents
Avg. Duration
% Severe Accidents
Accidents with Weather Impact
Top 5 Cities by Crash Count
Accidents During Peak Hours
Section 2: Interactive Map
Heatmap of accident locations
Filterable by severity, weather, time
Section 3: Trend Charts
Line chart: Accidents per week
Bar chart: Accidents by severity
Pie chart: Weather conditions
Section 4: Data Table
Scrollable table with filters
Columns: ID, City, State, Start Time, Severity, Duration, Weather
4.2 Filters and Interactivity
Date Range
State
City
Severity
Weather Condition
Time of Day
Day of Week
4.3 Data Refresh Frequency
Manual refresh or simulated real-time
Historical data retained for 5 years
5. Calculations and Business Rules
Duration: End Time – Start Time (in minutes)
Time of Day: Based on hour of Start Time
Weather Impact: Flag if Weather ≠ "Clear"
Risk Score: Severity × Duration
Severity Classification:
1 = Minor
2 = Moderate
3 = Serious
4 = Severe
6. Acceptance Criteria
All required fields are populated
Calculations match sample queries
Filters and drill-downs work as expected
Dashboard loads in < 5 seconds
Export to Excel works
Responsive on desktop and tablet
7. Test Scenarios
Scenario | Objective | Steps | Expected Result |
Create Random Row | Validate data generation | Generate row with formulas | All fields populated |
Lookup ZIP | Validate city-state-ZIP match | Select city, check state/ZIP | Correct match |
Filter by Severity | Test dashboard filter | Apply Severity = 4 | Only severe crashes shown |
Map Hover | Test interactivity | Hover over heatmap | Tooltip shows city, severity |
Export | Test export | Click Export to Excel | File downloads correctly |
8. Data Quality Monitoring
Metric | Target | Frequency |
Required Field Completeness | 100% | Daily |
ZIP Accuracy | 100% | Daily |
Duration Accuracy | 100% | Daily |
Weather Data Availability | > 80% | Weekly |
9. Training and Documentation
User Guide: How to use dashboard, filters, exports
Data Dictionary: Definitions of each field
FAQ: Common issues and how to resolve
Training Session: 1-hour walkthrough for analysts
10. Assumptions and Dependencies
Dataset is clean and updated manually or via macro
Power BI or Tableau is available
Users have access to dashboard workspace
ETL process is in place to load data
11. Approval and Sign-Off
Stakeholder | Role | Responsibility |
BI Analyst | Author | Confirm completeness |
Data Owner | Reviewer | Validate accuracy |
Safety Director | Business Owner | Confirm operational value |
12. Version History
Version | Date | Author | Changes |
0.1 | 2025-11-18 | Strategic Business Analyst | Initial draft |
1.0 | 2025-11-19 | Strategic Business Analyst | Final version for review |
13. Appendices
Appendix A: Glossary
Severity: 1 (Minor) to 4 (Severe)
Risk Score: Severity × Duration
Weather Impact: Any condition other than “Clear”
Appendix B: Related Documents
Excel Workbook: Collision Generator
Tableau Dashboard File available upon request
Data Dictionary
Comments