Data Project: Assessor Dashboard

portfolio
data
dashboard
Published

March 14, 2026

Data Project: Assessor Dashboard

The DataViz Assessor’s Dashboard was born of a frustration I had with traditional BI Tools. It was built as a custom on-prem dashboard driven by SQL live access to the database. Then it uses Python, JSON, JS, HTML, CSS, JavaScript and other tools to create a dashboard our non-technical staff can use. Below I describe the project, and the links here are to a sanitized demo version of the project. The demo derives its static data from csv exports for presentation purposes only, as nobody outside the organization can access our on-prem version.

Open Public Demo Maps Graphs Tables

Problem

Power BI was unable to parse our Geometry files (Parcel Shapes) natively, so we moved to Tableau. While Tableau eats Parcel Shapes within seconds and became our go-to BI tool, I found it frustrating that we had to create multiple workbooks to make the joins and star schemas work for all the reporting we needed. Further, as a limited budget local government agency, we did not have the funds for a full Enterprise release. As a workaround, I had a creator license for Tableau Desktop which was then published but hidden to Tableau Public. Then those links were shared as bookmarks for non-technical staff to use. This required singing into Tableau Cloud (which was always a multi-step process) and then signing into Tableau Public to publish. This was for each separate report I wanted to update, all while having no deeper control over various visual aspects. I decided I wanted one tool with a simple rebuild > republish interface when not creating new views.

Data

JavaScript 60.8% | TSQL 21.2% | HTML 8.3% | Python 6.7% | CSS 2.0% | PowerShell 1.0%

  • Parcel Shapes (Cadastral Parcels zip file) is located on the county website and re-accessed with each rebuild.
  • SQL > We have a large repository of SQL developed by the Data Dev team for all manner of reporting.
  • BI SQL > I created custom views of data in SQL for the DataViz tool to use for creating views.
  • Python > Is used for transforming and joining aspects of data and for computation analysis.
  • JSON > Is used to parse the parcel shape files into various map views.
  • HTML/CSS/JavaScript > Are then used to create an on-prem localized website accessed through a shared browser link by non-technical staff.
  • PowerShell > Is used to host instructions for the build tools to run the SQL queries and rebuild the site.
  • GitHub > A private GitHub repo holds this structured data.
  • Visual Studio Code > Is used to access the repo and PowerShell uses Run Task to rebuild the site at the data teams request.

Project vs Demo

The distinction matters here:

  • The project is the DataViz Assessor’s Dashboard work itself, including the SQL logic and the original dashboard workflow.
  • The demo is a public-facing interactive stand-in built from sanitized exports because visitors cannot access the original environment.
  • The demo is meant to show interaction patterns, dashboard structure, and data storytelling approach without exposing restricted source systems or confidential data.

Project SQL

At core, I am a SQL Developer and Data Nerd. SQL, Excel, Power Query/DAX are my strongest languages. However, I’ve picked up many other languages (above) as they served me well. These are the working SQL files behind the assessor-side project. The public demo does not connect to the original source system. It only re-presents sanitized exports produced from the results of these queries.

SQL Design Note: It’s worth noting that if I were to create these visuals in Power BI or Tableau, I would have created stand alone tables and keys for start schema configuration. These were built this way in the original project and never changed. Future iterations may adopt that form.

key_cat_group_codes.sql

This is the category lookup table. It pulls active impgroup code records so the dashboard can translate category codes into readable labels when showing assessed values by category.

Select
c.tbl_element As Cat_Group_Code,
c.tbl_element_desc As Cat_Description

From codes_table As c
  -- On a.group_code = c.tbl_element

Where c.code_status = 'A'
  And tbl_type_code = 'impgroup'

land_rates.sql

This query builds the land-rate detail extract. It starts from land header and land detail records, adds parcel geography and district labels, then joins lookup tables for land method, land type, and site rating so the dashboard can map and compare land-rate behavior by parcel.

