Command-Line Interface - Stonefield Query SDK

You can run Stonefield Query from the Windows Command Prompt, the Windows Run dialog, a batch file, or a"nother application so you don't have to find its icon on the Windows Desktop or in the Start menu and so you can have it automatically log in or run a particular report. To do so, execute:

directory\SFQuery.exe

In place of directory, enter the directory where Stonefield Query is installed. If there is a space in the directory, put quotes around this command. For example:

"C:\Program Files\Stonefield Query\SFQuery.exe"

There are two ways you can call Stonefield Query: passing parameters on the command line or putting the parameters into a text file and passing the name of that file to Stonefield Query.

If no output settings are specified when a report is run from the command line, the saved output settings for the report are used if there are any; if there are no saved output settings for the report, the report is previewed.

Command Line Parameters

Stonefield Query accepts the following parameters that tell it how to behave.

For all parameters, if there are any spaces in any part of the parameter, surround the entire parameter with quotes.

The examples are shown over more than one line but they should be one command line.

ini: if the Stonefield Query configuration files aren't located in the same directory as SFQuery.exe and Stonefield Query hasn't been run before on this computer, add "ini=directory" to the command line. In place of directory, enter the directory where the configuration files are located. If there are any spaces in the directory, surround the entire parameter with quotes.

Example:
```
  "C:\Program Files\Stonefield Query\SFQuery.exe"
      "ini=C:\Program Files\Stonefield Query\Data"
```
user and password: to automatically log into Stonefield Query, add "user=username" and "password=password" to the command line. In place of username and password, enter the user name and password. These parameters aren't required and in fact should normally not be passed: if you do pass them and a user with that name is already logged into Stonefield Query, or if all licenses of Stonefield Query are currently in use, you won't be able to run Stonefield Query this way.

Example:
```
  "C:\Program Files\Stonefield Query\SFQuery.exe"
      user=ADMIN password=ADMIN
```
activate: this is rarely needed, but you can have Stonefield Query automatically activate the license online by specifying "activate=serial number," where serial number is the serial number to activate.

Example:
```
  "C:\Program Files\Stonefield Query\sfquery.exe"
      activate=ABCDEFGH
```

database: if more than one database is available, specify a particular database to use by adding "database=databasename" to the command line. In place of databasename, enter the name of the database as you see it in the Stonefield Query Open Database dialog. If there are any spaces in the database name, surround the entire parameter with quotes. If you omit this parameter, the database used the last time Stonefield Query was run is used.

Example:
```
  "C:\Program Files\Stonefield Query\SFQuery.exe"
      "database=My Database"
```

report: if you want Stonefield Query to automatically run a particular report, add "report=reportname" to the command line. In place of reportname, enter the name of the report. Put quotes around the entire parameter (for example, "report=My Activity Report") if there are any spaces in the report name. If you want to run more than one report, specify "report1=reportname1" for the first report, "report2=reportname2" for the second, and so forth.

Example:
```
  "C:\Program Files\Stonefield Query\SFQuery.exe"
      "report=Customer Activity Report"
```
dashboard: if you want Stonefield Query to display a particular dashboard, add "dashboard=dashboardname" to the command line. In place of dashboardname, enter the name of the dashboard. Put quotes around the entire parameter (for example, "dashboard=My Dashboard") if there are any spaces in the dashboard name.

Example:
```
  "C:\Program Files\Stonefield Query\SFQuery.exe"
      "dashboard=My Dashboard"
```
print (or not specified): prints the report to the printer (like clicking the Print button in the Reports Explorer). This affects all reports specified by "report=" parameters.

Example:
```
  "C:\Program Files\Stonefield Query\SFQuery.exe"
      "report=Customer Activity Report" print
```
preview: previews the report on the screen (like clicking the Preview button in the Reports Explorer). This affects all reports specified by "report=" parameters.

