Helping building a cross-tab report in Crystal

I am trying to build a crystal report that compares charitable donations between last year to date and this year to date. I can use a filter If contribution date in {range last calendar year} then contribution amount which will give me the total donated for the previous year to date sorted by list code. However, I can't figure out how to get the numbers of donations for each list code to come out.
I've tried using the count of list code with the same formula, but all I get is the total number of contributions over the entire period, rather than the number of contributions for the last year to date (by the way the list codes are text, not numbers).
Any suggestions would be greatly appreciated.
changingourworldAsked:
Who is Participating?
 
MIKESoftware Solutions ConsultantCommented:
You shouldn't need to limit anything....the Crosstab will do the separating for you.

Be sure to add the DATE FIELD twice.....ONCE AS A ROW....AND SET IT TO GROUP ON YEAR.....THEN ONCE AS A COLUMN set it to group on MONTH.

Then add your other REPORTING FIELD AS ROW....and then add your calcs.

you should get this...:


                                                 JAN    FEB    MAR   APR   MAY....ETC...

2006    AAA
            BBB
            CCC
2007   AAA
           BBB
           CCC

And you can play around with the ROWS to have it look like you want...like adding the ROW FIELD before the DATe to get this:


AAA   2006
           2007
BBB   2006
          2007

GET IT???

MIkeV
0
 
MIKESoftware Solutions ConsultantCommented:
Use something like the RECORDNUMBER to either COUNT or DISTINCT COUNT on...in the Crosstab.

Please provide a simple example of what you are trying to achieve....

Thanks
MIkeV
0
 
changingourworldAuthor Commented:
I am trying to acheive a table that looks like the following:

List Name       Total Donations                   # of Donations        Average Donation            
                       Previous Year To Date       (PYTD)                    (PYTD)                            
                       (PYTD)
AAA              $$$$
BBB               $$$$
CCC               $$$$


Thanks.
0
 
MIKESoftware Solutions ConsultantCommented:
To get the number of donations..you need INSERT A SUMMARY field to COUNT ON. I don't know what this is..but it can be a RECORD# or INVOICE# etc..

To get the YEAR OVER YEAR COMPARISON...I would ADD YOUR DATEFIELD as a column.....and set it to group by YEAR.

You can do the same thing in the ROW it just depens on how you want your crosstab to look.

To get the Average...place the SAME FIELD that you used to COUNT on in the SUMMARY area...and this time do an AVERAGE.....

You follow?

MIkeV

0
 
changingourworldAuthor Commented:
That was the direction I was trying; however, while I can get the previous year to sum the donations, when I try and use a formula to count the appeals when limiting the date range, all the records are counted the formula I am using is for the column and the row is Gf.GF_Appeal

if {Gf.Gf_Date} in LastFullMonth then {Gf.Gf_Appeal}
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.