Select
lh.RevObjId As lrsn,
Case
  When pmd.neighborhood >= 9000 Then 'Manufactured Homes'
  When pmd.neighborhood >= 6003 Then 'District 6'
  When pmd.neighborhood = 6002 Then 'Manufactured Homes'
  When pmd.neighborhood = 6001 Then 'District 6'
  When pmd.neighborhood = 6000 Then 'Manufactured Homes'
  When pmd.neighborhood >= 5003 Then 'District 5'
  When pmd.neighborhood = 5002 Then 'Manufactured Homes'
  When pmd.neighborhood = 5001 Then 'District 5'
  When pmd.neighborhood = 5000 Then 'Manufactured Homes'
  When pmd.neighborhood >= 4000 Then 'District 4'
  When pmd.neighborhood >= 3000 Then 'District 3'
  When pmd.neighborhood >= 2000 Then 'District 2'
  When pmd.neighborhood >= 1021 Then 'District 1'
  When pmd.neighborhood = 1020 Then 'Manufactured Homes'
  When pmd.neighborhood >= 1001 Then 'District 1'
  When pmd.neighborhood = 1000 Then 'Manufactured Homes'
  When pmd.neighborhood >= 451 Then 'Commercial'
  When pmd.neighborhood = 450 Then 'Personal Property'
  When pmd.neighborhood >= 1 Then 'Commercial'
  When pmd.neighborhood = 0 Then 'PP_N/A or Error'
  Else Null
End As District,
pmd.neighborhood As GEO,
Trim(pmd.NeighborHoodName) As GEO_Name,
Case
  When pmd.pin Like 'A%' Then 'Athol' -- Athol
  When pmd.pin Like 'C%' Then 'CoeurdAlene' -- Coeur d'Alene
  When pmd.pin Like 'D%' Then 'Dalton_Gardens' -- Dalton Gardens
  When pmd.pin Like 'H%' Then 'Hayden' -- Hayden
  When pmd.pin Like 'V%' Then 'Hayden_Lake' -- Hayden Lake
  When pmd.pin Like 'P%' Then 'Post_Falls' -- Post Falls
  When pmd.pin Like 'R%' Then 'Rathdrum' -- Rathdrum
  When pmd.pin Like 'S%' Then 'Spirit_Lake' -- Spirit Lake
  When pmd.pin Like 'X%' Then 'Hauser' -- Hauser
  When pmd.pin Like 'F%' Then 'Fernan Lake Village'
  When pmd.pin Like 'B%' Then 'Bayview'
  When pmd.pin Like 'T%' Then 'Stateline'
  When pmd.pin Like 'U%' Then 'Huetter'
  When pmd.pin Like 'W%' Then 'Worley'
  When pmd.pin Like 'Y%' Then 'Harrison'
  When pmd.pin Like 'E%' Then 'Business Personal Property'
  When pmd.pin Like 'G%' Then 'Cable TV'
  When pmd.pin Like 'KC-%' Then 'Test Parcels'
  When pmd.pin Like 'M%' Then 'Mobile Homes'
  When pmd.pin Like '0%' Then 'Kootenai County'
  When pmd.pin Like '5%' Then 'Kootenai County'
  When pmd.pin Like '4%' Then 'Kootenai County'
  When pmd.pin Like 'UP%' Then 'Operating Property'
  When pmd.pin Like 'L%' Then 'Float Homes'
  Else 'UNKNOWN'
End As PIN_City,
Trim(pmd.pin) As PIN,
Trim(pmd.AIN) As AIN,
lh.TotalMktValue,
ld.lcm,
Trim(lcm.tbl_element_desc) As LandMethod,
ld.LandType As LandTypeNum,
lt.land_type_desc As LandType,
-- String_Agg(lt.land_type_desc, ', ') As AggregatedLandTypes,
ld.LandDetailType,
ld.SiteRating,
sr.tbl_element_desc As Legend,
ld.BaseRate,
ld.SoilIdent,
ld.LDAcres,
ld.ActualFrontage,
ld.DepthFactor,
ld.SoilProdFactor,
ld.SmallAcreFactor

-- Land Header
From LandHeader As lh
-- Land Detail
Join LandDetail As ld On lh.id = ld.LandHeaderId
  And ld.EffStatus = 'A'
  And lh.PostingSource = ld.PostingSource
-- Land Types
Left Join land_types As lt On ld.LandType = lt.land_type
Left Join codes_table As lcm On Cast(lcm.tbl_element As Int) = ld.lcm
  And lcm.code_status = 'A'
  And lcm.tbl_type_code = 'lcmshortdesc'
  -- 'lcmshortdesc' (aka Land Types)
Left Join codes_table As sr On sr.tbl_element = ld.SiteRating
  And sr.code_status = 'A'
  And sr.tbl_type_code = 'siterating'
  -- 'siterating' (aka Legends)
