Manual Cross tab

[MJD500]

Hi Anyone:

I'm trying to make a manual cross tab to show for each business line GH1

TP
CS
LAB

And then for each month (always 3 previous months), I want to show total # of tickets for each GH1
December January February
# Percent # Percent # Percent
TP 29 15% 30 25% 82 25%
CS 55 85% 70 75% 95 75%
Total 84 100% 100 100% 177 100%

I also have to be able to find 1st 2nd and 3rd month. The report runs every month which gets data for 3 previous months. Does anyone know how to do this/

Thank you

Jocelyne

 

[SmpTraining]

If I understand your problem correctly, you could solve as follows:

Create three separate formulas along these lines (I've written the first one to get you started):

@CurrentMonthValue
// Return value if current month
if month({date_to_check}) = month(currentDate) then 
   {field_with_value}
else 
   0

@MonthMinusOne
// Create Formula to return value if date-to-check was last month

@MonthMinusTwo
// Create Formula to return value if date-to-check was month before last

Place these formulas on the report in the details section under the headings 'Current Month', 'Month -1' and 'Month -2'. Of course you could also write formulas to convert these into actual Month names.

Now simply sum the detail level formulas to generate the Group level subtotals. The key feature of this method is that the formulas do not require 'WhilePrintingRecords' and so can be subtotaled (and shown as a percentage) as normal fields can.

Steve Phillips, Crystal Consultant

 

[MJD500]

Hi: Thanks for your help Steve. This will work for me I think. But How do I get
previous months? for example

if month(datetocheck) = month(currentdate) - ?? what to get last month, 2 months ago and 3 months ago?

Thanks again.

Jocelyne

 

[SmpTraining]

Really you need to use the 'datediff' function if your version of crystal supports it (I think it came in for version 8).

There's also the date range function 'LastFullMonth' that might be of some use for month -1.

Sorry - my system's playing up so I can't give you the precise syntax.



Steve Phillips, Crystal Consultant

 

[SmpTraining]

System's working again now - use the datediff function as follows:

@MonthMinusOne
if datediff("m", {date_to_check}, currentdate) = 1 then
{field_with_value} else 0

@MonthMinusTwo
if datediff("m", {date_to_check}, currentdate) = 2 then
{field_with_value} else 0

@MonthMinusThree
if datediff("m", {date_to_check}, currentdate) = 3 then
{field_with_value} else 0

etc.

Steve Phillips, Crystal Consultant

 

[MJD500]

Yes! Thanks. I wasn't too sure on how to use the datediff for this one. I got it!

Jocelyne