In this post, I will be highlighting a common method that I use to automatically pull information from the Elite database directly into MS Excel. I am often asked for a report that is transformed from an ad hoc request into a monthly (or more regularly) deliverable. I try to automate these as much as is possible. As is typical, there's more than one way to accomplish this objective, but here's how I often solve the problem.
1. Write Query to pull the exact information I need. In this case, trust account #606 is required with check dates prior to 1/31/10 (tie to month-end bank statement). This is run after reconciling the account. Therefore, all non-checks should have a presentation flag of 'Y', and are excluded.
SELECT trsttran.trindex AS 'Index #',
matter.mmatter AS 'Matter #',
dbo.udfMattDesc(matter.mmatter) AS 'Matter Description',
dbo.udfTKFullName(matter.mbillaty) AS 'Billing Attorney',
trsttran.trdate AS 'Date',
trsttran.trdocnum AS 'Check #',
ap.apname AS 'Vendor',
dbo.udfTrustDesc(trsttran.trindex) AS 'Description',
trsttran.tramount AS 'Amount'
FROM matter
INNER JOIN timekeep ON mbillaty=tkinit
INNER JOIN trsttran ON trmatter = mmatter
LEFT OUTER JOIN trcheck ON trcheck.trindex=trsttran.trindex
LEFT OUTER JOIN ap ON trcheck.trvendor=ap.apnum
WHERE trsttran.tracct = '606'
AND trsttran.trpresflag='N'
AND trsttran.trdate <= '1/31/2010'
AND trsttran.trdocnum Is Not Null
ORDER BY trsttran.trdate
2. At this point, for an adhoc request, I will often copy/paste the report into MS Excel and off we go. However, if I expect to be running this on a frequent basis, I will use the MS Query add-in to MS Excel and paste the SQL into the MS Query. This works like a charm. However, there are often criteria that will need to be changed each time the report is run. In MS Query, you can add parameters by replacing the criteria value with a '?' (i.e. trdate <= ?) This will allow you to either set a prompt to request the value, or reference a specific cell for a value. BUT, this only works if you have a fairly simple query that can be "graphically represented" in MS Query. So if your query can't be "graphically represented", what do you do? Stored Procedure. (it's not as scary as it sounds)
2.A. The code below will create a stored procedure that runs the code above with 2 variables passed to it. First, the bank account and second, the end date for transactions.
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'dbo'
AND SPECIFIC_NAME = N'usp_Trust_OS_Checks'
)
DROP PROCEDURE dbo.usp_Trust_OS_Checks
GO
CREATE PROCEDURE dbo.usp_Trust_OS_Checks
@tracct VARCHAR(20) = NULL,
@maxdate DATETIME = NULL
AS
SELECT trsttran.trindex AS 'Index #',
matter.mmatter AS 'Matter #',
dbo.udfMattDesc(matter.mmatter) AS 'Matter Description',
dbo.udfTKFullName(matter.mbillaty) AS 'Billing Attorney',
trsttran.trdate AS 'Date',
trsttran.trdocnum AS 'Check #',
ap.apname AS 'Vendor',
dbo.udfTrustDesc(trsttran.trindex) AS 'Description',
trsttran.tramount AS 'Amount'
FROM matter
INNER JOIN timekeep ON mbillaty=tkinit
INNER JOIN trsttran ON trmatter = mmatter
LEFT OUTER JOIN trcheck ON trcheck.trindex=trsttran.trindex
LEFT OUTER JOIN ap ON trcheck.trvendor=ap.apnum
WHERE trsttran.tracct = @tracct
AND trsttran.trpresflag='N'
AND trsttran.trdate <= @maxdate
AND trsttran.trdocnum Is Not Null
ORDER BY trsttran.trdate
GO
2.B. You can execute the stored procedure as such. This will give you the exact same results as the original query.
EXECUTE dbo.usp_Trust_OS_Checks '606','01/31/2010'
GO
2.C. Next I need to grant permission for our read-only account to execute the stored procedure.
GRANT EXECUTE ON dbo.usp_Trust_OS_Checks TO elitereadonly;
GO
3. Now, how do we get a stored procedure to execute from MS Query within MS Excel?
4. Now, anytime someone wants to refresh the information in this report, they can change the parameter values and choose Refresh Data from the data menu (each user must have MS Query installed and the data source defined).
Posts
