Skip to main content
Windward

Ad-hoc Queries

autotag-wiki-logo.pngAutoTag B.I. mode has been DEPRECATED as of version 13.

Overview

How to create ad hoc queries.

Details

You flip a template into ad-hoc mode by clicking the B.I. Mode menu button:

File:Wiki/AutoTag/02AutoTag_Reference_Guide/04Special_Features/03Ad-hoc_Queries/BI_Menu.png


This will bring up a separate document/workbook/presentation (hereafter called document) over the template. This document is the template run using the default values for the variables. If any variable has no default and is required, you will then be prompted for a value.

Note: The report document is a generated report, not a template. It has no data sources, no variables, and no tags (it will have tags if the data source for some tags is not defined in the template). The variables shown with the report and the data source(s) used to generate the report all come from the template. When you re-run the report, it re-runs its associated template.

You will see the following menu in the report.

File:Wiki/AutoTag/02AutoTag_Reference_Guide/04Special_Features/03Ad-hoc_Queries/BI_Menu2.png

There are five main menu choices here:

  • Ad-Hoc Queries: This brings up the Ad-Hoc variables window if it is closed or hidden. This button is disabled if the variables window is visible.
  • Template: This will bring up the underlying template as a separate document. If the document is already open, it flips to that document. If the document is not open, it will open it and make it the active document.
  • Run Query: You may wish to set several variable values before re-generating the report. Once you have set the new values for all variables you wish to change, click this button to re-build the report using the new values.
  • Previous: For drill-down, this will back up to the report you drilled-down from. This is for drill-down only, it does not go to the previous ad-hoc query on this document.
  • Forward: If you clicked previous, then you can click this button to go back to the drill-down sub-report you were last in. This is for drill-down only, it does not go to the previous next ad-hoc query on this document.

All queries are set from the drop-down menu for each variable:

File:Wiki/AutoTag/02AutoTag_Reference_Guide/04Special_Features/03Ad-hoc_Queries/BI_Variables.png



This gives you the ability to set ad-hoc queries for each variable:

  • Sort: Sort the results in either increasing or decreasing order. Sort is a global setting across all variables, you cannot sort one variable from A->Z and another Z->A. NOTE: A change in ad-hoc sorting changes the order for any sorts in the select, but it does not add sorts. If there is no “order by” in the select, then changing those values will not change the sort.
  • Filters: This applies a logical filter such as all items who's price is < $10.00 or all sales in the last quarter.
  • Select: This lets you select specific items to display.

Data Refresh

AutoTag lets you refresh the data in your reports or documents in two automated ways (and you can always do it manually via Run Query): Auto Refresh and Timed Refresh.

 

AQautorefresh.png

 

Auto Refresh

Set this option if you want the report to be refreshed (run with the latest set of data) every time a variable is changed. If you do not turn this option on, the data that is displayed is the data that existed when the report was first generated.

Timed Refresh

You can re-run ad hoc results automatically, every N seconds, through the timed refresh feature. You simply click the Timed Refresh button and tell the software how often to refresh the data:

TimedRefresh.JPG

If your data hasn't changed, the report output won't change, either. But if the data has changed, the report output will display the new data.

Ad Hoc Queries for XML Data Sources

A few important notes on ad-hoc queries for XML data sources:
  • Ad-hoc queries for XML data must edit the select statement. This edit assumes and “node = ${var}” is either inside brackets like “[node = ${var}]” or parenthesis like “(node = ${var})”. The wizard will always do this but if you write your own XPath, do not write “[node = ${var} or node2 = ${var2}]”, you must write “[(node = ${var}) or (node2 = ${var2})]

  • We support the XQuery syntax of “order by node, node descending” at the end of the XPath statement. This must be placed at the end of the select and we only support nodes based off the returned node. We do not support descending in the Java engine (this is a dom4j limitation).

  • The .NET engine and AutoTag have the XPath functions windward:matches(string, string) and windward:ends-with(string, string) added to the .NET XPath evaluator (which is XPath 1.0). These match the functionality of these functions in XPath 2.0. The windward: prefix is required as they are additions to the native functions.

  • To handle the AutoTag requirement to return a maximum of 10,000 nodes, we look for the text “[position() < 10000]” at the end of the XPath select (before the order by). If we see this, for any number, we will restrict the returned set of nodes to that number. We have to take this approach because there is no way in XPath to limit the total number of nodes, just the number of branches at a given level.

  • Was this article helpful?