I submitted this issue to the reporting services thread, but I'm beginning to think I may have to do something in SSAS:
I want to enable my users to select 2 random month end dates in a report parameter. After selecting these dates, I want to be able to take the corresponding financial values, be able to line them up next to each other in a report, and calculate the difference. Obviously in a matrix I can line the values up, but I am then not able to calc a difference. I do have a time hierarchy that allows me to do this across dates at the same level of granularity, i.e. year to year, qtr to qtr, etc., but if my users want to compare February month end to November month end, it's a problem. I am more than willing to add calculated members to the cube to get this done, I just haven't figured out the MDX for it.
Thanks in Advance...
I think this can be done without any calculated members, simply by using the following query:
SELECT
{Measures.ImportantFinancialValue1, Measures.ImportantFinancialValue2, ...} ON COLUMNS
, {Time.Date1, Time.Date2} ON ROWS
FROM Cube
HTH,
Mosha (http://www.mosha.com/msolap)
|||
Mosha-
Thanks for your quick response. I am using SSAS on top of SSRS, and am trying to avoid writing MDX as these reports may get very involved. Can I replicate this in a calc'd member, can it be dynamic enough to accept values at runtime?
|||I am supposed to find out the total number of open account between February - April and i am having difficulties. Below is a working statement for SQL which i am supposed to do in MDX for my Cube:
SELECT COUNT(*) AS Expr1
FROM ConsumerAccount
WHERE (DATEPART(yyyy, AccountOpenedDate) = '2007')
AND (DATEPART(mm, AccountOpenedDate) IN (02, 03, 04))
I have a cube called Batch process, column AccountOpenedDate and CrearedOnDate, how do i do it
