need help with summary report based on history activity

[andie18]

Hello,

I am writing this report that needs to count how many records have not been touched in "x" amount of days. I have posted this dilemma about a month ago but I had some emergencies to tend to and I am now ready to finish this thing.

This is my enviroment: GoldMine 5.5, MSSQL 2K, Crystal developer 8.5

This is the report: I need to count records (Contact1 - is database) that no history activity has been entered in the past 180 days and over 365 days (Conthist - is database{Conthist.ondate} is field) from today. I also need to seperate it by salesperson (flagged in Contact1 on field: {contact1.Key5}).

This is what I have so far:

the report has 2 groups - group#1 is based on {contact1.key3}. This allows me to sort by geographical region (this company has a lot of sales people and I distribute the reports by region). group#2 is based on {contact1.key5} which is the sales rep name. I then created a sort for distinct count of {contact1.accountno}. I did this because every contact record has a unique accountno and it would not exist w/o one. I then made a subreport that links to the salesrep name. The subreport has one group {contact1.key5} the salesrep name. Then in the TopN/Sort section, I have 2 sorts - 1. is Max. of {conthist.ondate} -descending and 2. is distinct count of {contact1.accountno}and in the select expert I have added this formula:
{CONTHIST.ONDATE} <= (currentdate - 180). I then repeated this process for another sub report that looks at 365 days and over.

When the report runs, it returns records accurately but..... (big but here) the dates are in reverse. Instead of the report reading dates from the bottom of the list (meaning newest date entry) it is looking at the first history entry for that record.

Can anyone tell me where I totally scewed this up? I'd be ever so grateful.

 

[IanWaterman]

Not sure why you are using subreports, when a running total formula will probably do the job for you.

Create one for 180 days and another for 365 days.

Set it to count whatever record

In the evaluate section click formula and use your date range formulas, one for 180 days and the other for 365 days.

In reset get it to reset on group being your sales person.

Insert these two Running Tot formulas in your sales person group footer.

If you need to sum this for whole report, create two more RT's, same as above but leave reset to Never, place in report footer.

Hope this helps

Ian Waterman
UK Crystal Reports Consultant.

 

[DataDivaJill]

i work with both gm and crystal and love both, in fact been nicknamed datadiva and report maven.
i would have created a report asking the to and from date for the history filter.
group on sales rep and by company, and then history date in descending order.

now you have all records by sales and then showing all companies with history in your date frame.
now i would create a counting field of distinct company (accounto) and i would then format the detail section to show only if the counting field =1.
this willl hide all others. and you can count the company at the same time.
contact me if you would like.
jill messier
computer control corp
jillm@ccc24k.com
860-529-6232