Re: I need to do some totals of subreports
From: Bob Holmes (rholmes_at_REMOVEmmwec.org)
Date: 02/10/05
- Next message: [LT]: "Shared Variable in Sub Report whith crossed table"
- Previous message: Rob Nicholson: "Set up connection to SQL server"
- In reply to: andrea.dominguez: "I need to do some totals of subreports"
- Next in thread: andrea.dominguez: "Re: I need to do some totals of subreports"
- Reply: andrea.dominguez: "Re: I need to do some totals of subreports"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 10 Feb 2005 07:52:56 -0500
Hi Andrea,
I'll address your issues in the reverse order:
To determine the problem with the zeros, I'd like to know what the field is.
Is this a formula? If so, what is the code in the formula?
To determine your totals, you'll need to restrict the data in the
subreport(s) just as you do in the main report. Whatever you are using for
your record selection in your main report, use it in your subreport. You
may need to link on more than one field.
The more subreports you have, the slower your report will be. If you can
create stored procedures or views that can return just the data you want for
the subreport, that might speed things up. For instance, if the subreports
are only for displaying the totals (you don't need any details), then, if
you can create a stored procedure that will return the totals as a single
record, then that should save processing time.
Also, instead of subreports to return totals for the region group, you might
be able to use a formula in your main report that uses a "shared" variable
to keep a running total of the totals from the inner groups.
If you have the ability to work with the database, I would also look into
adding some fields or tables that might help eliminate your large formulas.
You might be able to add a field that associates a user with a region.
Then, you won't need that formula, it would be just another field in the
database. Also, look to see if there is a table with the user's first and
last names. This would eliminate the need to use the formula for developing
the name from the id.
--
Bob Holmes
"andrea.dominguez" <andrea.dominguez@comcast.net> wrote in message
news:#lGl6iuDFHA.3596@TK2MSFTNGP12.phx.gbl...
> 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) I have attached part of the report
> (.jpg) so you can see what it looks like.
>
> 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 of
> this:
>
> 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. If you look at the
> picture, the Appts., calls, Mail, closed sales are just simple things
like:
>
> if ({conthist.rectype} [1] = A then 1 else 0
>
> the 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.
> 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. So if you looked at my pic at the 90 day pending column
> instead of the region total being 4 3508, it would be 2 3056.
>
> 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 be
> great too. I have checked every obvious thing. And if you look in the pic,
> 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
>
>
>
>
>
>
- Next message: [LT]: "Shared Variable in Sub Report whith crossed table"
- Previous message: Rob Nicholson: "Set up connection to SQL server"
- In reply to: andrea.dominguez: "I need to do some totals of subreports"
- Next in thread: andrea.dominguez: "Re: I need to do some totals of subreports"
- Reply: andrea.dominguez: "Re: I need to do some totals of subreports"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
