April 6, 2008 at 1:54 pm
Hi,
I have 3 Dimensions and a FactTable in my cube, How do I perform calculations between the two dates to produce
the report below.
DimStartDate
DimEndDate
DimArea
FactDataCount
sample report:
Area 5days 10days 30days
Miami 4 2 1
Memphis 3 3 2
April 21, 2008 at 7:18 am
Did you have any success with this I am having a similar problem?
April 21, 2008 at 9:49 am
Hey,
I basically added a named query to my DSV with the following sql and created a relationship between my StartedDate (PK=StartedDateKey) and TurnAroundTime(FK=StartedDateKey)
SELECT CASE WHEN (DATEDIFF(d, StartedDate.StartedDate, EndedDate.EndedDate) between 0 and 5) THEN '0-5'
WHEN (DATEDIFF(d, StartedDate.StartedDate, EndedDate.EndedDate) between 6 and 10) THEN '6-10'
WHEN (DATEDIFF(d, StartedDate.StartedDate, EndedDate.EndedDate) >= 90) THEN '> 90' ELSE 'UNKNOWN' END AS 'TurnAroundTime',
StartedDate.StartedDateKey, EndedDate.EndedDateKey
FROM StartedDate INNER JOIN
FactCaseData ON StartedDate.StartedDateKey = FactCaseData.StartedDateKey INNER JOIN
EndedDate ON FactCaseData.EndedDateKey = EndedDate.EndedDateKey
I then added this as a dimension to my cube.
April 21, 2008 at 12:12 pm
if you want to create intervals based on a fixed number of days and you're using a matrix then you can modify your query, you could just add another column like so:
[font="Courier New"]select ..., (1 + datediff(day, [start_date], [end_date]) / 5) as dayGroup
from ...[/font]
and use [dayGroup] for the matrix and the following expression for the display value:
[font="Courier New"]Fields!dayGroup & ' days'[/font]
alternatively, you could define the matrix using this expression:
[font="Courier New"]datediff(day, Fields!start_date.Value, Fields!end_date.Value) [/font]
and use a similar expression for the display value:
[font="Courier New"](1 + datediff(day, Fields!start_date.Value, Fields!end_date.Value)) & ' days'[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply