In this exercise, you will create a tool for finding uncleared checks older than a specified date. You'll use input cells with queries and Office Connector functions to make the search interactive. You will learn about the following:
Select cell C1, click in the Name box, type CutoffDate and press Enter. For more information, see Creating a Named Range.
Step 3 - Add the Check Count Column
Use a TSCount function to see how many potentially lost checks there are for a given account. The TSCount function returns the number of rows in a table that meet the specified conditions.
Select BankAccountID from the Value drop list and click OK.
Click Add Condition and select Type for Field.
Select Literal Value for Value Type.
Select Chk from the Value drop list and click OK.
Click Add Condition and select Reconciliation Status for Field.
Select Literal Value for Value Type.
Select O from the Value drop list and click OK.
Click Add Condition and select Reference Date for Field.
Select Less or equal for Comparison so we get checks with a reference date on or before what we enter.
Select Parameter for Value Type.
Select CutoffDate from the Value drop list and click OK. Your completed filter should look like this:
Click Finish to insert the list of checks.
Step 5 - Add Interactivity
As the report exists now, you can change either of the input values and click the Refresh button to see updated results. One odd thing about the behavior, though, is the fact that the Potential Lost Checks counts will update before you click Refresh. This is because we used a TSCount formula for the count, and workbook formulas automatically recalculate whenever one of the inputs changes.
In most cases, you will want to leave query parameters in their default state so you can control how the report refreshes. In some cases, though, you want instantaneous updates similar to workbook formulas, and this example will showcase how to accomplish that.
Right-click cell A10 and select Parameters from the popup menu.
Note -
You can actually right click anywhere inside the query results to bring up the context menu.
You will see the two query parameters listed on the left with BankAccountID already selected.
Check the box in the lower-right corner labeled Refresh automatically when cell value changes.
Select CutoffDate and check the Refresh automatically when cell value changes box again.
Click OK
Try modifying the value in C8 using the different bank accounts listed in column A above as a reference.
Try modifying the cutoff date in C1 to see how the report updates as you change input values.
Content updated 12/4/2012 Copyright � 2012 Event 1 Software, Inc. This documentation may not be copied in full or in part without written permission from Event 1 Software, Inc.