Create a view within SQL that can show (only) the manual Journal Entries from Control

This page will walk you step by step through generating a view and or an export from SQL that shows only manual Journal Entries from Control.

1. Go to the setup tab and enter reporting setup menu
2. In reporting setup chose “Reports On Main Menu”
3. Within “Reports On Main Menu” choose the sub-category of “User Reports”
4. Hit the add button at the top and choose new SQL Report
5. For menu item name you can name it “Manually Entered Gl Journal Entries Report”
6. Choose SQL and enter the query below into the box
7. In the options box choose create view and save
8. Now save your report

DECLARE @StartDate datetime;
DECLARE @EndDate datetime;
SET @StartDate = 
SET @EndDate =  
SELECT     Employee.FirstName +' '+ Employee.LastName AS Employee,
           Journal.CompletedDateTime AS JournalDate,
           Journal.Description AS Description,
           GLAccount.AccountName,
           Ledger.Amount,
           Ledger.EntryDateTime AS GLDate,
           TransHeader.OrderNumber,
           Ledger.JournalID
FROM  Ledger
LEFT JOIN GLAccount ON GLAccount.ID = Ledger.GLAccountID
LEFT JOIN Journal ON Journal.ID = Ledger.JournalID
LEFT JOIN Employee ON Journal.EmployeeID = Employee.ID
LEFT JOIN TransHeader ON Journal.TransactionID = TransHeader.ID
WHERE Journal.ClassTypeID = 20035
AND EntryDateTime BETWEEN @StartDate AND @EndDate
ORDER BY JournalID DESC

Contributor: Jeremy, Cyrious Software

Date: 9/2011

Version: Control 4.0+