Solved

# Helping building a cross-tab report in Crystal

Posted on 2007-07-26
228 Views
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.
0
Question by:changingourworld

LVL 17

Expert Comment

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

Author Comment

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

LVL 17

Expert Comment

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

Author Comment

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

LVL 17

Accepted Solution

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.

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

## Featured Post

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This video discusses moving either the default database or any database to a new volume.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.