Crystal Formula Crystal 11 1

[kezdawg]

I want a formula that can take a Date (Year) parameter, and look through my data set and find data that is minus one year of the parameter. However if the minus one year data is not available it needs to keep on looking back until it finds a year with some data.

ie user enter 2008.

I then need to find some 2007 data. If 2007 data doesnt exist , its needs to look for 2006 , 2005 etc until it finds some existing data.

I am using Crystal Version 11

 

[lbass]

Would you be limiting the report to one account, for example, where the entire report would be based on the same previous year, or will there be groups of accounts (or whatever) where each account might have a different previous year?

-LB

 

[IanWaterman]

Hi

YOu need to be more specific as to what you want to see in report.
A possible solution is to bring back all data, prior to you dateparam- 1year.

Then grouping data as required, make your last group date, and then use this group footer to display data rather than details.
In group suppression, only show date group footer if date is max of the preceding group.

Ian

 

[kezdawg]

lbass and Ian;

It will always be the one combination of the two years values per the entire report and will not change per group or account.


I was also wrong in saying that it will be current year and current year minus one. If the user runs a report it will first look to grab the same year and then search backwards until it finds the next most current set of data.

To briefly outline report. User enters a year (ie 2007) for the data they want to see. My report will then pick up all the relevant data for 2007 from the main set of data. In addition to that it then needs to hit another data set and find all the relevant data it has for 2007 (or 2006,2005 etc if 2007 doesnt exist) which is then also incorporated into the report.

This report is a medical/demographic report. User enters year 2007 and wants to see the 2007 medical information. But this information has to be quantified against some population data. However the nature of this population data is that it doesnt exist for current year (and sometimes not for the previous year either). However for most historical data scenarios the medical and the population data will be the same.

So scenarios could be;

medical information year(x) vs demographic data year (x)or
medical information year(x) vs demographic data year (x-1)or
medical information year(x) vs demographic data year (x-2)
etc etc

Ian; If possible i want to avoid bringing in all the data as i am already dealing with a lot of data.

 

[kezdawg]

thinking about it Ian's suggestion seems the only feasible solution to me.

I could use a subreport of the demographic which takes the date para from the original report and use Ian's trick and then pass back the demographic date into the main report.