Hello,

I'm rather new to using Crystal, and have found this forum helpful in answering some of my questions.  However, for some reason, I'm suffering a thought blockage in this one problem I'm currently working on, and would be grateful for any ideas or plans of approach that anyone might have.

Currently, I'm creating a monthly report using C#, and need something along the lines of:

---------------------------
Accounts with Amount Payable
[section includes columns Date, Account Number, Client Name, Associated Affiliates, Amt Payable, Prior Interest]

Accounts with Amount Refundable
[section includes columns Date, Account Number, Client Name, Associated Affiliates, Amt Refunded, Prior Interest]
---------------------------

Basically, it's one report with two sections - one section listing all the accounts with a postive amount payable and the other listing all accounts that have a negative amount payable.  The date is passed in by the user via a C# coded interface.

For some reason, my brain doesn't seem to want to wrap itself around this, so I'm currently encountering two main problems:

- What would be the best way to set it up?  I've considered creating two stored procedures, which in turn lead to two subreports, which in turn would be both inserted into one main report.  But doing so would require me passing in the date parameter into the main report and then into the subreports which would then be passed into the stored procs.  Is that even possible?  Or is this a not very good way to go about it.  For some reason, I get the feeling that there must be another way, but I just can't put my finger on it

- My second problem lies in the proper retrieval of the data and how to correctly display it in the report.  Each client listed (be it payable or refunded) has a number of associated affiliates (in a one to many relationship).  However, in the report, these affiliates need to be listed in a concatenated string for each client record.  I've toyed with it at the stored procedure level, and can't seem to come up with a proper solution.  Is there anything I can do at the report level?

Any help or insight on this would be greatly appreciated.  Thanks in advance!

Thanks for your suggestion, John!  That definitely does help to give another perspective on it. 

I'd prefer to avoid sub-reports too, and minimizing the number of stored procs is definitely something I'm in favour of.  However, a couple of quick clarification questions from someone who isn't as familiar with CR: if grouping is applied to the amt. pay/refund field, wouldn't it group each unique amount value together, which is similar to not grouping it at all since the amounts pay/refunded can be all over the board?  Or is it just me misinterpreting your response?

Unfortunately, the Systems Use Case document for this project dictates that the user wants the report to appear as I've noted (both payable and refunded on the same report but in separated sections), so deviation from it will definitely be frowned upon.

Thanks so much, Brian.

The catch is that there is no field that indicates whether an account is payable or refunded.  The status of the account is determined by the amount listed in the amount payable field, so the accounts change month to month.  However, John's and your point did light a light bulb for me in terms of giving me a direction to try.  I'm going to attempt to do some fancy SQL coding and perhaps see if I can create a temporary table in which I can create an 'payable/refund' type field before selecting the data for the report itself (at which point I can try implementing some grouping).

And I'll definitely look into the SQL loop for concatenating.

Thanks again for the suggestions!

It looks like you could create a formula on your amount field:

Thanks for the update, it sounds like you have come up with a great solution. Your use of temporary tables has reminded me of another SQL tool I need to make better use of.