Join parceltableview As pmd On lh.RevObjId = pmd.lrsn
  And pmd.EffStatus = 'A'
  And pmd.neighborhood <> 0

Where lh.EffStatus = 'A'
  And lh.PostingSource = 'A'
  And ld.PostingSource = 'A'

parcels.sql

This is the base parcel extract for the dashboard. It produces the parcel identifiers, district grouping, neighborhood geography, city inference from PIN prefixes, and the active property-class fields that other dashboard outputs can join back to.

Select Distinct
Case
  When pm.neighborhood >= 9000 Then 'Manufactured_Homes'
  When pm.neighborhood >= 6003 Then 'District_6'
  When pm.neighborhood = 6002 Then 'Manufactured_Homes'
  When pm.neighborhood = 6001 Then 'District_6'
  When pm.neighborhood = 6000 Then 'Manufactured_Homes'
  When pm.neighborhood >= 5003 Then 'District_5'
  When pm.neighborhood = 5002 Then 'Manufactured_Homes'
  When pm.neighborhood = 5001 Then 'District_5'
  When pm.neighborhood = 5000 Then 'Manufactured_Homes'
  When pm.neighborhood >= 4000 Then 'District_4'
  When pm.neighborhood >= 3000 Then 'District_3'
  When pm.neighborhood >= 2000 Then 'District_2'
  When pm.neighborhood >= 1021 Then 'District_1'
  When pm.neighborhood = 1020 Then 'Manufactured_Homes'
  When pm.neighborhood >= 1001 Then 'District_1'
  When pm.neighborhood = 1000 Then 'Manufactured_Homes'
  When pm.neighborhood >= 451 Then 'Commercial'
  When pm.neighborhood = 450 Then 'Specialized_Cell_Towers'
  When pm.neighborhood >= 1 Then 'Commercial'
  When pm.neighborhood = 0 Then 'Other (PP, OP, NA, Error)'
  Else Null
End As District,
pm.neighborhood As GEO,
Trim(pm.NeighborHoodName) As GEO_Name,
pm.lrsn,
Trim(pm.pin) As PIN,
Trim(pm.AIN) As AIN,
Case
  When pm.pin Like 'A%' Then 'Athol' -- Athol
  When pm.pin Like 'C%' Then 'CoeurdAlene' -- Coeur d'Alene
  When pm.pin Like 'D%' Then 'Dalton_Gardens' -- Dalton Gardens
  When pm.pin Like 'H%' Then 'Hayden' -- Hayden
  When pm.pin Like 'V%' Then 'Hayden_Lake' -- Hayden Lake
  When pm.pin Like 'P%' Then 'Post_Falls' -- Post Falls
  When pm.pin Like 'R%' Then 'Rathdrum' -- Rathdrum
  When pm.pin Like 'S%' Then 'Spirit_Lake' -- Spirit Lake
  When pm.pin Like 'X%' Then 'Hauser' -- Hauser
  When pm.pin Like 'F%' Then 'Fernan Lake Village'
  When pm.pin Like 'B%' Then 'Bayview'
  When pm.pin Like 'T%' Then 'Stateline'
  When pm.pin Like 'U%' Then 'Huetter'
  When pm.pin Like 'W%' Then 'Worley'
  When pm.pin Like 'Y%' Then 'Harrison'
  When pm.pin Like 'E%' Then 'Business Personal Property'
  When pm.pin Like 'G%' Then 'Cable TV'
  When pm.pin Like 'KC-%' Then 'Test Parcels'
  When pm.pin Like 'M%' Then 'Mobile Homes'
  When pm.pin Like '0%' Then 'Kootenai County'
  When pm.pin Like '5%' Then 'Kootenai County'
  When pm.pin Like '4%' Then 'Kootenai County'
  When pm.pin Like 'UP%' Then 'Operating Property'
  When pm.pin Like 'L%' Then 'Float Homes'
  Else 'UNKNOWN'
End As PIN_City,
pm.ClassCD,
Trim(pm.PropClassDescr) As Property_Class_Description,
pm.EffStatus

From parceltableview As pm

Where pm.EffStatus = 'A'

values_assessed.sql

This query rolls up last year’s total assessed value by parcel. It is the simplest valuation measure in the set and gives the dashboard a single assessed-value figure per lrsn.

