DatePart Issue / Relational Source

[kado]

Cognoisers,

I am trying to sum a measure (and ideally multiple measures) across 2009, 2008, and then bucket everything less than 2008 (in OTHER). I have the full-date to work with as an attribute of the dimension called DIVISION and can get it to build for all YEARS in a generic report. Using a dateapart({year}, date) I can get All Years as columns and the DIVISION as rows.

My first challenge is making it dynamic so the user picks the year and sends it through. So I have 1 data item (also datepart({year}, date)) ... which filters the report just fine for that one year. But then when I try to subtract one from this year for the second data item it works for the header but all the data is exactly the same as the selected year. I have tried having this as a DETAIL or SUMMARY filter but still have this issue. And I also tried taking it out of the Filter and only using the Data Item to reference the parameter ?Year? .... This results in giving me the right year but the measure aggregated for all of time (not filter per year).

Any thoughts? Perhaps I could break it out into multiple queries and multiple crosstabs but that could get sloppy.


-THANKS-

[kado]

Multiple queries into 1 query will work ... I thought that multiple data items in one query (acting as sub-selects) could handle it but for some reason report studio doesn't like that ....

thanks  :D