Example:
```
  "C:\Program Files\Stonefield Query\SFQuery.exe"
      "report=Customer Activity Report" preview
```
filename: outputs the report to the specified file: "filename=filename.ext." In place of filename.ext, specify the desired filename and extension. The file type is determined by the extension: "XLS" or "XLSX" for a Microsoft Excel file, "CSV" for comma-delimited, "TXT" for a text file, "DOC" or "DOCX" for a Microsoft Word document, "PDF" for a PDF file, and so on. Put quotes around the entire parameter if there are any spaces in the file name. If you want to run more than one report, specify "filename1=filename1.ext" as the file name for the first report, "filename2=filename2.ext" as the name for the second, and so forth.

Example:
```
  "C:\Program Files\Stonefield Query\SFQuery.exe"
      "report=Customer Activity Report"
      "filename=C:\My Documents\Customers.docx"
```
type: since Stonefield Query can output data-only, full format, PivotTable, or chart for Microsoft Excel or a spreadsheet file if you specify an XLS or XLSX extension, specify the type of file you want: "full" for full formatted Excel, "data" for data-only Excel, "fast" for fast data-only Excel, "pivottable" for an Excel PivotTable, "chart" for an Excel chart, or "spreadsheet" for a spreadsheet XLS file.

"pivottable" is only available for cross-tab reports and "chart" is only available for chart reports.

If you want to run more than one report, specify "type1=type1" as the type for the first report, "type2=type2" as the type for the second, and so forth.

Example:
```
  "C:\Program Files\Stonefield Query\SFQuery.exe"
      "report=Customer Activity Report"
      "filename=C:\My Documents\Customers.xlsx" type=data
```

timestamp: if you specify this parameter, Stonefield Query adds a timestamp to the filename.

Example:

  "C:\Program Files\Stonefield Query\SFQuery.exe"
      "report=Customer Activity Report"
      "filename=C:\My Documents\Customers.doc" timestamp

template: uses the specified template for the report: "template=template name." In place of template name, specify the desired template name. If you do not specify a template, the normal template for the report is used. Put quotes around the entire parameter if there are any spaces in the template name.

Example:
```
  "C:\Program Files\Stonefield Query\SFQuery.exe"
      "report=Customer Activity Report"
      template=Elegant
```
mailto: sends an email to the specified email addresses with the report as an attachment. If there are multiple email addresses, separate them with commas. If you don't specify the filename parameter, the report is output as a PDF file with the same name as the report. If you do specify the filename parameter, a file with that name and type is created and attached to the email.
ccto: specifies carbon copy (CC) addresses for the email. If there are multiple email addresses, separate them with commas.
bccto: specifies blind carbon copy (BCC) addresses for the email. If there are multiple email addresses, separate them with commas.
subject: specifies the subject for the email. Put quotes around the entire parameter if there are any spaces in the subject.

body: specified the body for the email. Put quotes around the entire parameter if there are any spaces in the body. For a line break, use <br/>.

Example:

  "C:\Program Files\Stonefield Query\SFQuery.exe"
      "report=Customer Activity Report"
      "mailto=somebody@mycompany.com,someoneelse@mycompany.com"
      ccto=someoneelse@mycompany.com
      bccto=anothersomeone@mycompany.com
      "subject=Customer Activities"
      "body=Here is the customer activity report"

All email related parameters affect all reports specified by "report=" parameters.

noshow: if the report is output to a file, use this parameter to indicate the file is not displayed after it's created (like turning off the Display file after creation option in the Output page of the Reports Explorer). This affects all reports specified by "report=" parameters.

