At 06:18 PM 9/25/2016, Bill Niehaus wrote:
R:Base X
Is there a way to print a report based upon distinct values for a
column (trial) in a table
that may have up to 300 rows with a common value for the column (trial)?
I would like to get one row for each unique value in the table along
with some calculated
values since as number of rows with that value for the column.
Bill,
There are several techniques to accomplish such task ...
01. Define a permanent or TEMPORARY view to keep a unique (distinct)
set of all
Trial values.
Then, use that view with distinct values for report along with
other calculations,
etc.
For example:
CREATE VIEW `TrialAndCount` (Trial,TrialCount) +
AS SELECT Trial,COUNT(*) FROM tablename GROUP BY Trial
COMMENT ON VIEW TrialAndCount IS 'Distinct record of Trial IDs
and Total Count'
Try the following example using the RRBYW19 sample database.
CONNECT RRBYW19
CREATE VIEW `InvoicesByCustomer` (CustID,TotalCount) +
AS SELECT CustID,COUNT(*) FROM InvoiceHeader GROUP BY CustID
COMMENT ON VIEW InvoicesByCustomer IS 'Total Number of Invoices
by Customer'
02. While in Report Designer, use the "Suppress Repeated Values"
option for Trail column
When the value of a label is the same across several records,
the Suppress Repeated
Values property allows you to print the value only once each
time the field value
changes. This can be useful in reports where a highly redundant
field has been included
in the detail band. If the detail band overflows onto a new
page, you can force the
suppressed value to print again by using the Reprint On
Subsequent property.
03. Last but not least, use the "Column Break" option for the Trial column
The "Break Header" band prints all objects located within its
boundaries once per
distinct break value as defined in the Breaks properties for
that break before the
Detail band. The Break Header band is often used for displaying
information that
needs to be printed once for each distinct value in the data set.
The "Break Footer" band prints all objects located within its
boundaries once per
distinct break value as defined in the Breaks properties for
that break after the
Detail band. The Break Footer band is often used for displaying
summary information
which uses the Detail band information which needs to be printed
once for each
distinct value in the data set.
There you have it!
Feel free to reach out to me with any questions, or if you need a
sample report to
illustrate the use of such feature.
Very Best R:egards,
Razzak.
--
You received this message because you are subscribed to the Google Groups
"RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
