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.

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 YTD when using paramatised date range

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 ROWS

FROM [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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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)
Go to Top of Page

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])
Go to Top of Page
   

- Advertisement -