Example:
```
  "C:\Program Files\Stonefield Query\SFQuery.exe"
      "report=Customer Activity Report"
      "filename=C:\My Documents\Customers.doc" noshow
```
value: this specifies a list of values for any ask-at-runtime filter conditions: "value=condition value". In place of condition value, specify the desired value. For dates, use the format YYYY-MM-DD. For logical or Boolean fields, use "T", "True", "Y", or "Yes" for True (anything else is considered to be False). For the "is between" or "is one of" operators, separate the values with a comma (for example, "2002-01-01,2002-12-31" for a "Date is between 01/01/2002 and 12/31/2002" condition). There can be several of these parameters, each for a different condition (the first one is for the first ask-at-runtime condition, the second is for the second condition, and so forth); for example, value=Germany value=2002-01-01,2002-12-31 specifies the values for two conditions. If you want to run more than one report, specify "value1=value1" for each of the values for the first report, "value2=value2" for the values for the second, and so forth. Omit the value (that is, use "value=" without specifying a value) to ignore the condition. Put quotes around the entire parameter if there are any spaces in the value. If the value contains a comma, escape it (prefix it with a backslash); for example, value=My Company\, LLC.

Example:
```
  "C:\Program Files\Stonefield Query\SFQuery.exe"
      "report=Customer Activity Report"
      value=2008-01-01,2008-12-31
```
allowdialogs: specify this parameter to tell Stonefield Query to display the ask-at-runtime dialog if there are any ask-at-runtime conditions and you haven't specified their values using the value parameter. If you omit "allowdialogs" and there are ask-at-runtime conditions for the report, the program terminates with error code -13 (see the chart later in this topic). Specifying this parameter also causes a progress dialog to appear as the report runs. This affects all reports specified by "report=" parameters.

Example:
```
  "C:\Program Files\Stonefield Query\SFQuery.exe"
      "report=Customer Activity Report"
      allowdialogs
```
action: this tells Stonefield Query to perform some maintenance at startup: "action=name." In place of name, specify "reindex" to recreate the indexes for its data files, "repair" to repair and reindex its data files, "setup" to display the Setup dialog normally only displayed the first time you run Stonefield Query, or "fixsecurity" to fix any problems that may have arisen with report security.

You can also specify a startup action by adding Action=name (where "name" is the name of the action) to an Other section in SFQuery.ini.

Example:
```
  "C:\Program Files\Stonefield Query\SFQuery.exe"
      action=setup
```

language: this tells Stonefield Query to use a specific language for dialogs and reports: "language=name." In place of name, specify the name of the language to use, such as "English" or "German."

Example:
```
  "C:\Program Files\Stonefield Query\SFQuery.exe"
      language=French
```

printer: tells Stonefield Query to print the report to a specific printer: "printer=name." In place of name, specify the name of the printer to use as it appears in the Windows Printers and Faxes dialog. Put quotes around the entire parameter if there are any spaces in the name.

Example:
```
  "C:\Program Files\Stonefield Query\SFQuery.exe"
      "report=Customer Activity Report"
      "printer=HP LaserJet"
```
filter: this parameter allows you to specify additional filter conditions to be added to the report: "filter=condition." In place of condition, specify the filter condition. Put quotes around the entire parameter if there are any spaces in the condition.

Example:
```
  "C:\Program Files\Stonefield Query\SFQuery.exe"
      "report=Customer Activity Report"
      "filter=Transaction.Amount > 50"
```

runbygroup: this parameter tells Stonefield Query to output each group to a separate file.

Example:

  "C:\Program Files\Stonefield Query\SFQuery.exe"
      "report=Customer Activity Report"
      runbygroup

appendfile: this parameter tells Stonefield Query to append the report output to an existing file. This option is ignored if the output isn't sent to a PDF file.

Example:
```
  "C:\Program Files\Stonefield Query\SFQuery.exe"
      "report=Customer Activity Report"
      "filename=C:\My Documents\Customers.pdf" appendfile
```
allowemptyresultset: this parameter tells Stonefield Query to act as if the Run report with no records option is turned on: it runs the report even when there are no records that match the filter conditions, resulting in a blank report.

Example:
```
  "C:\Program Files\Stonefield Query\SFQuery.exe"
      "report=Customer Activity Report"
      allowemptyresultset
```
addgroupvalue: this parameter tells Stonefield Query to add the group value to the file name when outputting each group to a separate file.

