mdx/analysis/reporting service for date calculation

[j-in-nz]

Hi,

I have a cube with the following
DimStartDate
DimEndDate
DimArea
FactDataCount

I need to output the following report

Area 5days 10days 30days
Miami 4 2 1
Memphis 3 3 2

1)Where do I do the dateDiff calculation, at report or cube level?
2)How do I work out which dates belong to the above groups? I'm assuming i have to check if the dateDiff lies between those numbers?

thanks

[yadgor]

let me know if you get this solved, i have a similar issue.....i'll post here if i find something as well...

[Island1]

Here is some general guidance:

Question 1: You can put it in either, but if you create a calculated member, etc. in the AS layer, you’ll do yourself a favor: It will mean a single point of maintenance (versus doing the calculation in every report under consideration), as well as a performance benefit, in most cases, in running the reports. I’ve written myriad articles where I demonstrate the logic (In both my Analysis Services and Reporting Services columns – see links below).

Question 2: If you mean “how I differentiate, say, in the Date dim, which date is a Start versus an End date, you can use role playing in 2005. If you don’t mean that, or if you’re not on 2k5, please get back to me and I can possibly suggest alternative approaches.

Good Luck, and let me know if you need further …