Designing Reports

How do I add wildcards to a parameter?

Wildcards are used in SQL where clauses when comparing strings using the ``like'' operator. For example, to select all records with a name starting with ``Smit'', your query might look something like this: select name, age, shoe_size from person where upper(person.name) like upper('Smit%') You can use a parameter to let the user specify the string prefix to search for (``Smit''). However, parameters may not be inside quotes (see
What does ``Parameter index out of range'' mean?). So what exactly do you put in the WHERE clause? You have to add the parameter and the wildcard separately, concatenating them together using the string concatenation operator. For Oracle and PostgreSQL, that is ``||''. I'm not sure if it's the same for all databases or not. For example, upper(person.name) like upper({?Start of Name} || '%') The characters ``||'' are the string concatenation operator, at least for Oracle and PostgreSQL. The string '%' is the wildchard character that we are appending to the end of the user's string. To search for the user's string anywhere in the name column, add wildcards to the beginning and the end, like this: upper(table.name) like upper('%' || {?Start of Name} || '%')

How do I ignore unspecified parameters?

If you put a parameter in the WHERE clause (the ``Select Records'' text), then the user must specify a value. If he doesn't, then the parameter will be replaced by emptyness, which isn't always what you want. When the user does not specify a parameter value, it would be nice to be able to use some special value like NULL or '%'. Here's a trick that will let you do that. Put the parameter into a formula and put the formula into the WHERE clause. You can then add code to the formula that looks for empty values and returns whatever you want. For example, here is a formula that returns either ``table.column = 'user string' '' or ``table.column is NULL'': if "{?parameter}".empty? "table.column is NULL" else "table.column = '#{?parameter}'" end Warning: the previous formula is untested.

How do I count the number of records?

Use the new ``group.count''
special value. Here's the old answer, for historical interest:
Add a column to your query (using a User Column) that always returns the number 1. Add that column to the details section, then hide it. Finally, add a subtotal on that field for each group you desire. Voila: the number of records in the group! Don't forget to set the subtotals' format strings to ``#,###'' to eliminate the decimal fraction.

How do I add a GROUP BY clause?

You don't. DataVision does not use or allow GROUP BY clauses; it uses ORDER BY and groups the records itself. This is because we normally want to see the detail values for each row. Using GROUP BY ``collapses'' the rows into one row. For instance, the example report groups by office name and job post date but we want to see all the job listings in the details row. Therefore, we can't use GROUP BY. Instead, we ORDER BY the group columns and let DataVision recognize group boundaries. You can use a view or a stored procedure or DataVision's Ruby formulas to do what you want. If you can't figure it out, try asking on the DataVision mailing list.

Can I edit the SQL?

No. Some JDBC drivers do not return enough information to allow DataVision to determine which colums are being returned by a query. For example, the PostgreSQL driver only returns column names, not table or schema names. Thus if two tables have the same column name DataVision would have no way of knowing which column is being returned. In order to allow arbitrary SQL DataVision would need to include a full SQL parser so it could understand which columns were being returned, which were stored procedure calls, etc.