none
Infinite Recursion Detected

    Question

  • The following code returns an error:  Infinite Recursion Detected: The loop of dependencies is:  Avg Visits->Avg Visits

    WITH

     

    MEMBER Measures.[Avg Visits] AS

    {

    AVG(Descendants({[Visit Date].[Visit Date].CurrentMember,[Visit Date].[Visit Date].[Date]},[Measures].[Visits]))}

    MEMBER

     

    Measures.[CountDays] AS

     

    COUNT(Descendants({[Visit Date].[Visit Date].CurrentMember,[Visit Date].[Visit Date].[Date]},[Measures].[Visits]))

    SELECT

     

    { [Measures].[Avg Visits], [Measures].[Visits], Measures.[CountDays] } ON COLUMNS

     

    FROM

     

    [Outpatient]

    WHERE

     

    ( [Visit Date].[Visit Date].[FY].&[FY10],[Facility].[Facility].[Division].&[2013] )

     

    Friday, May 14, 2010 11:12 PM

Answers

  • You've got a bunch of braces that appear to be in the wrong place. Does the following query give you what you expect?

    WITH MEMBER Measures.[Avg Visits] AS
    	AVG(
    		Descendants(
    			[Visit Date].[Visit Date].CurrentMember,
    			[Visit Date].[Visit Date].[Date]
    		),
    		( [Measures].[Visits])
    	)
    MEMBER Measures.[CountDays] AS
    	COUNT(
    		Descendants(
    			[Visit Date].[Visit Date].CurrentMember,
    			[Visit Date].[Visit Date].[Date]
    		)
    	)
    SELECT 	{ 
    		[Measures].[Avg Visits], 
    		[Measures].[Visits], 
    		Measures.[CountDays] 
    	} ON COLUMNS 
    FROM 	[Outpatient] 
    WHERE 	(
    		[Visit Date].[Visit Date].[FY].&[FY10],
    		[Facility].[Facility].[Division].&[2013] 
    	)

    HTH, Martin

     

     

    Saturday, May 15, 2010 1:08 AM
  • Thanks, that works well.  Only one unexpected result is that days with no visits are not part of the average.  Not sure how to have the Average function inculde the days without a visit as a 0 value.

    By default, the NULL value was excluded in the function AVG. If you want the result include the NULL value, then you have to calculate with expression like this SUM()/Count().

    If the standard syntax is used, empty cells can be excluded or included by using the EXCLUDEEMPTY or INCLUDEEMPTY flags

     

    Hope this helps,

    Raymond Li

    Friday, May 21, 2010 5:35 AM

All replies

  • You've got a bunch of braces that appear to be in the wrong place. Does the following query give you what you expect?

    WITH MEMBER Measures.[Avg Visits] AS
    	AVG(
    		Descendants(
    			[Visit Date].[Visit Date].CurrentMember,
    			[Visit Date].[Visit Date].[Date]
    		),
    		( [Measures].[Visits])
    	)
    MEMBER Measures.[CountDays] AS
    	COUNT(
    		Descendants(
    			[Visit Date].[Visit Date].CurrentMember,
    			[Visit Date].[Visit Date].[Date]
    		)
    	)
    SELECT 	{ 
    		[Measures].[Avg Visits], 
    		[Measures].[Visits], 
    		Measures.[CountDays] 
    	} ON COLUMNS 
    FROM 	[Outpatient] 
    WHERE 	(
    		[Visit Date].[Visit Date].[FY].&[FY10],
    		[Facility].[Facility].[Division].&[2013] 
    	)

    HTH, Martin

     

     

    Saturday, May 15, 2010 1:08 AM
  • Thanks, that works well.  Only one unexpected result is that days with no visits are not part of the average.  Not sure how to have the Average function inculde the days without a visit as a 0 value.
    Tuesday, May 18, 2010 9:58 PM
  • Thanks, that works well.  Only one unexpected result is that days with no visits are not part of the average.  Not sure how to have the Average function inculde the days without a visit as a 0 value.

    By default, the NULL value was excluded in the function AVG. If you want the result include the NULL value, then you have to calculate with expression like this SUM()/Count().

    If the standard syntax is used, empty cells can be excluded or included by using the EXCLUDEEMPTY or INCLUDEEMPTY flags

     

    Hope this helps,

    Raymond Li

    Friday, May 21, 2010 5:35 AM