I am attaching the two Crystal Reports that I used in my conference session.  Both of them are written in Crystal XI, so you will need to have that installed in order to open them.

Drew

Thank you for posting this Drew.

Drew,

Thanks for posting these sample reports.  Could you help me to ensure I'm understanding the prospect activity report well?

In the prospect activity report, It appears you exported the last action from the constituent record, not necessarily a action linked to a proposal record, correct?  Which, if any, filters did you use for the last action criteria in export?  I see you used the select expert to match up prospect solicitor to action solicitor, is this right? Also, how are the "stoplights" in the last action sub-report used?  I've never seen graphics used before, but I'm imagining it ties to action status or whether or not the action is overdue?

Similar to the last action sub-report, have you ever created a next action sub-report?

Thanks for your help,

jwant

When we converted, BB recommended that we hide the prospect status field and track all prospects through proposals.  This made the process much more cumbersome that it should have been and caused us to create very nebulous proposals just so that we could track the constituent.  A couple of years ago, we determined that this REALLY was not working, so we changed our procedures.  This means that not all actions are linked to proposals, but they still need to be counted for the solicitor metrics.

The filters available for actions on export did not provide the degree of flexibility that we needed, so we export ALL actions.  We use the select expert in the sub-report to get the actions that we are really interested in.  (We do use the last action in the main report, but since that can be any action [such as a research request] and won't necessarily count toward the solicitor's performance, we only use that for a preliminary sort.)

The "Last Action" sub-report is a holdover from a previous version of the report.  There was no need to rename it, because most people would never see it.  It's easy to create next/last action sub-reports based on a combination of the "Completed" checkbox and the action status if you are exporting ALL actions, as we are. Just use the Select Expert to refine which actions you want.

The stoplights are used to ensure that the development officers are keeping up with their prospects.  It is based on the action NotePad date (which really confuses the DOs, but is how management wants it.)  The prospect status also plays a role in determining the speed of the change.  A green stoplight means that they are up to date with that prospect given their current status; yellow means that they are in danger of falling behind with that prospect; and red means that have fallen behind and need to have some kind of contact with that person.

Does that help?

Drew

Drew,

Thanks for your response.  This project took a backseat last week, but I'm now back on it.  I see how exporting all the actions would you give you more flexibility in the crystal report.  Although I've worked with sub-reports before and understand how they are linked to the main report, I can't figure out the logic to use in Select Expert to create the next and last action sub-reports (I only want to display one record in each sub-report: the very next and very last action independent of the action status).

I know the select expert will use the link fields to display only those actions records linked to the prospect (or proposal if I go that route).  I also know I can create another formula: Action Date >= Today for the next action sub-report (and the opposite, <=, for the last action sub-report)  What I can't figure is the logic to filter only the very next or last action record.

Perhaps I need to group the report in some way, or I need to create and insert a formula field into the details section and then filter on this field using select expert.

Any advice would be greatly appreciated.

I think your stoplights are great and make the report intelligent.  As you can see...I don't need to worry about putting these into my report just yet.  But, is there a term to refer to these objects, so that I can look them up in Crystal Help?  Oh wait, I just took a look at them.  These are just objects you've inserted, then used formulas to suppress the entire section they are in.  Good stuff!  I can see how using the suppression formula among others can make a report very dynamic.  Thanks again.

 jwant

You can only do so much with the Select Expert, because it is applied WhileReadingRecords and can only use information from one record.

1. Use the Select Expert to choose all actions >= Today (or < Today [note that the opposite of >= is < and not <=]).
2. Sort your subreport by the action date (ascending).
   
3. Place the action information in the report header for the next action or the footer for the last action.

Alternatively, for step 3, you can use Not(OnFirstRecord) or Not(OnLastRecord) to suppress the subreport sections for actions that you don't want to display.

Drew

Drew,

Thanks for pointing me in the right direction.  I was just about to write you again to help troubleshoot, but I reviewed by sub-report links by double-checking the link (relationship) graph vs. the links in the sub-reports one last time...maybe it was the first.  Well, I found the issue and it's working great!  Thanks a bunch.  Oh, why put the action fields in headers and footers instead of the details section of the sub-reports?

jwant