record selection based on year in crosstab

[sfabry]

I have a cross tab that is sorted on YEAR > MONTH > PT TYPE so that JAN 11 and JAN 12 are together to be compared etc.
Last we changed midyear the way we recorded a certain set of data thus I have to use different criteria to correctly select data. Below are the formulas I am using. I am verifying JAN 11 data and it is pulling data from a room specified in the NOT clause. Can anyone tell me why?

FORMULAS:
@2011
{v_Surg_Proc_CR.cr_prdate} = {?Date} and
not ({v_basic_case_rec_data.room_mnc} in ["JASU_XRAY", "JGI08", "JGI09", "JGI10", "JGIMOTLTY", "JIXR", "JIXRADDON", "JTRNFSN1", "JTRNFSN2"]) and
{v_basic_case_rec_data.fac_descr} = "XXX" and
not({v_Surg_Proc_CR.surg_service} in ["DIAGNOSTIC","MEDICINE"])
@2012
{v_Surg_Proc_CR.cr_prdate} = {?Date} and
not ({v_CRA_01_General_Stats.bk_pat_type} in ["ASU_LOCAL","IP_GI_XRAY","OP_GI_XRAY"]) and
{v_basic_case_rec_data.fac_descr} = "XXX" and
not({v_Surg_Proc_CR.surg_service} in ["DIAGNOSTIC","MEDICINE"])
@YEAR
year(currentdate)

RECORD SELECTION FORMULA
if ({@year} = ({@year}-1)) then
{@2011} else
if ({@year} = {@year}) then
{@2012}

I actually originally had
if ({@year} = 2011) then
{@2011} else
if ({@year} = 2012) then
{@2012}
with the same incorrect results but then i started fooling around and trying to make the date not hardcoded in the formula. I would actually like to do that once i get this working but one step at a time...
I am using crystal 11. Thank you in advance for any help!

 

[Madawc]

if ({@year} = ({@year}-1)) then

@year isn't going to be one less than itself. I think you've been muddling years from the data and years from currentdate.

I would also advise splitting up those complex formulas. E.g. one could be

{v_basic_case_rec_data.room_mnc} in["JASU_XRAY", "JGI08", "JGI09", "JGI10", "JGIMOTLTY", "JIXR", "JIXRADDON", "JTRNFSN1", "JTRNFSN2"]

This will come out as True or False, and can also be referenced by name in a seleciton. But first you can display the fields next to the unselected data and see how the tests are working.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[sfabry]

do you mean turn each statement into a formula?

 

[Madawc]

Yes. You can then reference the name, perhaps use it for some more complex test.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[sfabry]

I actually think I got this to work by fixing the date -

@YEAR
year({v_Surg_Proc_CR.cr_prdate})
this is also the field that date range parameter is set on

RECORD SELECTION
if ({@year} = (2011)) then
{@2011} else
if ({@year} = 2012) then
{@2012}

Do you know of a way to make this compare this and previous year without having to change the formula with the year change?

 

[Madawc]

Try following my original suggestion

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[sfabry]

i did also set up the formulas but i am not sure what you mean? since i changed from currentdate to the date param i should change it back to "- 1"?

 

[Madawc]

I've said it as plainly as I can

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[sfabry]

ill continue to work on un-muddling it then. thank you for your help.