Summary and repetitive data

[461mmk]

Hi everyone!

Here is my problem:
I have 3 tables with the following data

table 1: eventTypeId (uniqueidentifier); eventType (string)
table 2: eventId (uniqueidentifier); eventTypeId; eventCost (decimal)
table 3: actionID (uniqueidentifier); eventId; actionCost (decimal)

As you can see, I have Types of Events; Events (with cost) and Actions of Events (with cost)

I need a report with the following:

1. Summary of Action Cost per Event
2. Summary of Action Cost and Event Cost per Type of Event

I'm using Crystal Reports .NET with Visual Studio 2003.

I made a dataset (XML) with the tables mentioned and a report with the following formulas:

Summary of Action Cost per Event: sum ({table2.eventCost}, {table2.eventId} ) - works fine.

Summary of Action Cost and Event Cost per Type od Event: sum ({table3.actionCost}, {table1.eventTypeId}) + sum ({table2.eventCost}, {table1.eventTypeId})

what I get is the eventCost added one time for each Action on that event. Example:

Event1; Event1Cost: 100
Action1Cost (Event 1): 10
Action2Cost (Event 1): 15

Correct summary: 125 (100 + 10 + 15)
Result on the report: 225 (100 + 10 + 100 + 15)

What can I do?
Thanks in advance.

 

[synapsevampire]

Since you only have one {table2.eventCost} per{table1.eventTypeId}, try using a maximum or minimum instead of a sum for this field.

-k

 

[IdoMillet]

The problem is due to the fact that each event record is repeated for each action it has.

The simplest solution is to:

1. Group the report (at some level) on EventID

2. Compute the total event cost as a RUNNING TOTAL and set (in the Running Total creation dialog) the EVALUATE option to Once for Each EventID group. This will ensure the event cost gets summed only once for each event.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[461mmk]

Thanks for the tips.

I do have more than one event pet type of event.

My report has the following structure:

Group 1 - eventTypeID
should show Summary of Action Cost and Event Cost per Type of Event

Group 2 - eventID
should show Summary of Action Cost per Event and Summary of Action Cost per Event + Event cost

I couln't make it work with running totals maybe I did something wrong.

 

[IdoMillet]

In the running total, sum the Action Cost, Evaluate once for each Group Level #2, and reset for the appropriate level for which you need the sum (level 2, level 1, or grand total).

If this doesn't work, I'll eat my hat... )

Just describe what options you selected in the running total if you are still experiencing problems.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[461mmk]

Thanks!

You don't need to eat your hat. It works fine.

Cheers

 

[IdoMillet]

And then they tell we should get more fiber in our diets... )

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com