Group By report problem

rbuser

Hello,

I am having a problem getting a report to display correctly. The report is
an "Accounts Receivable" report that lists overdue accounts and the
transactions for those accounts.

I have a MSSQL stored procedure returning the data just fine, joining the
ACCOUNTS and ACCOUNT_REGISTER tables by the Account_ID, grouped by the
customer's Account_ID column.

The result I am trying to achieve in layout is this :

______________________________________

Account_ID CustomerName BalanceDue
101 Henry James $300.00
______________________________________
Transaction 1 Amount
Transaction 2 Amount
Transaction 3 Amount
-----------------------------------------------------
______________________________________

Account_ID CustomerName BalanceDue
102 Mick Jaggar $100.00
______________________________________
Transaction 1 Amount
Transaction 2 Amount
-----------------------------------------------------


Right now the report lists the Group Header data (the account data) multiple
times. It does display all the transactions but if there are two
transactions for each account it will print the header band or the master
data twice.

I have tried experimenting with every logical column in the "groups" dialog
at design time, (Account_ID, Transaction_ID, Customer_ID etc) and the result
is the same.

I have the following DBTexts on the GroupHeader[0] band:
Accounts Table <--
Account_ID
ActiveStatus
Balance
Customer Table <--
CustomerName


I have the following DBTexts on the Detail band :
Account_Register Table <-- (Transactions)
Transaction_ID
Description
Amount
Type

The groups defined are : DataPipeline.Account_ID
I tried the Customer_ID, Customer's Account_ID which of course holds the
same value as the Account table Account_ID column, either way it does not
work.


The stored proc that returns the data from MSSQL does the GROUP BY like
this:
GROUP BY
CU.Customer_ID,
A.Account_ID,
CU.Account_ID,
...


I have also tried this :
GROUP BY
A.Account_ID,
CU.Customer_ID,
CU.Account_ID,
...


Can someone please tell me why the Account data (the One) persists on
printing once for each transaction record (the Many)?


Thanks very much

nicocizik

Hi Reid,

There are no known issues like this with the Group Headers printing multiple
times before the group breaks. Double check to be sure your group is set to
break on the correct value and that your data is in fact grouped by the
AccountID. If you still have an issue, see if you can recreate issue using
the Customer and Orders tables in the DBDEMOS database and send me an
example to support@digital-metaphors.com.

Looking at the example of what you would like to see you gave below, it
looks like you could use two tables, one with the AccountID, CustomerName
and BalanceDue, and one with AccountID, Transaction, and Amount. Then link
these two tables in a Master/Detail relationship, traversing the master data
in the group header and traversing the transaction data in a child subreport
inside the detail band. Using this method rather than trying to join all
the data together may make things a bit simpler inside the report layout.

--
Thanks for supporting ReportBuilder! Please vote for ReportBuilder in the
Delphi Informant Readers Choice awards!

http://www.delphizine.com/ballot2004/