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.trdate2. 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 GO2.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' GO2.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; GO3. Now, how do we get a stored procedure to execute from MS Query within MS Excel?
- Within MS Excel, first choose 2 cells for input for the variables (go ahead and put the data there).
- Choose the Data menu, Import External Data, New Database Query.
- Choose the data source that corresponds to your Elite database.
- After logging in, you should see the query wizard, choose cancel.
- Choose Yes when prompted to continue editing in MS Query.
- Next you should see the "Add Tables" dialog, choose close. You have now arrived at the basic MS Query window (looks remarkably similar to MS Access).
- Under the View menu, choose the SQL option. Use the following code to execute the stored procedure.
{CALL dbo.usp_Trust_OS_Checks(?,?)}
- You will get an alert saying that the SQL cannot be represented graphically, choose OK.
- Next you will be prompted for the two parameters. Enter the data requested (in my case, 606 and 1/31/2010).
- You should now see the data in MS Query.
- From the File menu, choose Return Data to MS Excel.
- You will be prompted with the following dialog box.
- Choose the cell you would like the data to start in, and then choose the Parameters option. Here you have the option to either prompt for the value, use a static value, or get a value from a cell. If you choose the latter, you will have an option to automatically refresh the data when the value changes (if you have more than 1 parameter, I'd recommend not choosing this). When you are done, choose OK and OK and your data should magically appear within MS Excel.
No comments:
Post a Comment