Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
asumner
Starting Member
3 Posts |
 Posted - 2010-09-22 : 17:35:41
|
| Can anyone tell me how to fix the MDX below so that it will calculate a YTD amount when filtering on a parameter driven (ssrs) date range?The mdx statment works perfectly when selecting on a single date but when I filter on a date range I get the #Error result for the ytd measure.The only "fix" I have come up with is hard coding the date in the measure eg "SUM(YTD([Time].[Calendar Hierarchy].&[2010-09-05T00:00:00]),[Measures].[ActualHours])"but that is no good as the date range is parameter driven.If I try to use this "SUM(YTD(STRTOMEMBER(@FromTimeDate, CONSTRAINED)), [Measures].[ActualHours])" I get the error "the fromdatetime parameter could not be resolved because it was referenced in an inner sub expression"Current MDX===========WITH MEMBER [Measures].[YTD] AS SUM(YTD([Time].[Calendar Hierarchy].CurrentMember),[Measures].[ActualHours])SELECT {[Measures].[ActualHours],[Measures].[YTD] } ON COLUMNS, [OrganizationStaffView].[Organization Hierarchy].[Staff Name] ON ROWSFROM [MiniManager]WHERE ( [OrganizationStaffView].[Zone].&[Business Technology Services] ,[OrganizationStaffView].[Team].&[ATM & Windows Development Team] ,[Time].[Calendar Hierarchy].&[2010-09-05T00:00:00] : [Time].[Calendar Hierarchy].&[2010-08-29T00:00:00])Thanks Andy |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-09-23 : 13:39:59
|
| have a look at STRTOMEMBER function. seems like thats what you want------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
asumner
Starting Member
3 Posts |
Posted - 2010-09-23 : 17:40:43
|
| Thanks, but I have already tried that. SSRS threw a wobbly when using parameter in calculated member.I suspect that when I used a single date the YTD function was happy because it had a member to work from, but with the date range it has a set and doesn't know what to do. If this is true then: can somebody tell me how to convert the date range to a single member? (or let me know if I am completely worng) |
|
|
|
asumner
Starting Member
3 Posts |
Posted - 2010-09-26 : 15:05:46
|
| Solved this myself: grab todays date, convert to a member in the date hierarchy and use the financial year level to perform the PERIODSTODATE calculation over.MEMBER [Measures].[Now] AS "[Time].[Fiscal Hierarchy].[Date].&[" + FORMAT(Now(), "yyyy-MM-dd") + "T00:00:00]"MEMBER [Measures].[BillableHours_FYTD] AS SUM(PERIODSTODATE([Time].[Fiscal Hierarchy].[Fiscal Year], strtomember([Measures].[Now], constrained)), [Measures].[BillableHours]) |
|
|
|
|
|
|
|
|
YTD when using paramatised date range