Crystal Report Totals

Keri Eadie · January 2018

I have created a crystal report that Groups Proposals by Solicitor, then Proposal Ask Status. The details list the constituents and the proposal information (ie ask, expected and funded amount). I have then totaled these amounts. It works beautifully. Now I need to count the number of actions attached to the proposal. When I add the action information in it counts the proposal amounts for each action. So for example if I have one proposal with an ask amount of $5000 and two actions in the group total it calculates the ask amount as $10,000 instead of $5,000. How do I count the number of actions without ruining my totals?

Ryan Hyde · January 2018

I don't use Crystal at all, so this is me just spitballing, but can you write a formula that takes the total you're seeing and divide it by the number of actions? That should result in the same total regardless of the number of actions associated.

Melissa S Graves · January 2018

Maybe export a unique field - like action import ID in your export and use that for your count.

Marie Stark · January 2018

You might be able to do it with a Running Total field.

James Andrews · January 2018

Keri Eadie:

I have created a crystal report that Groups Proposals by Solicitor, then Proposal Ask Status. The details list the constituents and the proposal information (ie ask, expected and funded amount). I have then totaled these amounts. It works beautifully. Now I need to count the number of actions attached to the proposal. When I add the action information in it counts the proposal amounts for each action. So for example if I have one proposal with an ask amount of $5000 and two actions in the group total it calculates the ask amount as $10,000 instead of $5,000. How do I count the number of actions without ruining my totals?

Use a running total instead of a summary -- you don't have a lot of control over summaries, and you do over running totals. Tell it to evaluate on change of field: proposal ID. Then tell it to reset on each group.

John Heizer · January 2018

Keri Eadie:

I have created a crystal report that Groups Proposals by Solicitor, then Proposal Ask Status. The details list the constituents and the proposal information (ie ask, expected and funded amount). I have then totaled these amounts. It works beautifully. Now I need to count the number of actions attached to the proposal. When I add the action information in it counts the proposal amounts for each action. So for example if I have one proposal with an ask amount of $5000 and two actions in the group total it calculates the ask amount as $10,000 instead of $5,000. How do I count the number of actions without ruining my totals?

Because you might have multiple Action records for each Proposal you'll wind up with multiple rows for each Proposal (one-to-many relationship). There are two possible approaches:

1) Group the report by Proposal and make the Actions the Details. Depending on how you structure the reported sums you still may wind up with incorrect Ask Amount totals.

2) Create a subreport for the Actions (and exclude those tables completely from the main report, which will eliminate the one-to-many problem). This will keep the Actions separated from the Proposals.

EDIT: A 3rd approach would be to create a SQL statement (a "Command" in CR parlance) that only returns a COUNT of the Actions without a detail record for each Proposal.

Elaine Tucker · January 2018

I agree with using a subreport to total actions.

Catherine Avena 2 · January 2018

What about including the total summary field in your mdb file? Just make sure you set the parameters so that it only includes any actions linked to the proposal.

Keri Eadie · January 2018

Keri Eadie:

I have created a crystal report that Groups Proposals by Solicitor, then Proposal Ask Status. The details list the constituents and the proposal information (ie ask, expected and funded amount). I have then totaled these amounts. It works beautifully. Now I need to count the number of actions attached to the proposal. When I add the action information in it counts the proposal amounts for each action. So for example if I have one proposal with an ask amount of $5000 and two actions in the group total it calculates the ask amount as $10,000 instead of $5,000. How do I count the number of actions without ruining my totals?

Thanks everyone. I ended up adding a group for the proposals and then using the running totals - evaluate on change of field (the proposals)

Crystal Report Totals

Comments

Categories