Can anyone let me know how to calculate YTD data in a crosstab for a relational data source?
is there more than one year in your date selection or is your date limited to the YTD timespan?
If there's more than one year you could use a case when year(date) = 2006 then (value) else (0) end
Oh! sorry I missed that information
Fiscal Year starts from April 1 and ends on March 31. The user has the ability to choose Month and Year at the prompt and he can choose any year from 1985 to 2020.
Suppose the user chooses Feb 2004 in the prompts then the report should display data from April 1, 2003 to Feb 29, 2004. Is it as easy as it sounds????
Thanks.
so:
step 1. Convert the year and month to a valid date if possible
step 2 create a date_from (e.g. ?year? -1 ) and a date_to based on this created valid date
step 2: filter your data on " where date between date_from and date_to "
step3: just sum all data and now you have all data for a ytd.