Reporting Integration

This guide covers how to integrate the account hierarchy with reporting queries and WDP procedures.

Primary Interface

portal.vw_wg_accounts is the primary integration point for all reporting.

Always Start Here

When building reports, use portal.vw_wg_accounts as your starting point. It joins all three hierarchy tables into a flat, queryable interface.

Standard Join Pattern

Standard Join Pattern

Join CieTrade fact tables to portal hierarchy

-- Join CieTrade fact tables to portal hierarchy
FROM new_ct.billing_charges bc
JOIN portal.vw_wg_accounts v
ON v.cietrade_account_id = bc.account_id
AND v.location_name = bc.location_name

Critical Join Rule

Always join on BOTH cietrade_account_id (CPID) AND location_name. Address IDs alone are insufficient because location_name is the business key.

Performance-Optimized Pattern

For queries with OR predicates on join keys, use UNION ALL instead:

Optimized Pattern (UNION ALL)

Enables index seeks - replaces Hash Match with Nested Loops

Anti-Pattern (OR Predicate)

AVOID - OR predicate forces Hash Match

-- AVOID: OR predicate forces Hash Match
SELECT ... FROM new_ct.services s
JOIN portal.vw_wg_accounts v
  ON v.cietrade_account_id = s.account_id
  OR v.location_name = s.location_name

WDP Integration Examples

Procedure	Join Pattern	Purpose
usp_stg_refresh_schedule_services	CPID + location_name	Load scheduled services
usp_stg_append_dispatch_weight	CPID + location_name	Get location_id for weight data
usp_export_weight.sql	Uses location_id	Export weight metrics
usp_export_spend_rebate.sql	Uses location_id	Export spend/rebate data

Parent-Level Filtering

Filter by Parent

Get all locations for a specific parent account

-- Filter all locations for a parent
SELECT *
FROM portal.vw_wg_accounts
WHERE parent_account_id = 1197;  -- UPS

Archive Maintenance Example

Delete metrics for all locations under a parent