Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Helping building a cross-tab report in Crystal

Posted on 2007-07-26
7
Medium Priority
?
229 Views
Last Modified: 2008-01-09
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
Comment
Question by:changingourworld
  • 3
  • 2
5 Comments
 
LVL 17

Expert Comment

by:MIKE
ID: 19577514
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

by:changingourworld
ID: 19577765
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

by:MIKE
ID: 19578024
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

by:changingourworld
ID: 19578172
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

by:
MIKE earned 1000 total points
ID: 19578212
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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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 …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question