Creating totals in Crystal Reports 2008
I created an outstanding invoices report in Crystal Reports 2008. The data is pulled from an Access database.
I have a formula that calculates the remaining amount for each OPEN contract. In order for that formula to work I had to have all invoices included in the report. Then I used if then else statements to make it so that the entire row would be "blank" for each invoice that has been paid. I then used the Section Expert (by right clicking on Details in design view) to "Suppress Blank Sections."
The report is now how I want it except for TOTALS.
I want a grand total for remaining amounts and a grand total for unpaid invoices.
I cannot use a "summary" to sum either of them up because of how the report was created.
Is there another way for me to total these two columns? If so, how?
Thank you in advance for your time.
I have a formula that calculates the remaining amount for each OPEN contract. In order for that formula to work I had to have all invoices included in the report. Then I used if then else statements to make it so that the entire row would be "blank" for each invoice that has been paid. I then used the Section Expert (by right clicking on Details in design view) to "Suppress Blank Sections."
The report is now how I want it except for TOTALS.
I want a grand total for remaining amounts and a grand total for unpaid invoices.
I cannot use a "summary" to sum either of them up because of how the report was created.
Is there another way for me to total these two columns? If so, how?
Thank you in advance for your time.
Don't pull the fully paid invoiced to the report so that you can suppress the lines. Filter out the fully paid invoices in the record selection so that none of the fully paid invoices appear on the report, whether suppressed or not. Once you do this, then you can do a simple some of those amounts.
It is that straight forward, unless I misundetstood your question.
It is that straight forward, unless I misundetstood your question.
ASKER
Hlanai:
I originally used record select to show only unpaid invoices. The formula to calculate the remaining amounts per contract looked something like this:
{table1.originalAmount} - Sum({table2.invoiceAmount}
CR only summed the invoices that were in the report, then.
Also, using record select made it so that only contracts with unpaid invoices showed up in the report. I need remaining amounts for all open contracts whether there are unpaid invoices or not.
This is why I created the report the way I did.
mlmcc:
As far as getting the data set correctly, I returned blank strings for all data fields relating to each paid invoice so that I could suppress those sections.
The two reason I even have the paid invoices in the report is so that
(1) I could calculate the remaining amounts for each contract and
(2) All open contracts and their remaining amounts showed up.
Like I said that made it so that my report showed exactly what I wanted. So to clarify, the report is essentially outstanding invoices AND amounts remaining.
When I select Insert - Summary and then select unpaidInvoices from the drop down menu, the options in the next drop down of how to summarize the options are Maximum, Minimum, Count, etc. BUT Sum, Average, Median, etc. are not included. I am assuming that this is because I am returning a blank string (as explained above).
If you could clarify where you would put this:
If (HidingFormula) then
0
Else
{Amount Field}
then I will be able to let you know if it will work.
Both:
I hope I didn't confuse you even more. This should hopefully break it down:
Please let me know if you know a better way to filter, hide, or suppress the paid invoices in a way where I will be able to:
(1) Show the remaining amounts for each open contract whether the contract has unpaid invoices at the time or not.
AND
(2) Calculate these remaining amounts CORRECTLY.
AND
(3) Summarize the unpaid invoices as a grand total in the footer.
p.s. I would like to summarize the remaining amounts as a grand total in the footer too, but when I go to Insert - Summary, the pull down menu does not include my report field amountRemaining. I am assuming this is because there is a Sum in the formula for the field. This is one item I would like to have in the report but it will not make or brake it. Please let me know if you have any ideas as to why it will not let me summarize that field.
I originally used record select to show only unpaid invoices. The formula to calculate the remaining amounts per contract looked something like this:
{table1.originalAmount} - Sum({table2.invoiceAmount}
CR only summed the invoices that were in the report, then.
Also, using record select made it so that only contracts with unpaid invoices showed up in the report. I need remaining amounts for all open contracts whether there are unpaid invoices or not.
This is why I created the report the way I did.
mlmcc:
As far as getting the data set correctly, I returned blank strings for all data fields relating to each paid invoice so that I could suppress those sections.
The two reason I even have the paid invoices in the report is so that
(1) I could calculate the remaining amounts for each contract and
(2) All open contracts and their remaining amounts showed up.
Like I said that made it so that my report showed exactly what I wanted. So to clarify, the report is essentially outstanding invoices AND amounts remaining.
When I select Insert - Summary and then select unpaidInvoices from the drop down menu, the options in the next drop down of how to summarize the options are Maximum, Minimum, Count, etc. BUT Sum, Average, Median, etc. are not included. I am assuming that this is because I am returning a blank string (as explained above).
If you could clarify where you would put this:
If (HidingFormula) then
0
Else
{Amount Field}
then I will be able to let you know if it will work.
Both:
I hope I didn't confuse you even more. This should hopefully break it down:
Please let me know if you know a better way to filter, hide, or suppress the paid invoices in a way where I will be able to:
(1) Show the remaining amounts for each open contract whether the contract has unpaid invoices at the time or not.
AND
(2) Calculate these remaining amounts CORRECTLY.
AND
(3) Summarize the unpaid invoices as a grand total in the footer.
p.s. I would like to summarize the remaining amounts as a grand total in the footer too, but when I go to Insert - Summary, the pull down menu does not include my report field amountRemaining. I am assuming this is because there is a Sum in the formula for the field. This is one item I would like to have in the report but it will not make or brake it. Please let me know if you have any ideas as to why it will not let me summarize that field.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
mlmcc:
I see what you mean now. That is so simple I cannot believe I didn't think of it myself. It worked perfectly for the invoices, but I still can't get the remaining amounts to total. Do you have any suggestions for that?
I see what you mean now. That is so simple I cannot believe I didn't think of it myself. It worked perfectly for the invoices, but I still can't get the remaining amounts to total. Do you have any suggestions for that?
Is the remaining amount a field in the database?
mlmcc
mlmcc
ASKER
mlmcc:
No. It is a field in my report. The formula looks something like this:
{Table1.originalAmount} - Sum ({Table2.invoiceAmount}, {Table1.contractTitle})
If it were a field in the database then my report would have been much simpler. It is actually a query in the database.
No. It is a field in my report. The formula looks something like this:
{Table1.originalAmount} - Sum ({Table2.invoiceAmount}, {Table1.contractTitle})
If it were a field in the database then my report would have been much simpler. It is actually a query in the database.
Try this idea for the grand total
Sum({Table1.originalAmount
mlmcc
Sum({Table1.originalAmount
mlmcc
ASKER
mlmcc:
I tried that earlier! There must be a lot of incorrect numbers from years ago so the total does not come out right.
Thank you for all of your help.
I tried that earlier! There must be a lot of incorrect numbers from years ago so the total does not come out right.
Thank you for all of your help.
If you have bad data then you really can't do much in the report unless you can determine it is a bad record and ignore it.
mlmcc
mlmcc
A formula like
If (HidingFormula) then
0
Else
{Amount Field}
You should be able to summarize that to get the value.
If not the please upload the report.
mlmcc