| Author |
Message |
shefe
Groupie
Joined: 09 Feb 2008
Online Status: Offline
Posts: 48
|
  
Topic: Summary with Dates and Amounts
Posted: 18 Mar 2011 at 12:36pm |
|
Hello All,
I have a report on Crystal report 8.5 and i want to display summary before 7/1/2010 with amount without the details but righter after 7/1/2010 it shows the details and amount
Here is a sample
Aron Reeeed
05/1/2003 Charge $150
05/1/2003 Payment $100
09/1/2010 Charge $100
10/30/2010 Payment $50
So the report should like this
Summary before 7/1/2010 $50
09/1/2010 Charge $100
10/30/2010 Payment $50
Grand Total owe $100
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 21 Mar 2011 at 3:43am |
use a formula field to get the values you want
if datefield<date(2010,7,1) and typefield="charge" then amountfield else
if datefield<date(2010,7,1) and typefield="payment" then amountfield * (-1) else 0
then use a sumamry funtion on this fomrula to SUM it
|
| IP Logged |
|
shefe
Groupie
Joined: 09 Feb 2008
Online Status: Offline
Posts: 48
|
Posted: 21 Mar 2011 at 8:47am |
|
This is working fine thanks
Now what i want to do is per student if there grand total is equal $0 then it should not show up on the report, i want to hide or suppress anyone whose balance is $0
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 21 Mar 2011 at 8:52am |
group on the student
use the sum at the group level
use group select statement to exclude theose records
SUM(formula,student)=0
|
| IP Logged |
|
shefe
Groupie
Joined: 09 Feb 2008
Online Status: Offline
Posts: 48
|
Posted: 21 Mar 2011 at 10:08am |
|
Wow
Just working like a charm, evertything is showing up accurately and the grand total and amount owe is perfect
the final phase is where it is kinda complicated
we do Check students who owe us money every fiscal year
so from July 1 2010 until June 30 2011 so when i do select expert to {AccountActivity.Transaction date} in DateTime (2010, 07, 01, 00, 00, 00) to DateTime (2011, 06, 30, 00, 00, 00)
it shows up student who don't owe money for example
Aron Reeeed
04/29/2010 Payment $600
09/03/2010 Credit $44
09/01/2010 Charge $880
1/29/2011 Payment $236
so the amount owe display $0 which is correct but as soon i do select expert to {AccountActivity.Transaction date} in
DateTime (2010, 07, 01, 00, 00, 00) to DateTime (2011, 06, 30, 00, 00,
00)
The report displays the following which is wrong
09/03/2010 Credit $44
09/01/2010 Charge $880
1/29/2011 Payment $236
and amount owe now becomes $600 but this student made a payment on
04/29/2010 in the amount of $600 to balance the account to be $0
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 21 Mar 2011 at 10:24am |
since you have no fiscal year process you will need another way to get your list.
Is your list really students wha have made 'charge' during that time frame?
|
| IP Logged |
|
shefe
Groupie
Joined: 09 Feb 2008
Online Status: Offline
Posts: 48
|
Posted: 21 Mar 2011 at 11:02am |
|
Yes by charges so once a payment is made it deducts it and if they have a credit it deducts too, credit are like discount, scholarship or work study
On each student activity there is transaction date, transaction type transaction id , billing items, amount and i see post date and post status
Is there a way to create a formula to use regular current date or computer date instead of using transaction date ??
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 21 Mar 2011 at 11:11am |
maybe. Don't know your data.
You can just use another group level formula to filter
create another formula to 'flag' the current student records
@flagcurrent as:
if tabledate in {?begindate} to {?enddate} and payment='Chanrge" then 1 else 0
sum this at the group level
any student with any charge between your param dates will have a value >0 so use that to filter.
NOT(SUM(first_formula,student)=0 and SUM(@flagCurrent,student)=0))
|
| IP Logged |
|
shefe
Groupie
Joined: 09 Feb 2008
Online Status: Offline
Posts: 48
|
Posted: 21 Mar 2011 at 4:32pm |
|
wow
You are the best, thank you so much it works
Thanks it is working crystal report is the best ..
Now i am going to replace the charge with payment between the parameter dates and see what i get just as a test
Thanks iti is working crystal report is the best ..
|
| IP Logged |
|
shefe
Groupie
Joined: 09 Feb 2008
Online Status: Offline
Posts: 48
|
Posted: 28 Mar 2011 at 11:13am |
|
How does do a multiple groups on the students who owe money from previous fiscal calendar years to see how much they owe from the formula you gave me ""@flagcurrent as: if tabledate in {?begindate} to {?enddate} and payment='Chanrge" then 1 else 0 sum this at the group level any student with any charge between your param dates will have a value >0 so use that to filter.
NOT(SUM(first_formula,student)=0 and NOT SUM(@flagCurrent,student)=0))""
|
| IP Logged |
|
|
|
