SQL, db design or Crystal problem? - wrong data on reports

[kruby]

Hi,

I'm having a problem with correct transaction amounts appearing on my reports. I'm not sure whether it's due to the database design or if I'm not creating the report correctly with Crystal.

I have 3 tables:

1. Deal
Deal_ID (primary key)
Deal_Date
Deal_Amount

2. Attribute_List
Attribute_ID (primary key)
Attribute_Description

3. Deal_Attributes (junction table linking Deals with their Attributes)
Deal_ID (primary key)
Attribute_ID (primary key)

Here's some data.

Deal table
Deal_ID: 1
Deal_Date: 01/01/2001
Deal_Amount: $15 000 000.00

Attribute_List table
Attribute_ID: 1
Attribute_Description: Cross Border
Attribute_ID: 2
Attribute_Description: IPO

Deal_Attribute table
Deal_ID: 1
Attribute_ID:1
Deal_ID: 1
Attribute_ID: 2

(i.e. as you can see in the last table Deal 1 has both attributes: Cross Border and IPO).

The report is grouped by date and the detail section only shows Deal_ID and Deal_Amount. The report has a parameter field on the Attribute_Description field so that the user can choose to see deals with certain attributes. I've placed this in the header section along with a formula field that displays the attribute(s) chosen for the report.

There's no problem if the user chooses "Cross Border" in the parameter field, the correct amount will appear on the report. But, if they choose both "Cross Border" and "IPO" the report doubles the Deal_Amount field and the report displays $30 000 000.00, instead of the deals correct $15 000 000.00 amount. Additionally, the user can choose "All Attributes" if they want to see all the deals. This causes a problem, too, because instead of showing the deal's correct amount, the report doubles up each deal's transaction if it's associated with more than one attribute.

Here's the SQL statement.

SELECT
Attribute_List.Attribute_Description,
Deal.Deal_ID, Deal.Deal_Date, Deal.Deal_Amount

FROM
Deal_Attribute,
Attribute_List,
Deal

WHERE
Deal_Attribute.Attribute_ID = Attribute_List.Attribute_ID AND
Deal_Attribute.Deal_ID = Deal.Deal_ID AND
(Attribute_List.Attribute_Desciption = 'IPO' OR
Attribute_List.Attribute_Description = 'Cross Border')

How can I get the correct transaction amount to show up on the report?

Thanks very much,
Karen

 

[IdoMillet]

Karen,

One easy way to solve this is to Group the report by
Deal_ID, then use a Running Total to sum the deal_amount, but set the 'Evaluate' option to once per group.

This way the deal_amoount gets counted only once for each deal.

If you don't want to group by Deal_ID, you can achieve the same thing by setting the 'Evaluate' option of the Running Total to once per Deal_ID.

Cheers,
- Ido ixm7@psu.edu

 

[kruby]

Thank you so much, that worked.

 

[kruby]

Thanks Ido, that worked, but now I have another issue with the same data concerning running totals.

I'm able to get the true deal count for each dealer (using a running total) and I'm able to get the true count of deals for ALL dealers (using another running total). I need to be able to divide the two running totals to get a dealer's percent of all deals. This field would be placed next to each dealer's total count.

Normally, it would just be a formula such as DealerCount/TotalCount, but from my readings and searching the KB, you can't summarize based on a summary field, especially using running totals (something to do with 2nd pass on the report?).

What would be the workaround for this? I tried the initalize,evaluate,display formula approach and that didn't work.

Any ideas?

Thanks so much,
Karen

 

[IdoMillet]

Karen,

What you are describing doesn't require summarizing summary fields, it only requires creating a formula dividing one running total by another. This, as far as I know, is possible.

Please clarify.

Cheers,
- Ido ixm7@psu.edu

 

[kruby]

Ido, you're right, you can reference running totals in formulas. I've been reading all day on runnnig totals and trying to get this to work and I'm all confused on this end

My two running total fields are #DealersCount - placed on group footer and #TotalCount - placed on report footer. They show correct totals.

I have a formula called @PercentCount whose formula is:
{#DealersCount}/{#RTotalCount} which is on the group footer. The numbers aren't coming out even close to being correct.

Anything I can check that might be causing incorrect numbers?

Thanks.

 

[IdoMillet]

Karen,

The problem is that the Running Total {#RTotalCount}
is "Running". It gets its correct value only at the end of the report when it finishes accumulating.

I would try getting the correct overall count through either SQL or a subreport (passing the value from the subreport to the main report using a shared variable).

Cheers,
- Ido ixm7@psu.edu