Trouble with Grand Total 1

[AKFerris]

I work for an international tradeshow mgmt company that sells booth space and sponsorships. I am creating our comprehensive status report that shows all sales, by event# (using a parameter), grouped in the following ways:

Group 1: Order Type (Exhibit Space, Sponsorship)
Group 2: Order Status (Signed Contract, Unsigned Contract, Reserved, Complimentary)
Group 3: Office Name (We have 8 offices around the world)

The summarized fields are:
-- @contract_value (order_total - order_tax)
-- order_total
-- payments

Each group has a summary and then they are grand-totaled at the end. When I run this report, including all Order Statuses, the results are perfect.

I have a second version of this report that excludes Reserved and Complimentary statuses using a Group Selection Formula. (I'm not sure if I did the Group Select Formula correctly. I used the Expert, but, it doesn't let me specify which group I am referring to? Not sure how that works, might be my problem).

Anyway, the totals for the groups work fine, but, the grand total at the bottom is including the excluded statuses.

I've read about Running Totals and I've tried using that, but, I must be doing something wrong. . .

In case it matters, the Contract_Status table is Left Outer Joined to the Order table. I am working with an off-the-shelf database that has 507 tables, so, I can't control the structure.

Any ideas? Thank you in advance!!

 

[JasonDove]

Running totals are the answer.

For the Grand Total results do not "reset".

"Crystal Reports Formulas Explained" is the most advanced book on the market. Currently available with a free 70 page Crystal Reports XI tutorial:

http://www.scry-business-intelligence.com/products/bookoffer.php

 

[lbass]

I can't really tell how you created your original summaries, but as Jason said, running totals are necessary with group selection, so if you were doing a sum of payments, you would use that in the first running total section, evaluate for each record (probably, if you used inserted summaries before and they gave you the correct results), and reset never.

Running totals will work correctly with group selection without explicitly excluding the non-group selected records.

-LB

 

[AKFerris]

Ok, I went back and took out my summaries and used running totals for group 2 and 3. These are returning the correct results.

When I do a 3rd running total (which I want to [grand]total group 1), its still returning a total that includes the values for the statuses I excluded.

Perhaps I have the group selection formula incorrect?

Using the group expert I put:
{CC385_CHART_INVEN.CC385_ASSIGN_STS} = "SIGNED" or {CC385_CHART_INVEN.CC385_ASSIGN_STS} = "UNSIGNED"

Sorry in advance if I'm being dense. . .I am self taugh and have only been doing this for a few weeks.

 

[lbass]

Do you mean for these values to be included in the report? What values are being excluded?

-LB

 

[AKFerris]

No, I do not want them in the Grand Total which is why I attempted to filter them out using the Group Selection formula.

I have also tried to filter them out using the Record Selection and I get the same results.

It just doesn't make sense. . .

 

[AKFerris]

Clarification: All I want to see on this report are the Statuses: Signed and Unsigned (which forces me to exclude reserved and complimentary).

 

[lbass]

Group selection SELECTS records for the report, it doesn't exclude them. Why not remove them from the report altogether in the RECORD selection formula?

not(
{CC385_CHART_INVEN.CC385_ASSIGN_STS} in ["SIGNED","UNSIGNED"]
)

-LB