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.