Running total help...like Crystal XI 1

[adddeveloper]

I can perform this running total easily w/ Cyrstal reports via the "Running Total" window where:

1. I want a certain expression, here would be the sum of a money column

2. I want it to total on this certain grouping

3. I want it to reset on this certain grouping

So, let's say I have three groupings centering around time zones for the United States:

1. Inner most group is per a state's county

2. 2nd most is per actual state

3. Outter most is per state regions like P.S.T, M.S.T, etc. time zones.

If my detail section has multiple rows b/c of my joins then the totals are duplicated. How can I do a running total like crystal and have it "reset" on the 2nd and 3rd groupings and only do the actual "totalling" based on a criterion?



Thanks!

 

[xlbo]

use the RunningSum function in RS - you specify in the function which change of field you want it to break on....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[adddeveloper]

xlbo,

You don't mean runningvalue() do you?

 

[xlbo]

That's the fella !

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[adddeveloper]

xlbo,

Well, I just used the same scenario I had before when using the simple sum(), but did a "Distinct" in my SQL.

That seemed to do the trick.

Any downers to that?

Thanks!

 

[xlbo]

If it does the trick, it does the trick ! You know your data better than anyone......

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[adddeveloper]

Hmmm...well, Distinct didn't work. I was sceptical for it to be that simple.

If I use runningvalue, that's not giving me what I want. I can't tell the higher group(first grouping) to reset on a lower group (2nd grouping) like in Crystal. I has to be the group itself or I get an error in the build, which won't really help.

 

[xlbo]

Not sure I get what the problem is - can you show an example of the data and how you want it summing up?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[adddeveloper]

Let's say I have the three groupings from most outter to the inner:
1.U.S. four quarters (North, South, East, West)
2. U.S. 50 States (so, each of the 50 states)
3. Each states's top two city's measured by population

...and the sum() is for energy consumption in whatever, let's say Kilo-Watts.
...the above is just an example.

Now, what I'd like is even though the detail band gives me duplicate records, I want to sum correctly per each of the three detail bands. If I have two of the same records in my detail band then the 3rd level grouping is off (doubled), and so will the other two.

 

[xlbo]

maybe a silly question but why do you have duplicates with the same measures against them ? Any kind of running sum will be stumped by that as it uses a change in parent group to determine what to sum - the detail records will not be examined....

If you need to do this and cannot get rid of duplicates, I would suggest creating a new measure which sets any duplicates to 0 by testing the detail line. You can then display the original measure but do your runningvalue calcs on the new measure to get accurate subtotals.

Personally though, I'd get rid of the dupes in your data set - IMHO, that is generally an indication that something is not quite right in your data set

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[adddeveloper]

Thanks xlbo!

Unfortunately, a new requirement has brought about the data duplications.

I can fix the issue with sub-reporting, or using Crystal XI.