pull out period of time

[jschill2628]

I would like to just pick out the last quarter for a years worth of data. So if I choose January- December. I just want to pick out Oct, Nov, Dec, and I want to arrange by month. Is there a way to do this. I want to be able to pull out the last Quarter, and well as the yearly total in the crosstabs, by in the graphs I only want to see the last three months worth of data. Does this make sense.

 

[IanWaterman]

YOu can create a formula

@lastQuarter
If Month(YOurdateField) in [10, 11, 12] then {valuefield} else 0

or if you want to filter in select expert

Month(YOurdateField) in [10, 11, 12]

This assumes you have already filtered on year, otherwise you will get those months for all years.

Ian

 

[SmpTraining]

A crosstab or chart reports on all the data in the report. There is no way to filter data in a specific chart etc in the way you want.

I believe you are trying to achieve the following:

Report dates - Jan to Dec

Oct Nov Dec Others Total
20 10 30 540 600
10 20 30 240 300

If you object to the 'Others' column then you will need to use other workarounds such as a)subreports with shared variables and manual cross tabs, b)commands, c)database views.

If you can live with the 'Others' column then the trick is to write a formula that outputs exactly the headings you want to see in the cross tab. Then everything works fine.

So for example,

select month(maximum({?dateRangePrompt})) 
  case 1,2,3 : if month({?OrderDate}) in 10,11,12 then monthname(month(?OrderDate})) else "Other"
  case 4,5,6 : if month({?OrderDate}) in 1,2,3 then monthname(month(?OrderDate})) else "Other"
  case 7,8,9 : if month({?OrderDate}) in 4,5,6 then monthname(month(?OrderDate})) else "Other"
  case 10,11,12 : if month({?OrderDate}) in 7,8,9 then monthname(month(?OrderDate})) else "Other"

I've not tested this so expect some syntax errors but hopefully you get the idea.

I'm happy to help if you run into difficulties.

Steve Phillips, Crystal Reports Trainer & Consultant

http://www.smptraining.co.uk