Database Analysis of Sage 500 using Excel- Database Queries, Filters and Pivot Tables

This training videos is for any Sage 500 customer who is interested in using Microsoft Excel to analyze their GL data. We will create a Database Query in Excel to connect to Sage 500 data and then analyze that data using Filters and Pivot Tables. Thank you, Doug Leasure Copy and modify this SQL Query: select a.CompanyID, FiscYear, FiscPer, GLAcctNo, a.[Description], DebitAmt-CreditAmt as Amount, 'Actual' BudgetTypeID, AcctGroupID, case when AcctCatID IN ('1','5','7','8') THEN 'Debit' when AcctCatID='9' THEN 'Statistical' else 'Credit' end AcctType, substring(GLAcctNo,1,4)+'-'+substring(GLAcctNo,5,2)+'-'+substring(GLAcctNo,7,2)+'-'+substring(GLAcctNo,9,2) FmtAcctNo, substring(GLAcctNo,1,4) Natrl, substring(GLAcctNo,5,2) Division, substring(GLAcctNo,7,2) Dept, substring(GLAcctNo,9,2) Region from sage500_app..tglAcctHist ah left join sage500_app..tglAccount a on a.GLAcctKey=ah.GLAcctKey left join sage500_app..tglNaturalAcct na on a.NaturalAcctKey=na.NaturalAcctKey left join sage500_app..tglAcctGroup ag on na.AcctGroupKey=ag.AcctGroupKey left join sage500_app..tglAcctCategory ac on ag.AcctCategoryKey=ac.AcctCategoryKey where a.CompanyID='SOA' Union All select a.CompanyID, FiscYear, FiscPer, GLAcctNo, a.[Description], BudgetAmt as Amount, BudgetTypeID, AcctGroupID, case when AcctCatID IN ('1','5','7','8') THEN 'Debit' when AcctCatID='9' THEN 'Statistical' else 'Credit' end AcctType, substring(GLAcctNo,1,4)+'-'+substring(GLAcctNo,5,2)+'-'+substring(GLAcctNo,7,2)+'-'+substring(GLAcctNo,9,2) FmtAcctNo, substring(GLAcctNo,1,4) Natrl, substring(GLAcctNo,5,2) Division, substring(GLAcctNo,7,2) Dept, substring(GLAcctNo,9,2) Region from sage500_app..tglBudget b left join sage500_app..tglBudgetType bt on b.BudgetTypeKey=bt.BudgetTypeKey left join sage500_app..tglAccount a on a.GLAcctKey=b.GLAcctKey left join sage500_app..tglNaturalAcct na on a.NaturalAcctKey=na.NaturalAcctKey left join sage500_app..tglAcctGroup ag on na.AcctGroupKey=ag.AcctGroupKey left join sage500_app..tglAcctCategory ac on ag.AcctCategoryKey=ac.AcctCategoryKey where a.CompanyID='SOA'

Видео Database Analysis of Sage 500 using Excel- Database Queries, Filters and Pivot Tables автора Улучшаем бухгалтерский учёт в Экселе