‍

🧮 Power Query for Accountants: Accessing Viewpoint Vista SQL Data Without Writing Code

You work in accounting, and your system is Viewpoint Vista. Your data lives in a Microsoft SQL Server database, and maybe you’ve heard about T-SQL, stored procedures, or ODBC connections — but you’re not a developer, and you don’t want to be. You want clean, reliable data for your reports.

That’s where Power Query comes in. It’s built into Excel, and it lets you connect to Viewpoint Vista’s SQL data, transform it visually, and document every step — no coding required.

🔌 Step 1: Connect Excel to Viewpoint Vista’s SQL Database

1. In Excel, go to Data → Get Data → From Database → From SQL Server Database
2. Enter your server name (ask IT or check your ODBC settings)
3. Select a SQL view — for example:
   • GLDT for general ledger transactions
   • JCCD for job cost detail
   • PREH for payroll history
   • ARCM for customer master data

✅ Tip: Views in Viewpoint Vista aren’t simply convenient — they’re part of the system’s data security model. Instead of exposing raw tables, Viewpoint Vista uses views to control what data users can access based on their roles and permissions. That means when you connect to a view like GLDT or JCCD, you’re seeing only the data you’re authorized to work with — a safer, more compliant way to build reports.

🧪 Step 2: Shape Your Data with Power Query

Once your data loads, you’ll see a visual editor — no formulas, no code.

Here’s what you can do:

• Rename columns for clarity (e.g., AcctNum → Account Number)
• Filter rows (e.g., only show FiscalYear = 2025)
• Split columns (e.g., separate Location:Rate into two fields)
• Add calculated columns (e.g., Net = Debit - Credit)
• Sort and group (e.g., total costs by Job or Phase)

🧾 Audit Tip: Every transformation step is logged in the “Applied Steps” pane. You can rename steps to match your internal documentation or compliance checklist.

📤 Step 3: Load and Refresh

• Load the cleaned data into an Excel table
• Use the Refresh button to update your data each period — no need to rebuild your logic

📋 Bonus: Pair your Power Query workbook with a monthly close checklist or tax prep tracker to keep your reporting consistent and audit-ready.

✅ Final Thoughts

Power Query is one of many ways to get data out of Viewpoint Vista. You can also export grids directly from Vista or query the database using SQL Server Management Studio (SSMS). Each method has its strengths, but Power Query in Excel happens to be the easiest and most approachable option for beginners — especially those who want repeatable, audit-friendly reports without writing code.

To explore all available views and their descriptions, run the VA View Columns report from within Viewpoint Vista. It’s a great way to discover which views are available for your role and reporting needs.

Need help with Viewpoint Vista, Excel, or reporting workflows?
Reach out to ConstrucTech — we specialize in practical, audit-ready solutions for construction accounting teams. Whether you're building a report, troubleshooting a data pull, or optimizing your monthly close, we’re here to help.

‍