Select
i.RevObjId As lrsn,
Sum(c.ValueAmount) As CadValue_TotalAssessed

From CadRoll r
Join CadLevel l On r.Id = l.CadRollId
Join CadInv i On l.Id = i.CadLevelId
Join tsbv_cadastre As c
  On c.CadRollId = r.Id
  And c.CadInvId = i.Id
  And c.ValueType = 109 -- Variable

Where r.AssessmentYear = Year(GetDate()) - 1 -- Last year's data, as this year hasn't been created until June.

Group By
i.RevObjId

values_assessed_by_category.sql

This is the category breakout query. It takes the prior year’s assessed values and groups them by parcel plus FullGroupCode, which makes it possible to compare the composition of assessed value across land and improvement categories.

Select
i.RevObjId As lrsn,
c.FullGroupCode,
Sum(c.ValueAmount) As CadValue_ByCat

From CadRoll r
Join CadLevel l On r.Id = l.CadRollId
Join CadInv i On l.Id = i.CadLevelId
Join tsbv_cadastre As c
  On c.CadRollId = r.Id
  And c.CadInvId = i.Id
  And c.ValueType = 470 -- 470 AssessedByCat Assessed Value

Where r.AssessmentYear = Year(GetDate()) - 1 -- Last year's data, as this year hasn't been created until June.

Group By
i.RevObjId,
c.FullGroupCode

-- By Cat Optional views

/*
-- Improvement
And c.FullGroupCode In (
    '25', '26', '26H', '27', '30', '31H', '32', '33', '34H', '35', '36', '37H', '38', '39', '41H', '42', '43', '45',
    '46H', '47H', '48H', '49H', '50H', '51', '51P', '55H', '56P', '56Q', '56Q2', '56Q3', '57P', '58P', '58Q', '58Q2',
    '58Q3', '58Q4', '59P', '59Q', '59Q2', '59Q3', '59Q4', '63P', '63Q', '63Q2', '63Q3', '63Q4', '65H', '66P', '67',
    '67L', '67P', '68P', '68Q', '68Q2', '68Q3', '68Q4', '69P', '69Q', '69Q2', '69Q3', '69Q4', '70P', '71P', '71Q',
    '71Q2', '71Q3', '71Q4', '72P', '72Q', '72Q2', '72Q3', '72Q4', '75P', '81', '81P'
)
*/

/*
-- Land
And c.FullGroupCode In (
    '01', '03', '04', '05', '06', '07', '09', '10', '10H', '11', '12', '12H', '13', '14', '15', '15H', '16', '17',
    '18', '19', '20', '20H', '21', '22', '25L', '26LH', '27L', '81L'
)
*/

values_assessed_ten_year.sql

This is the historical trend query. It produces a parcel-level assessed value time series across ten years, keeping the parcel geography and district context attached so the dashboard can compare long-range value movement by place and parcel.

Select Distinct
i.RevObjId As lrsn,
Case
  When pmd.neighborhood >= 9000 Then 'Manufactured Homes'
  When pmd.neighborhood >= 6003 Then 'District 6'
  When pmd.neighborhood = 6002 Then 'Manufactured Homes'
  When pmd.neighborhood = 6001 Then 'District 6'
  When pmd.neighborhood = 6000 Then 'Manufactured Homes'
  When pmd.neighborhood >= 5003 Then 'District 5'
  When pmd.neighborhood = 5002 Then 'Manufactured Homes'
  When pmd.neighborhood = 5001 Then 'District 5'
  When pmd.neighborhood = 5000 Then 'Manufactured Homes'
  When pmd.neighborhood >= 4000 Then 'District 4'
  When pmd.neighborhood >= 3000 Then 'District 3'
  When pmd.neighborhood >= 2000 Then 'District 2'
  When pmd.neighborhood >= 1021 Then 'District 1'
  When pmd.neighborhood = 1020 Then 'Manufactured Homes'
  When pmd.neighborhood >= 1001 Then 'District 1'
  When pmd.neighborhood = 1000 Then 'Manufactured Homes'
  When pmd.neighborhood >= 451 Then 'Commercial'
  When pmd.neighborhood = 450 Then 'Personal Property'
  When pmd.neighborhood >= 1 Then 'Commercial'
  When pmd.neighborhood = 0 Then 'PP_N/A or Error'
  Else Null
