Reporting - Stonefield Query

How do I sort on a field that doesn't appear in the report?

You can only sort on fields that are included in the report. If you want to sort on a field that doesn't appear in the report, add the field to the report and in the Properties dialog for that field, turn off the Display this field in the report setting. This tells Stonefield Query to not display that field in the report but to still retrieve it from the database. You can then sort on this field in the sort step of the report wizard.

How can I show a count of each group in a summary report?

Turn on the Show count in group footer setting in the Properties dialog for the grouped field.

On an advanced layout report, a field that contains dollar amounts is split onto two lines, with the rightmost digits on the second line. What causes this and how do I fix it?

When you run a non-advanced layout report, Stonefield Query figures out the maximum width of each field for the current data set and sizes the field appropriately. However, when you make it into an advanced layout report, the field sizes can't be dynamically adjusted, so it uses the width you defined for the field in the Properties dialog. If some values end up being wider than that, the field wraps to the next row. So, the solution is to make the field wider in the Advanced Report Designer.

How do I use more than 10 values in a "is one of" filter?

You can chain "is one of" conditions by specifying 10 values for the first one, then adding another condition using "or" as the connection, specifying the same field, choosing "is one of" for the operator, and entering another 10 values.

I added a field to a chart in Step 2 but didn't put it in the chart (that is, it still appears in the Available fields list in Step 3). If I try to use that field in an expression for the chart title, I get an error when running the report. The same happens with a cross-tab report.

Fields you leave in the Available Fields list in Step 3 of the Chart or Cross-Tab Wizards aren't retrieved from the database so you can't do anything with them in the report. They are there so you can quickly change the report to include a different field if you wish.

I have a report showing both header and detail records. The problem is that the sum of the header amounts is too high.

This problem occurs when you include header and detail fields in the detail area of the report. For example, this report shows fields from Orders and Order Lines. Freight comes from Orders.

Because Stonefield Query sums fields for each line in the detail band, header fields are summed multiple times. For example, the freight for order 10248 is $32.38 but because it's summed, it's added up three times (once for each order line) to give a total of $97.14, which is incorrect. The grand total at the end of the report is also many times higher than it should be.

There are a couple of solutions to this:

Include fields from the header table in the group header by turning on the Include this field in group header option on the Grouping page of the Field Properties dialog. As you can see, Freight is only displayed once per order.

The downside of this approach is that you can't sum fields displayed in the group header so you can't display the total freight at the end of the report or another grouping level.
Turn on Advanced layout in step 5 of the report wizard. In the Advanced Report Designer, create a variable called, for example, FreightTotal, with Value to store and Initial value both set to 0 and Calculation type set to Sum. Double-click the Group Footer band and put something like the following into the On exit expression:
```
  _vfp.SetVar('FreightTotal', FreightTotal + Freight)
```
This tells the report engine to add up the freight amounts only when the group (in this case, the order number) changes. Double-click the Freight field and set Print repeated values to No on the Print When page. Finally, add a new field to the Summary band with FreightTotal as the expression. Here's the result: the Freight amount is only shown once per order line and has a total at the end of the report.

If you want subtotals shown at another grouping level, such as Customer Number, set the Reset value based on setting for the variable to the other group field and add a field to the Group Footer band with FreightTotal as the expression and Calculation type (on the Calculate page) set to None.

I'm having trouble finding the field I need for a report.

If there are a lot of fields in your database, it can be challenging to find the one you're looking for. Here are some tips that may help:

If you're not sure what a field contains, click the Values button in step 2 of the report wizards. The different values displayed in the dialog often give a clue as to what the field is used for.
Fields are displayed by default in alphabetical order. This makes it easy to find a field in the list. However, sometimes it makes more sense to display fields in the order they appear in the table. Click the button to display fields in table order.
A long list of fields can make it difficult to find the ones you're interested in. If you know part of the field caption you're looking for (such as a field with "tax" somewhere in the caption), click the button, enter the text, and click OK. If there are any fields that match, the first one is selected. Press F3 to find the next one containing the same text.
A long list of tables can make it difficult to find the ones you're interested in. Once you've selected fields from one table, chances are you're next going to select fields from a directly-related table. For example, if you chose a field from Customers, you are more likely to choose a field from Orders than from Products. Click the button to reduce the list of tables to only those directly related to ones you've already selected fields from.
If you're familiar with the name of your application's tables and fields, you might find it easier to locate the tables and fields you want by their real names. Turn on the Display real table and field names option in the Options dialog to display the real name followed by the descriptive name in parentheses.
Sometimes the fields in an application are organized into pairs of "header-detail" tables; invoices and invoice lines is a classic example. These are called "header-detail" pairs because the information displayed in the header or top of an invoice goes in the invoices table and the line items go in the invoice lines table. When looking for a particular field, ask yourself if there's only one of these things in the invoice (invoice number, invoice date, customer number, and so on) or if there can be more than one (product description, quantity sold, etc.). If there's only one, you'll find the field in the header (in this case, invoice) table. If there's more than one, it'll be in the detail (invoice lines in this example) table.