Well, I've been struggling with this for over a day, so it's time to break down and ask for help :-).

I have a report where I'm trying to show the following:

1. Main Case category(s)
 A: Case Location(s)
 B: Practitioner performing Case types at case locations.
 B: Number of cases

For example:

Category: UROLOGY
    CASE TYPE: |Inpatient | Outpatient | Loc B Outpatient|   TOTAL
  Surgeon: Smith       2        3             1               6     
           Jones         3        1           4                 8
        
         Totals         5       4           5                  14
   
As shown above, I want totals (counts) for both case location  and counts for number of cases practitioner performed.

I'm trying to show above for
1.previous month
2. Same Month last year
3. Current Fiscal year vs. last fiscal year (2010). Our FYs run Oct 1 to Sept. 30. So, in a final report I want:

- Counts for previous month (say, July 2011)
- same month in 2010 (i.e., July 2010)
- Stats for current FY (Oct '10 to July '11)
- Stats for prev. FY to date (Oct '09 to July '10)
- The difference of FY '11 to date vs. FY '10 for same period.

Here's what I tried:
1. With Select Expert, filtered by date for previous month,  and created two crosstabs: first crosstab shows data based on date range parameter of prev. month (the 1st to the 31st). Second crosstab would give me FY '10 data, and hopefully the year over year comparisons.

2. To get 2010 data, running totals using the dateserial command for one year earlier.

However, no matter what I do, I cannot pull 2010 data. Either I get 2011 data again, or data which doesn't match true data.

So I'm wondering:
1. Is my Select Expert the Gatekeeper for both crosstabs, and since my parameters are for just July '11, the second crosstab won't show '10 data?
2. Is crosstab the wrong tool?
3. If #2 is true, then what's the best way?
4. Will a subreport be necessary to show the 2010 data and the comparison with 2011?

I'm running Crystal Reports Professional 10.0.5.

Any help would be greatly appreciated. I'm new to this job (although not new to Crystal), and this report will reduce an ENORMOUS manual data entry task.

Thanks in advance.


Edited by kitster100 - 19 Aug 2011 at 5:15am

Thanks, Lockwelle. I'll look into an SP.

To get ALL the data I need for both crosstabs, my Select Expert reads:
table.date > date(2009,09,30).

To narrow it down for just one month (July of 2011) in my 'Previous Month' crosstab, I ihave (?fromdate) and (?todate) parameters of just the previous month in a formula which runs when I refresh the report. In other words, I'm using Select Expert to capture ALL the data I need for the second crosstab I plan to insert (2011 vs 2010 comparisons), however, for the first crosstab, which contains ONLY the previous month, I'm trying to use a formula to filter the Select Expert dataset further.

 However, when I run the report, I get  results based on the Select Expert. It seems to ignore the narrower parameters in my formula.
 
Where am I going wrong?

 Thank you.