End As District,
pmd.neighborhood As GEO,
Trim(pmd.NeighborHoodName) As GEO_Name,
Case
  When pmd.pin Like 'A%' Then 'Athol' -- Athol
  When pmd.pin Like 'C%' Then 'CoeurdAlene' -- Coeur d'Alene
  When pmd.pin Like 'D%' Then 'Dalton_Gardens' -- Dalton Gardens
  When pmd.pin Like 'H%' Then 'Hayden' -- Hayden
  When pmd.pin Like 'V%' Then 'Hayden_Lake' -- Hayden Lake
  When pmd.pin Like 'P%' Then 'Post_Falls' -- Post Falls
  When pmd.pin Like 'R%' Then 'Rathdrum' -- Rathdrum
  When pmd.pin Like 'S%' Then 'Spirit_Lake' -- Spirit Lake
  When pmd.pin Like 'X%' Then 'Hauser' -- Hauser
  When pmd.pin Like 'F%' Then 'Fernan Lake Village'
  When pmd.pin Like 'B%' Then 'Bayview'
  When pmd.pin Like 'T%' Then 'Stateline'
  When pmd.pin Like 'U%' Then 'Huetter'
  When pmd.pin Like 'W%' Then 'Worley'
  When pmd.pin Like 'Y%' Then 'Harrison'
  When pmd.pin Like 'E%' Then 'Business Personal Property'
  When pmd.pin Like 'G%' Then 'Cable TV'
  When pmd.pin Like 'KC-%' Then 'Test Parcels'
  When pmd.pin Like 'M%' Then 'Mobile Homes'
  When pmd.pin Like '0%' Then 'Kootenai County'
  When pmd.pin Like '5%' Then 'Kootenai County'
  When pmd.pin Like '4%' Then 'Kootenai County'
  When pmd.pin Like 'UP%' Then 'Operating Property'
  When pmd.pin Like 'L%' Then 'Float Homes'
  Else 'UNKNOWN'
End As PIN_City,
Trim(pmd.pin) As PIN,
Trim(pmd.AIN) As AIN,
r.AssessmentYear As AssessmentYear_TenYear,
Cast(Concat('01/01/', r.AssessmentYear) As Date) As AppraisalDate,
Sum(c.ValueAmount) As AssessedValue

From CadRoll r
Join CadLevel l On r.Id = l.CadRollId
Join CadInv i On l.Id = i.CadLevelId
Join tsbv_cadastre As c
  On c.CadRollId = r.Id
  And c.CadInvId = i.Id
  And c.ValueType = 109 -- 109 Total Assessed Value
Join parceltableview As pmd On i.RevObjId = pmd.lrsn
  And pmd.EffStatus = 'A'
  And pmd.neighborhood <> 0

Where r.AssessmentYear Between Year(GetDate()) - 10 And Year(GetDate())

Group By
i.RevObjId,
r.AssessmentYear,
pmd.neighborhood,
pmd.NeighborHoodName,
pmd.pin,
pmd.AIN

values_net_tax_value.sql

This query calculates last year’s net taxable value by parcel. In the dashboard it supports the comparison between total assessed value and the portion that remains taxable after exemptions and other adjustments.

Select
i.RevObjId As lrsn,
Sum(c.ValueAmount) As CadValue_NetTax

From CadRoll r
Join CadLevel l On r.Id = l.CadRollId
Join CadInv i On l.Id = i.CadLevelId
Join tsbv_cadastre As c
  On c.CadRollId = r.Id
  And c.CadInvId = i.Id
  And c.ValueType = 455 -- Net Taxable

Where r.AssessmentYear = Year(GetDate()) - 1 -- Last year's data, as this year hasn't been created until June.

Group By
i.RevObjId

Visuals

The public demo is split into focused pages so visitors can inspect the interaction model built on the sanitized exports:

Information

Overview page describing scope, data inputs, and what each dashboard section covers.

Maps

Includes parcel district fills, assessed ten-year GEO bubbles, assessed-by-category GEO bubbles, assessed-vs-net-tax parcel comparisons, and land-rates mapping.

Graphs

Charts parcel counts, assessed trends, category summaries, assessed-vs-net-tax comparisons, and land-rate patterns using the same shared filters.

Tables

Provides parcel-level tables plus summary tables for the other metric bundles with paging and shared filter state.