Example:
```
  "C:\Program Files\Stonefield Query\SFQuery.exe"
      "report=Customer Activity Report"
      runbygroup addgroupvalue
```
select: this parameter tells Stonefield Query to select the specified report name rather than the one selected when Stonefield Query was last used: "select=report name." Put quotes around the entire parameter if there are any spaces in the report name.

Example:
```
  "C:\Program Files\Stonefield Query\SFQuery.exe"
      "select=Customer Activity Report"
```
edit: this parameter tells Stonefield Query to edit the specified report: "edit=report name." Put quotes around the entire parameter if there are any spaces in the report name.

Example:
```
  "C:\Program Files\Stonefield Query\SFQuery.exe"
      "edit=Customer Activity Report"
```
new: this parameter tells Stonefield Query to bring up the New Report Wizard: "new." You can specify the report type with "reptype=type code", where type code is Q for a quick report, X for a cross-tab report, L for a label, C for a chart, and G for a gauge; if this parameter is omitted, the Report Type dialog appears.

Example:
```
  "C:\Program Files\Stonefield Query\SFQuery.exe"
      new reptype=Q
```
login: this parameter tells Stonefield Query to prompt the user to log in when running a dashboard from a shortcut.

Example:
```
  "C:\Program Files\Stonefield Query\SFQuery.exe"
      "report=My Dashboard" login
```
exit: closes Stonefield Query after printing the report. This is useful if you only want Stonefield Query to output the specified report and not display its normal user interface. This affects all reports specified by "report=" parameters. When it terminates, Stonefield Query sets an exit code so you can determine the outcome of the report request. See the Error Codes topic for a list of the codes.

Parameters File

If the command line becomes too long (more than 255 characters) or you'd rather put the parameters into a file for more flexibility (it's probably easier to change the contents of a file than to change how Stonefield Query is called), specify the name of the file containing the parameters by adding "parameters=filename" to the command line, where filename is the name of the file containing the parameters. For example:

"C:\Program Files\Stonefield Query\SFQuery.exe"
    "parameters=C:\My Documents\Parameters.xml"

Specify the parameters in the file using the following XML structure:

<parameters>
  <settings *attributes*/>
  <report *attributes*>
    <values>
      <value value="*value for condition*"/>
    </values>
    *additional values nodes*
  </report>
  <report *attributes*>
    <values>
      <value value="*value for condition*"/>
    </values>
    *additional values nodes*
  </report>
  *additional report nodes*
</parameters>

There is only one settings element per file. As is usual with XML, specify attributes using:

attributename="value"

Quotes are required whether there are spaces in the values or not. Also, XML is case-sensitive, so be sure to specify element and attribute names in lower-case.

The attributes for settings are:

ini: specify the path for the INI file. This attribute is required.

Example:
```
  <settings ini="C:\Program Files\Stonefield Query\Data"/>
```
user and password: specify the user name and password to log in as. These attribute are optional and should be omitted if you want to avoid a problem running Stonefield Query from another application while the user is also logged into it.

Example:
```
  <settings ini="C:\Program Files\Stonefield Query\Data"
      user="ADMIN" password="ADMIN"/>
```
database: specify the database to report on. This attribute is optional; if it isn't specified, the database used the last time the user ran Stonefield Query is used.

Example:
```
  <settings ini="C:\Program Files\Stonefield Query\Data"
      database="My Database"/>
```

language: this tells Stonefield Query to use a specific language for reports. This attribute is optional; if it isn't specified, the language used the last time the user ran Stonefield Query is used.

Example:
```
  <settings ini="C:\Program Files\Stonefield Query\Data"
      language="French"/>
```

exit: specify "yes" to close Stonefield Query after printing the report. This attribute is optional; if it isn't specified, Stonefield Query stays open.

Example:
```
  <settings ini="C:\Program Files\Stonefield Query\Data"
      exit="yes"/>
```
allowdialogs: specify "yes" to tell Stonefield Query to display the ask-at-runtime dialog if there are any ask-at-runtime conditions. This attribute is optional; if you don't specify this and there are ask-at-runtime conditions for the report, the program terminates with error code -13 (see the chart above). Specifying this attribute also causes a progress dialog to appear as the report runs.

