MDX实现基于Date维度LastRelevantDate属性的x次递归查询方案问询

Yes, you can absolutely build an MDX query to iterate through the LastRelevantDate property x times starting from a specified date member. The key here is using MDX's Generate function to create a recursive set of members, paired with StrToMember to convert the property value into a valid date member reference.

Example Query (Fixed Iteration Count) #

Let's say you want to iterate 5 times starting from [Date].[Date].&[20230101]. Here's a working example:

WITH 
  -- Define your starting date member (update to match your cube's member reference)
  MEMBER [Measures].[StartingDate] AS StrToMember("[Date].[Date].&[20230101]")
  -- Generate a set containing all iterated members
  SET [RelevantDates] AS 
    -- Start with the initial member
    {[Measures].[StartingDate]}
    -- Add x-1 more members (here, 4 additional iterations for total 5)
    + Generate(
        {1:4},
        {StrToMember([RelevantDates].Item(Count([RelevantDates])-1).Properties("LastRelevantDate"))},
        ALL
      )
SELECT 
  -- Return each member's name in separate columns
  [RelevantDates].Item(0).Name AS [Iteration 1],
  [RelevantDates].Item(1).Name AS [Iteration 2],
  [RelevantDates].Item(2).Name AS [Iteration 3],
  [RelevantDates].Item(3).Name AS [Iteration 4],
  [RelevantDates].Item(4).Name AS [Iteration 5]
ON COLUMNS
FROM [YourCubeName] -- Replace with your actual cube name

More Flexible Version (With Parameters) #

If you want to make the starting date and iteration count dynamic, use parameters:

WITH
  -- Define parameters for reusability
  PARAMETERS 
    p_StartMember = "[Date].[Date].&[20230101]",
    p_TotalIterations = 5
  -- Recursively build the set of relevant dates
  SET [RecursiveDateSet] AS 
    Generate(
      {1:p_TotalIterations},
      IIF(
        Item(0) = 1,
        -- First iteration: use the starting member
        {StrToMember(p_StartMember)},
        -- Subsequent iterations: use the previous member's LastRelevantDate
        {StrToMember(
          [RecursiveDateSet].Item(Item(0)-2).Properties("LastRelevantDate")
        )}
      ),
      ALL
    )
SELECT 
  -- List all iteration names (adjust columns based on p_TotalIterations)
  [RecursiveDateSet].Item(0).Name AS [Step 1],
  [RecursiveDateSet].Item(1).Name AS [Step 2],
  [RecursiveDateSet].Item(2).Name AS [Step 3],
  [RecursiveDateSet].Item(3).Name AS [Step 4],
  [RecursiveDateSet].Item(4).Name AS [Step 5]
ON COLUMNS
FROM [YourCubeName]

Key Notes to Avoid Issues #

• Validate Property Format: Ensure the LastRelevantDate property returns the full unique name of the target member (e.g., [Date].[Date].&[20230102]). If it only returns a raw date string (like 20230102), modify the StrToMember call to concatenate the member path:

  StrToMember("[Date].[Date].&[" + [PreviousMember].Properties("LastRelevantDate") + "]")
  

• Handle Edge Cases: If a member's LastRelevantDate is empty or points to an invalid member, add a check to avoid errors:

  IIF(
    NOT IsEmpty([PreviousMember].Properties("LastRelevantDate")),
    StrToMember([PreviousMember].Properties("LastRelevantDate")),
    NULL
  )
  

• Engine Compatibility: Minor syntax tweaks might be needed depending on your MDX engine (e.g., SSAS vs. Mondrian), but the core logic stays the same.

内容的提问来源于stack exchange，提问作者Fabian Gehring