Method Community

Hello,

I am trying to create a report that shows every active customer and for each of those customers, shows the next service date and type and their last service date and type. What I want looks like:

Customer          Last Service             Next Service
cust123            11/23/09 interior        2/15/10 exterior

I am basing my report on the Customer table rather than the activity table so that if a customer does not have a service it shows up in the report.

I actually have two questions.

1) How do you do a join on the customer and activity tables so that I could show (and filter) all activities by each customer including showing that a customer has no activities.? I have looked in the forums and it appears that the way to do an SQL join is to use the detail report but when I create a detail report using "Activity" as the "data member"I get a report that shows all activity records repeated under each customer name. If I use Customer.Activity as the data member I get the exact same activty record one or more times under each customer. All I want is to show each customer's activities or lack of activities.

2) Can you display the info from one detail record based upon if it is the min or max of a certain field? For me, I want to show info from the record that has the Max(Activity.DueDateStart) where Activity.ActivityStatus = "Completed"  within the context of each customer.

Can you help me or point me in the right direction to the documentation for the report generator. If I could just submit my own SQL statement and then write my report against the dataset my custom SQL produced would be great!

If you have not already done so, take a look at our community webinar on report designing, it actually takes you through creating a sample report based off the customer table showing activities. In the example the report has the following format:
 
Customer(customer table)

Date(activity table)      Type(activity table)      Duration(activity table)

The fields used from the activity table can be changed to fields you’d like to display instead. Notice this report will still list customers with no activities.

To display detail info based on a min or max you’ll want to pay close attention to the summary editor near the end of the webinar above. In the example I have added a label in the Group Footer to display the sum of the duration for each customer’s activities. You’ll be able to do the same to display the min and max date value by adding a label and modifying the summary properties.

For additional information also see our Report Guru videos.

Finally and somewhat related, take a look at the new "SQL Override" for grid columns feature mentioned in our recent blog. Something you might be interested in.

Please let me know if this helps,

Val,

Can the same thing be done in a Grid?  The nice thing about using a Grid would be Dynamic filtering and sorting and Export.

Thanks!

Ken