Example:
```
  <settings ini="C:\Program Files\Stonefield Query\Data"
      allowdialogs="yes"/>
```
select: this tells Stonefield Query to select the specified report name. This attribute is optional; if it isn't specified, the report selected the last time the user ran Stonefield Query is selected.

Example:
```
  <settings ini="C:\Program Files\Stonefield Query\Data"
      select="Customer Activity Report"/>
```

edit: this tells Stonefield Query to edit the specified report. This attribute is optional.

Example:

  <settings ini="C:\Program Files\Stonefield Query\Data"
      edit="Customer Activity Report"/>

new: this tells Stonefield Query to bring up the New Report Wizard. You can specify the report type with a reptype attribute with a value of Q for a quick report, X for a cross-tab report, L for a label, C for a chart, and G for a gauge; if this attribute is omitted, the Report Type dialog appears. You can also specify a default application view to use with "appview=application view name." These attributes are optional.

Example:
```
  <settings ini="C:\Program Files\Stonefield Query\Data"
      new="yes" reptype="Q" appview="My Application View"/>
```

You can also specify custom attributes. Stonefield Query doesn't use these attributes but code you use anywhere in Stonefield Query could. Here's an example:

<settings ini="C:\Program Files\Stonefield Query\Data"
    MyCustom1="A" MyCustom2="B"/>

Each report element represents one report. You can have as many report nodes as you wish; each will run a report. If there are no value sub-elements of a report element, you can specify an "empty" element by closing the report tag with "/>"; otherwise, you will need a closing tag.

The attributes for the report node are:

name: the name of the report to run. This attribute is required.

Example:
```
  <report name="Customer Activity Report"/>
```
output: the type of output: "preview" to preview the report, "print" to print the report, or "file" to output to file. This attribute is optional; if it isn't specified and the filename attribute is omitted, the report is printed.

Example:
```
  <report name="Customer Activity Report" 
      output="preview"/>
```
printer: tells Stonefield Query to print the report to a specific printer. This attribute is optional; if it isn't specified, the Window default printer is used.

Example:
```
  <report name="Customer Activity Report" 
      printer="HP LaserJet"/>
```
template: the name of the template to use with this report. This attribute is optional; if it isn't specified, the default template for the report is used.

Example:
```
  <report name="Customer Activity Report" 
      template="Elegant"/>
```
filename: the name of the file to output to. This attribute is required if you want to output to a file.

Example:
```
  <report name="Customer Activity Report"
      filename="C:\My Documents\Customers.docx"/>
```
type: since Stonefield Query can output data-only, full format, PivotTable, or chart for Microsoft Excel or a spreadsheet file if you specify an XLS or XLSX extension, specify the type of file you want: "full" for full formatted Excel, "data" for data-only Excel, "fast" for fast data-only Excel, "pivottable" for an Excel PivotTable, "chart" for an Excel chart, or "spreadsheet" for a spreadsheet XLS file. This attribute is optional; if it's omitted, "data" is assumed.

"pivottable" is only available for cross-tab reports and "chart" is only available for chart reports.

Example:
```
  <report name="Customer Activity Report"
      filename="C:\My Documents\Customers.xlsx" 
      type="data"/>
```
timestamp: "yes" to add a timestamp to the filename or "no" to not do that. This attribute is optional; if it is omitted, "no" is assumed.

Example:
```
  <report name="Customer Activity Report"
      filename="C:\My Documents\Customers.doc"
      timestamp="yes"/>
```
display: if the report is output to a file, this attribute indicates if the file is displayed after it's created ("yes") or not ("no"). This attribute is optional; if it isn't specified, the file is displayed.

