Skip to main content
Share / Export

Vista Reporting Recipe

Putting It Together (Build The Statement Step-By-Step)​

This lesson is a full walk-through of building a real Vista-style report query, starting from a simple **SELECT *** and ending with a grouped, security-aware, joined statement.

We’re going to build this final report: total Actual Cost by Job/Phase/Cost Type (with descriptions), for Company 1 across the 2025 posting months.

Step 1) Identify the base table​

Start by confirming the base dataset and its "grain". Here, JCCD is job cost detail (many rows per job).

While exploring, start with TOP (N) so you don’t accidentally return thousands of rows.

SELECT TOP (50)
  *
FROM JCCD
;

Note: without an ORDER BY, SQL Server is free to return any 50 rows. That’s fine for initial exploration — we’ll add sorting later when we care about “latest/highest.”

Step 2) Select your basic columns​

List the core keys + the measure you care about. Also start using a table alias immediately so every column reference is explicit.

SELECT
  JCCostDetail.JCCo,
  JCCostDetail.Job,
  JCCostDetail.Phase,
  JCCostDetail.CostType,
  JCCostDetail.ActualCost
FROM JCCD JCCostDetail
;

Step 3) Filter for Company + Month range​

In Job Cost reporting, filtering by Mth is filtering by the posting month (accounting period).

Tip: use ISO date literals (YYYY-MM-DD) to avoid regional parsing surprises.

SELECT
  JCCostDetail.JCCo,
  JCCostDetail.Job,
  JCCostDetail.Phase,
  JCCostDetail.CostType,
  JCCostDetail.ActualCost
FROM JCCD JCCostDetail
WHERE JCCostDetail.JCCo = 1
  AND JCCostDetail.Mth BETWEEN '2025-01-01' AND '2025-12-01'
;

Step 4) Join HQCO (security) + bring in descriptions​

Add joins one at a time. In Vista, it’s common to join HQCO to apply company data security, then join job/phase/cost-type master/setup tables to pull in "Description" fields.

SELECT
  JCCostDetail.JCCo,
  JCCostDetail.Job,
  Jobs.Description [Job Desc],
  JCCostDetail.Phase,
  JobPhases.Description [Phase Desc],
  JCCostDetail.CostType,
  JCCostTypes.Description [CostType Desc],
  JCCostDetail.ActualCost
FROM JCCD JCCostDetail
JOIN HQCO HQCompany
  ON JCCostDetail.JCCo = HQCompany.HQCo
JOIN JCJM Jobs
  ON JCCostDetail.JCCo = Jobs.JCCo
 AND JCCostDetail.Job  = Jobs.Job
JOIN JCJP JobPhases
  ON JCCostDetail.JCCo  = JobPhases.JCCo
 AND JCCostDetail.Job   = JobPhases.Job
 AND JCCostDetail.Phase = JobPhases.Phase
JOIN JCCT JCCostTypes
  ON JCCostDetail.PhaseGroup = JCCostTypes.PhaseGroup
 AND JCCostDetail.CostType   = JCCostTypes.CostType
WHERE JCCostDetail.JCCo = 1
  AND JCCostDetail.Mth BETWEEN '2025-01-01' AND '2025-12-01'
;

Step 5) Summarize ActualCost (SUM) + add GROUP BY​

Once you introduce SUM(...), every other selected column must be grouped.

SELECT
  JCCostDetail.JCCo,
  JCCostDetail.Job,
  Jobs.Description [Job Desc],
  JCCostDetail.Phase,
  JobPhases.Description [Phase Desc],
  JCCostDetail.CostType,
  JCCostTypes.Description [CostType Desc],
  SUM(JCCostDetail.ActualCost) [Actual Cost]
FROM JCCD JCCostDetail
JOIN HQCO HQCompany ON JCCostDetail.JCCo=HQCompany.HQCo
JOIN JCJM Jobs ON JCCostDetail.JCCo=Jobs.JCCo AND JCCostDetail.Job=Jobs.Job
JOIN JCJP JobPhases ON JCCostDetail.JCCo=JobPhases.JCCo AND JCCostDetail.Job=JobPhases.Job AND JCCostDetail.Phase=JobPhases.Phase
JOIN JCCT JCCostTypes ON JCCostDetail.PhaseGroup=JCCostTypes.PhaseGroup AND JCCostDetail.CostType=JCCostTypes.CostType
WHERE JCCostDetail.JCCo = 1
  AND JCCostDetail.Mth BETWEEN '2025-01-01' AND '2025-12-01'
GROUP BY JCCostDetail.JCCo
  , JCCostDetail.Job
  , Jobs.Description
  , JCCostDetail.Phase
  , JobPhases.Description
  , JCCostDetail.CostType
  , JCCostTypes.Description
;

Step 6) Add ORDER BY for readability​

Sorting doesn’t change the totals—it just makes results easier to scan.

SELECT JCCostDetail.JCCo,
  JCCostDetail.Job,
  Jobs.Description [Job Desc],
  JCCostDetail.Phase,
  JobPhases.Description [Phase Desc],
  JCCostDetail.CostType,
  JCCostTypes.Description [CostType Desc],
  SUM(JCCostDetail.ActualCost) [Actual Cost]
FROM JCCD JCCostDetail
JOIN HQCO HQCompany ON JCCostDetail.JCCo=HQCompany.HQCo
JOIN JCJM Jobs ON JCCostDetail.JCCo=Jobs.JCCo AND JCCostDetail.Job=Jobs.Job
JOIN JCJP JobPhases ON JCCostDetail.JCCo=JobPhases.JCCo AND JCCostDetail.Job=JobPhases.Job AND JCCostDetail.Phase=JobPhases.Phase
JOIN JCCT JCCostTypes ON JCCostDetail.PhaseGroup=JCCostTypes.PhaseGroup AND JCCostDetail.CostType=JCCostTypes.CostType
WHERE JCCostDetail.JCCo = 1
  AND JCCostDetail.Mth BETWEEN '2025-01-01' AND '2025-12-01'
GROUP BY JCCostDetail.JCCo
  , JCCostDetail.Job
  , Jobs.Description
  , JCCostDetail.Phase
  , JobPhases.Description
  , JCCostDetail.CostType
  , JCCostTypes.Description
ORDER BY JCCostDetail.Job
  , JCCostDetail.Phase
  , JCCostDetail.CostType
;