This option will create an Excel based criteria form where you can enter parameters, filters and other options and then execute the report.

From the toolbar select Tools > Reports Wand > Insert Criteria Form. The reports assigned to the currently selected responsibility will be displayed.

Due to the fact that the reports are assigned to a specific responsibility, Reports Wand cannot currently be used with the special “All sets of books access” functionality. If you cannot see any reports, it could be that none have been created or that they have been incorrectly classified by your developer.

You can search for reports by category or by completing a portion of the report name in the search box. Select the report you want to execute. The criteria form is created in Excel. Multiple criteria forms can be inserted into a single sheet.

The structure of this criteria form should be left intact to ensure it operates as designed. It is recommended that you only enter values into the white cells. The example function area below the black line is optional and can be deleted without concern.

Report Short name

       As defined by the report developer and used in the Run Reports Function

Report Name

       As specified by the report developer

Report Title

       This text will be inserted at the top of the report when it is executed.

       You can overtype this with any report title that you would like.

       You can use any Excel functionality or formula to derive a title dynamically e.g. you could incorporate the entered parameter values into the report title using the Excel “Concatenate” formula.

       The date and time will be added automatically.

Report Type – Required value

       At this stage the only valid value is “Sheet”

       This will be used in future to provide other report output formats.

Report To

       This field determines where the report output will be populated.

       If left blank, the output will be populated to a new worksheet which will be created in the active workbook.

       A default output location can be specified as part of the report definition. See the Default Report To section.

       To set the “Report To” location, use the normal method for linking Excel cells. Enter an “=” into the cell and then click on the target cell where you would like the report output to populate. Excel will enter a formula into the cell e.g. =Sheet2!A1.

       Initially the value in the cell may appear as “0”. This will be set correctly once you execute the report the first time.

       It is useful to use an Excel named range as the “Report To” location. Create an Excel named range and link the “Report To” cell to that location or select it by pressing F3.

       Once the report has been executed once, the value displayed in the “Report To” cell will be the report short name. This is the unique code that identifies the report in Reports Wand.

       If you double click the “Report To” cell Reports Wand will take you to the report output location.

Output Mode – Required value

       Use “Replace” to overwrite previous rows or “Append” to insert records below without deleting previous data.

       You can type the value in or select it from a list of values. Double click the cell to open the list of values.

Parameters

       Parameters are optionally specified by the developer. If they have been specified then a value will need to be entered by the user.

       If the report developer linked the parameter to a list of values double click the cell to show the available values.

       Otherwise type the value into the cell or derive the value using other Excel functionality.

       If the report developer provided a default value for this parameter it will be pre-populated.

       If the report developer provided a description or instructions for this parameter they will appear as an Excel cell note.

Filters

       Filters can be used to restrict the data returned by the report query.

       Double click a filter in the “Field” column to select which field you would like to filter by.

       Double click a filter in the “Operator” column to select the filter operator e.g. “Equals” or “Less than”.

       The available operators will depend on the data type of the selected filter field e.g. more operators are available for a text type field than a numeric field.

       If the report developer linked the filter field to a list of values, double click the filter in the “Value” column to show the available values.

       Otherwise type the value into the cell or derive the value using other Excel functionality.

       If the report developer provided a default value for this filter field it will be pre-populated.

       If the report developer provided a description or instructions for this filter field they will appear as an Excel cell note.

Advanced

       By default Reports Wand will include all the filters that you enter and only values that meet all the conditions will be returned.

       If you would like to control which parameters are evaluated using an “AND” condition and which will use an “OR” condition you can specify this in the Advanced cell.

       For example, if you would like to return all values in the query that meet filter condition 1 and 2 or any value that meets filter condition 3, enter the following in the Advanced cell “(1 AND 2) OR 3”.

       You can use any combination of the following ( ) AND OR to build advanced filter conditions.

Limit Row Count

       Enter a number to limit the returned records to the first x rows.

Order

       Add a sort condition to the resulting query.

       Double click the cell to select the fields to sort by from a list of values.

       If the reports developer has activated the advanced “Batching” option for this report you will not be allowed to add your own sort condition. In this case Reports Wand will warn you that your custom sort condition has been ignored but will continue to execute the report.

Example Function

       An example “Run Report” function is added at the end of the criteria form.

       The “Run Report” function is another way of specifying all the required information for a Reports Wand report. It differs from the criteria form in that all the information is contained in the formula in a single cell.

       The parameters of the “Run Report” function must be specified in a particular way. This example function can therefore assist you to format the function parameters correctly and to learn how the function works by providing correctly formatted parameters.

In the example function, almost all of the function parameters are linked to the equivalent field in the criteria form. The only exception is the “Report To” location. Due to the fact that this parameter takes the form of an Excel cell link to indentify the target output location, it cannot be linked to the criteria form. Therefore updating the “Report To” location in the criteria form will not update the “Report To” location in the example “Run Report” function. You need to update this parameter in the function directly.

       The “Run Report” function has the additional feature of being able to return a value. Double click the “Field” and “Operator” cells to select values from a list of values. The function can then be calculated. See the Calculating Run Report Function section for detail on calculating a result using the “Run Report” function.

       This Example Function section can be deleted without invalidating the criteria form.

       See the Run Report Function section for more information on how to use this function.