Question

我试图用一个计算出来的成员来建立一个参数查询系统。

我为"硬编码"的司法罪状创造了计算成员并且我所设定的参数并不影响每个组的总数

但是,我需要列出每个总计数,并列出一个工作参数。如果选定的司法类型没有值,则应该显示零计数。

我敢肯定,有一个容易的方法,但对于MDX/SSRS来说是相当新的。非常感激任何帮助!

以下是查询 :

WITH 

Member  [Measures].[Jud] as
        (
        [Dim Foreclosure Loan].[Judicial Flag].[1],
        [Dim Foreclosure Loan].[FCL Stage].&[Sale Held],
        [Measures].[Loan Count]
        )
Member  [Measures].[Non-Jud] as
        (
        [Dim Foreclosure Loan].[Judicial Flag].[0],
        [Dim Foreclosure Loan].[FCL Stage].&[Sale Held],
        [Measures].[Loan Count]
        )
Member  [Measures].[Total] as
        (
        [Dim Foreclosure Loan].[Judicial Flag],
        [Dim Foreclosure Loan].[FCL Stage].&[Sale Held],
        [Measures].[Loan Count]
        )
--------------------------------    Query Begins    --------------------------------
SELECT NON EMPTY
        {
        ([Dim Date].[Calendar].[Day].Members)
        } ON ROWS,
        NON EMPTY 
        ({
        ([Measures].[Jud]),
        ([Measures].[Non-Jud]),
        ([Measures].[Total])
        }) ON COLUMNS
        FROM ( SELECT ( STRTOSET(@DimForeclosureLoanJudicialFlag, CONSTRAINED) ) ON COLUMNS
        FROM [Foreclosure])
        WHERE 
        (IIF( STRTOSET(@DimForeclosureLoanJudicialFlag, CONSTRAINED).Count = 1,
        STRTOSET(@DimForeclosureLoanJudicialFlag, CONSTRAINED),
        [Dim Foreclosure Loan].[Judicial Flag].currentmember)
        )CELL PROPERTIES VALUE

<强度 > 更新 :需要帮助提供备注;见下文评论。

Answer 1

我不完全清楚你在问什么,但如果我要重复一遍,我想这就是你试图做的。我想你想在栏目上看到贾德、非朱德和达图尔。我想你正在试图使用参数来选择特定的司法旗帜。不清楚的是,参数值是否限制在1和2,正如你为衡量标准所显示的那样,或者,如果有一个等级,那里有1和2个孩子,我会假设第一个等级,因为它很容易解释,我们可以从那里走。

您需要为司法国旗 1 和 2 设置您的措施, 以考虑 < code> WHERE < /code > 条款中的内容。现在, 它们是硬码, 并且让 SSAS 忽略 < code> WHERE < /code > 条款中的内容。您需要有条件地使用它, 在测量定义中加入一个 < code> IIIF < /code > 的语句。您应该返回一个 < code> NULLL 值, 作为假条件, 以便SSAS 能够最佳地执行查询, 然后使用格式_string 属性填充零, 取代 < code> NULLL (这比在虚假状态中使用 0 更好) 。然而, 您的 " code> NON EMPTY 关键词会在列中取消未包含在 < code> WHERE < /code > 条款中的司法旗帜, 所以您需要删除它。保持在行中, 您只显示对Jud 或非 Jud 的日。

根据我对你想做什么的猜测尝试查询

WITH 
  MEMBER [Measures].[Jud] AS 
  iif(
        [Dim Foreclosure Loan].[Judicial Flag].CurrentMember is 
            [Dim Foreclosure Loan].[Judicial Flag].[1], 
            (
              [Dim Foreclosure Loan].[Judicial Flag].[1]
             ,[Dim Foreclosure Loan].[FCL Stage].&[Sale Held]
             ,[Measures].[Loan Count]
            ),
            NULL), format_string="#;#;0;0"
  MEMBER [Measures].[Non-Jud] AS 
  iif(
        [Dim Foreclosure Loan].[Judicial Flag].CurrentMember is 
            [Dim Foreclosure Loan].[Judicial Flag].[0], 
            (
              [Dim Foreclosure Loan].[Judicial Flag].[0]
             ,[Dim Foreclosure Loan].[FCL Stage].&[Sale Held]
             ,[Measures].[Loan Count]
            ),
            NULL), format_string="#;#;0;0" 
  MEMBER [Measures].[Total] AS 
    (
      [Dim Foreclosure Loan].[Judicial Flag]
     ,[Dim Foreclosure Loan].[FCL Stage].&[Sale Held]
     ,[Measures].[Loan Count]
    ) 
SELECT 
  NON EMPTY 
    {
      [Dim Date].[Calendar].[Day].MEMBERS
    } ON ROWS
 , 
    {
      [Measures].[Jud]
     ,[Measures].[Non-Jud]
     ,[Measures].[Total]
    } ON COLUMNS
FROM 
(
  SELECT 
    StrToSet
    (@DimForeclosureLoanJudicialFlag
     ,CONSTRAINED
    ) ON COLUMNS
  FROM [Foreclosure]
)
WHERE 
  IIF
  (
      StrToSet
      (@DimForeclosureLoanJudicialFlag
       ,CONSTRAINED
      ).Count
    = 1
   ,StrToSet
    (@DimForeclosureLoanJudicialFlag
     ,CONSTRAINED
    )
   ,[Dim Foreclosure Loan].[Judicial Flag].CurrentMember
  )
CELL PROPERTIES VALUE, FORMATTED_VALUE;

友情链接