Re: Deer Ramone Briscobar
- From: "Briscobar" <youcant@xxxxxxxxxxxxxx>
- Date: Tue, 6 Mar 2007 14:10:33 -0500
"Frisbee®" <billLASTNAME@xxxxxxxxx> wrote in message
news:uuhjJPBYHHA.3824@xxxxxxxxxxxxxxxxxxxxxxx
Kindly contact me via IM or possibly answer here...
W(hy)TF will Crystal not allow you to do summaries or running totals on
certain formulas?
Let me stop you right here....CR gets its data in 2 passes. The first pass
on the DB, it grabs the necessary data from it's SELECT statement. On the
second pass, it calculates summaries and other "WhilePrintingRecords"
functions. Or, a summary of a forumla that uses a summary. So, you can't do
a summary of a summary, since all summaries are done at the same time.
There are a couple ways I get around this. The first is to use manual
running totals. They're not pretty, but they're not hard to implement. You
can create a formula that introduces a variable, and adds to it based on
certain criteria. So, for instance, below, you can create manual running
totals instead of using SUM({@Q1 Wages}, {Employees.Employee No}), as well
as your Q2, Q3, and Q4 summaries. Then you can use those manual running
totals in a summarized formula like the one you've just created.
Another method that I'm not a fan of, but will work, is subreports. I tend
not to use this method because of the increase in time it takes to run the
report. But for your requirements, it may work.
So basically, I recommend "tricking" Crystal into thinking it's doing a
regular summary, but it's really doing a summary of a manual running total.
I may have read somewhere that SQL Server 2005 Reporting Services allows you
to summaries of summaries, but I haven't used it and I may be mistaken. If
you have that option, you may want to give it a try.
I have a formula in Group 2 of 2, and it has to be calculated on other
group summaries. Unfortunately, I also need a Group 1 total and a grand
total, but Crystal will not play nicely.
Clues on solution?
Formula pasted below (it's a BEAR) This is for a UCT-6 report, if you
were curious (SUTA)
If {@Quarter} = 1 Then
If Sum ({@Q1 Wages}, {Employees.Employee No}) < {@Quarter Ceiling} Then
0
Else
Sum ({@Q1 Wages}, {Employees.Employee No}) - {@Quarter Ceiling}
Else If {@Quarter} = 2 Then
If Sum ({@Q1 Wages}, {Employees.Employee No}) +
Sum ({@Q2 Wages}, {Employees.Employee No}) < {@Quarter Ceiling} Then
0
Else If Sum ({@Q1 Wages}, {Employees.Employee No}) >= {@Quarter Ceiling}
Then
Sum ({@Q2 Wages}, {Employees.Employee No})
Else
Sum ({@Q1 Wages}, {Employees.Employee No}) +
Sum ({@Q2 Wages}, {Employees.Employee No}) - {@Quarter Ceiling}
Else If {@Quarter} = 3 Then
If Sum ({@Q1 Wages}, {Employees.Employee No}) +
Sum ({@Q2 Wages}, {Employees.Employee No}) +
Sum ({@Q3 Wages}, {Employees.Employee No}) < {@Quarter Ceiling} Then
0
Else If Sum ({@Q1 Wages}, {Employees.Employee No}) +
Sum ({@Q2 Wages}, {Employees.Employee No}) >= {@Quarter Ceiling}
Then
Sum ({@Q3 Wages}, {Employees.Employee No})
Else
Sum ({@Q1 Wages}, {Employees.Employee No}) +
Sum ({@Q2 Wages}, {Employees.Employee No}) +
Sum ({@Q3 Wages}, {Employees.Employee No}) - {@Quarter Ceiling}
Else If {@Quarter} = 4 Then
If Sum ({@Q1 Wages}, {Employees.Employee No}) +
Sum ({@Q2 Wages}, {Employees.Employee No}) +
Sum ({@Q3 Wages}, {Employees.Employee No}) +
Sum ({@Q4 Wages}, {Employees.Employee No}) < {@Quarter Ceiling} Then
0
Else If Sum ({@Q1 Wages}, {Employees.Employee No}) +
Sum ({@Q2 Wages}, {Employees.Employee No}) +
Sum ({@Q3 Wages}, {Employees.Employee No}) >= {@Quarter Ceiling}
Then
Sum ({@Q4 Wages}, {Employees.Employee No})
Else
Sum ({@Q1 Wages}, {Employees.Employee No}) +
Sum ({@Q2 Wages}, {Employees.Employee No}) +
Sum ({@Q3 Wages}, {Employees.Employee No}) +
Sum ({@Q4 Wages}, {Employees.Employee No}) - {@Quarter Ceiling}
Else
0;
That's an ugly formula. Better you than me, broseph. Would a Select/Case
format be cleaner? Or a combination of the two? 4 Case statements each with
if-then statements embedded in them? Might make for easier reading, if
that's at all important.
KB
.
- References:
- Deer Ramone Briscobar
- From: FrisbeeŽ
- Deer Ramone Briscobar
- Prev by Date: Deer Ramone Briscobar
- Next by Date: What version do i need?
- Previous by thread: Deer Ramone Briscobar
- Next by thread: What version do i need?
- Index(es):
Relevant Pages
|
