Method Community

I'm in the process of creating a report similar to the QuickBooks A/R Aging Detail Report,
but also want to include information from the Customer table and the Activities table. Currently my report is based
on the Customer table as master, with Activity and Transaction as detail tables. This leads to various problems with
the grouping, sorting, and placement of the information I want in my report.

My questions are:

Where do I get detailed information about Report Designer and its various controls?

How do the various tasks in the Group Header dropdown window work, and could I use these to arrange my data?

Can I use the Customer Group Header Group fields task to group based on a field from the Transactions table?

I've already watched all the webinars, read all the forum posts on Report Designer, and produced various reports of the single-customer
type, which have worked fine. This has me stumped however.

any help would be appreciated,

Rolf

Hi Rolf,

Just to get a better understanding can you please advise what information you would want to display on the report? You mentioned the Customer and Activity tables, do you know wht specific fields you want to show? Grouping and sorting may be a little painful but once we have more information we can help guide you.

Additionally we don't have any detailed info or documentation regarding the Report Designer but you can check out the DevExpress documentation. Keep in mind that we're not using the newest version.

- Ashur

Hi Ashur,

What I'm trying to produce looks like this:

<STORE>                                            COLLECTIONS REPORT                                           <A/R Account>
                                                     (overdue invoices and charges)

Name          Collection Note      30 90    Dial      Status      Due Date      Commitment    Age    Balance

1 – 30 days
Customer       Collection Note      1    5      yes/no    active       12/15/2012      12/18/2013    24   1097.12

Total 1 – 30                                                                                                                      <Sub-Total>

31 – 90
Customer       Collection Note       1    5      yes/no    active       12/15/2012      12/18/2013     24    1097.12

Total 1 – 90                                                                                                                       <Sub-Total>

> 90
Customer       Collection Note       1    5      yes/no    active       12/15/2012      12/18/2013     24    1097.12

Total >90                                                                                                                            <Sub-Total>

TOTAL                                                                                                                                 <TOTAL>

Store is custom field Customer.Store and is used as a filter in the Generate Report action.

A/R account is Customer.Transaction.Customer.Account field, and could be used as a filter in the Generate Report action, I hope,
but that may not work as I think I can only use one table for filtering there. In that case I would create multiple report versions
for the A/R account I wish to use, and select as appropriate.

Name is Customer.fullname field.

Collection Note, Dial Status and Commitment are all custom Customer table fields.

Balance is Sum(Customer.Transaction.Customer.Amount) where Transaction.DueDate is in range of aging group (ie. 1-30, 31-90, or >90)

30 is Count of Activities of type Phone per customer for last 30 days

90 is Count of Activities of type Phone per customer for last 90 days

Due date is the latest Transaction.DueDate per customer per aging group
ie. Group Summary value Max(Customer.Transaction.Customer.DueDate) for transactions of type Invoice or Charge.

Age is calculated field (Today() - DueDate), which gives rise to the question: Is there a way to use a summary field in a different calculated field?

So, to summarize, I'm try to create collection reports (by store and by A/R account types) for overdue invoices and charges sorted
by customer and aging group. Only one line per customer per aging group allowed, with it being the summary for any multiple entries
per group.

Hopefully this clarifies what I'm trying to do, and helps you to give me some pointers on this.

thanks,
Rolf

I believe what you are trying to do is possible, however, this type of customization goes beyond the scope of the forums. It would need to be taken to the next level; speaking to an in-house Consultant or a Method Solution Provider.

What you can try doing is creating a custom table then populating it with all the data you need for the report, then creating the report based off of that custom table. In order to design and filter the report as you would like will not be a simple task.

Let me know if you would like to speak with one of our Consultants and I'll get the ball rolling.

- Ashur