Need to display totals of subreports

[andie18]

Hello,

I sure hope that I am going to explain this well enough for everyone to follow along. I have been working on a report for a considerable amount of time. It is designed in Crystal 8.5 developer and the datasource is GoldMine
5.5 SQL 2000 SP3a.

This report is a summary style report that lists the salesperson and a count of their activites for the week as well as their foecasted sales totals. In order for them to appear of this report, they have to complete any activity within the time range (date parameters)

for the most part it looks like this:

SE Region from 2/1/05 to 2/7/05

SalesPerson New Appt Call Mail closed won lost 90 dayTL
george 3 4 2 0 3 1 2 2 452
fred 4 4 4 4 4 2 2 2 3056
Region TL 7 8 6 4 7 3 4 4 3508
​

I have this report in 2 groups. The first group is for the geographical region the salesperson is located in. I have a formula that takes care ofthis:

if ({conthist.userid})="JSCHMOE" then totext ("NE") else

This is almost 100 lines so I won't bother you with the whole formula.

the second group is for the userid. That is a formula too so it will translate the username into a full name:

if ({conthist.userid})="JSCHMOE" then totext ("Jack Schmoe") else

Again, it is about 100 lines so I won't bother you. In the second group is where I have formulas for the activities. I put the summaries in the footer of group 2. For the most part it isn't rocket science. The report sums Appts., calls, Mail, closed sales are just simple things like:

if ({conthist.rectype} [1] = A then 1 else 0

there are columns for New, Proposals delivered, 90Day pending, 90 TL are subreports. To get the totals for the individual users I have them linked by userid (conthist.userid to cal.userid). The 90 Day TL is a subreport using a shared variable and everything has been running fine (with the exception of the mystery blanks, which are really zero's) for about a year.

A couple of days ago my boss asked me to do totals for each of these columns per group 1 (region) The stuff that wasn't a subreport - no sweat. I just placed a sum of my formula in the footer of group 1. My problem is with the subreports displaying totals in the right place. So I made more subreports and placed them in the footer of group1. I have run into 2 problems that I am not sure what to do.

Problem #1 - it is now lazy dog slow. The performance has dropped considerably. I am running it in the designer. I access the datasource via a file DSN over a VPN connection(Checkpoint) and I am using comcast cable modem.

Problem #2 - even though I have the new subreports linking thru the group #1 (which I created in all subreports and used the same formula), sometimes my totals are not right. From a lot of painful investigation it looks like it is not looking at the people on the list of the main report but everyone who is in that region whether they completed anything or not. I tried to change the links to include the userid but it will then only give me the last person listed. For example, the 90 day pending column instead of the region total being 4 3508, it would be 2 3056. (which belong only to the last salesperson on the list)

I am hoping to fix the subreport links so the new subreport only see the people listed on the main report and adds them and increase the speed. Oh and if anyone knows what's the deal with the mystery zero's that would begreat too. I have checked every obvious thing. And it is internitten. I have 1 user that it shows and the next guy it doesn't.There is no rhyme nor reason that I can find.

Thanks for reading all of this and thank you if you can help me out!

Andrea

 

[synapsevampire]

The mystery blanks probably aren't zeros, more likely nulls.

Using subreports in groups are bad news in general, so if you can create database objects such as Views or Stored Procedures, you'll be much better off.

There probably isn't anything you can do about the speed issue, but if you want the subreport data to reflect specific people, and for specific dates, then you need to link by all of the applicable fields. You state that you are linking through a group, perhaps you meant to say the field that you're grouped on.

This formula kinda threw me:

if ({conthist.rectype} [1] = A then 1 else 0

It's either missing a paren, or perhaps you meant to be using an array of some sort?

-k