Example:
```
  <report name="Customer Activity Report"
      filename="C:\My Documents\Customers.doc"
      display="no"/>
```
mailto: sends an email to the specified email addresses with the report as an attachment. If there are multiple email addresses, separate them with commas. If you don't specify the filename attribute, the report is output as a PDF file with the same name as the report. If you do specify the filename attribute, a file with that name and type is created and attached to the email. The mailto attribute is required if you want to email a report.
ccto: uses this attribute as the "carbon copy" or CC addresses. If there are multiple email addresses, separate them with commas. This attribute is optional.
bccto: uses this attribute as the "blind carbon copy" or BCC addresses. If there are multiple email addresses, separate them with commas. This attribute is optional.
subject: the subject for the email. This attribute is optional.

body: the body for the email. You can specify line breaks using "<br>", which must be encoded as "<br>". This attribute is optional.

Example:

  <report name="Customer Activity Report"
      mailto="someone@mycompany.com,someoneelse@mycompany.com"
      ccto="someoneelse@mycompany.com"
      bccto="anothersomeone@mycompany.com"
      subject="Customer Activities"
      body="Here is the customer activities report.&lt;br&gt;This is a new paragraph."/>

erasefile: "yes" to erase the file after the email is sent or "no" to not erase the file. This attribute is optional; if it's omitted, "yes" is assumed.

Example:
```
  <report name="Customer Activity Report"
      mailto="someone@mycompany.com"
      subject="Customer Activities"
      erasefile="no"/>
```
runbygroup: this parameter tells Stonefield Query to output each group to a separate file.

Example:
```
  <report name="Customer Activity Report" 
      runbygroup="yes"/>
```
appendfile: this parameter tells Stonefield Query to append the report output to an existing file. This option is ignored if the output isn't sent to a PDF file.

Example:
```
  <report name="Customer Activity Report"
      filename="C:\My Documents\Customers.pdf"
      appendfile="yes"/>
```
embedfonts: this parameter tells Stonefield Query to embed fonts in the file. This option is ignored if the output isn't sent to a PDF file.

Example:
```
  <report name="Customer Activity Report"
      filename="C:\My Documents\Customers.pdf"
      embedfonts="yes"/>
```

embedinemail: this parameter tells Stonefield Query to embed the report as HTML in the body of the email.

Example:

  <report name="Customer Activity Report"
      mailto="someone@mycompany.com"
      subject="Customer Activities"
      body="Here is the customer activities report."
      embedinemail="yes"/>

headings: this parameter indicates whether the first row of the output includes column headings. This option is ignored if the output isn't sent to an Excel data-only, comma-separated, or text file.

Example:
```
  <report name="Customer Activity Report"
      filename="C:\My Documents\Customers.pdf"
      headings="no"/>
```
allowemptyresultset: this parameter tells Stonefield Query to act as if the Run report with no records option is turned on: it runs the report even when there are no records that match the filter conditions, resulting in a blank report.

Example:
```
  <report name="Customer Activity Report"
      filename="C:\My Documents\Customers.pdf"
      allowemptyresultset="yes"/>
```

addgroupvalue: specify "yes" for this parameter to add the group value to the file name when outputting each group to a separate file.

Example:

  <report name="Customer Activity Report"
      filename="C:\My Documents\Customers.pdf"
      runbygroup="yes" 
      addgroupvalue="yes"/>

filter: this parameter allows you to specify additional filter conditions to be added to the report.

"<" and ">" must be encoded as "<" and ">", respectively.

Example:
```
  <report name="Customer Activity Report"
      filter="Transaction.Amount &gt; 50"/>
```

The values and value elements are used to pass the values for ask-at-runtime conditions to the report. For dates, use the format YYYY-MM-DD. For logical or Boolean fields, use "T", "True", "Y", or "Yes" for True (anything else is considered to be False). For the "is between" or "is one of" operators, separate the values with a comma (for example, "2002-01-01,2002-12-31" for a "Date is between 01/01/2002 and 12/31/2002" condition). Omit the value (that is, use an empty value element) to ignore the condition. If the value contains a comma, escape it (prefix it with a backslash); for example, <value value="My Company\, LLC"</value>.

Example:

<report name="Customer Activity Report">
  <values>
    <value value="2008-01-01,2008-12-31"/>
  </values>
</report>