BB Gap Report的计算方法以及有用的MDX查询表达式（对于Property,Aspect等不同Dimension的过滤）

	H&S
	Av %	Min %	Level*
Process	0	0
Implementation	0	0
	E
	Av	Min	Level
Process	0	0
Implementation	0	0
	Q
	Av	Min	Level
Process	0	0
Implementation	0	0
	Total
	Av	Min	Level
Process	0	0
Implementation	0	0

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

   

Minimum/Average Score %：

 

{ [ (points acheived in element 1　/ points available　in element 2) + ....+

　(points achieved in element n / points available in element n) ] / number of

elements } x 100

 

对于每个Element 的Percentage相加，然后除以总的Element个数。

计算方法和OptionalElementScoringRule的minimumAverageScorePercentage：最小平均百分比一样。
对应的MDX如下：

Protocol Level1针对的是Element级别，Level2针对的是Sub-Element, Level3针对的是Question.Level4为每个Question分给Aspect的分数。

 

   

with

 

 

member [Measures].[Average Score] as Avg

([Protocol].[r Parent].[Level1], Measures.[% Score]*100)

member

 

 

[Measures].[Min Score] as Min

([Protocol].[r Parent].[Level1], Measures.[% Score]*100)

 

 

member [Measures].[Protocol Code] as [Protocol].[r Parent]. CurrentMember . Properties ( "m Code"

)

 

 

select

{[Measures].[% Score],[Measures].[Score],[Measures].[Max Score], [Measures].[Available Score],

[Measures].[Protocol Code],Measures.[Average Score],Measures.[Min Score]}

 

on columns

,

 

 

--{[Protocol].[r Parent].[All], [Protocol].[r Parent].[Level1]} on rows,

{[Aspect].[m Name].[All],[Aspect].[m Name].[All]}

 

on rows

 

 

from ( select

( { [Question Property].[Question Property].[Implement] } )

 

 

on columns from

[Scoring Balfour Beatty Group Audit Protocol 2009-10 110909 Rev 15b PR200201])

 

 

where

[Assessment].[r Parent].&[{8AD8D2ED-A4A8-4BED-8F01-1FC7316A0C3D}]

 

 

如果要得到所有Scoped问题的平均分，则用下列表达是。区别在于Level3还是Level1.

with

 

 

member [Measures].[Average Score] as Avg([Protocol].[r Parent].[Level3], Measures.[Score])

 

member [Measures].[Min Score] as Min([Protocol].[r Parent].[Level1], Measures.[Score])

 

member [Measures].[Protocol Code] as [Protocol].[r Parent].CurrentMember.Properties( "m Code" )

 

select {[Measures].[% Score],[Measures].[Score],[Measures].[Max Score], [Measures].[Available Score], [Measures].[Protocol Code],Measures.[Average Score],Measures.[Min Score]} on columns,

{[Protocol Data].[r Parent].[Level1]}

on rows

 

-- {[Aspect].[m Name].[All],[Aspect].[m Name].[m Name]} on rows

 

from ( select ( { [Question Property].[Question Property].[Question Property].[Implement] } ) on columns

 

from [Scoring Balfour Beatty Group Audit Protocol 2009-10 110909 Rev 15b PR200201]) where [Assessment].[r Parent].&[{6bf13047-9d62-4e37-a47f-f25d9341e6ed}]

 

 

如果要得到所有问题的平均分数（包括Scope以及非Scope的情况）

则用下列表达是。区别在于CoalesceEmpty(Measures.[Score],0)。如果非Scope的Question,则Measures.[Score]应该为Null.

with

 

member [Measures].[Average Score] as Avg([Protocol].[r Parent].[Level3], CoalesceEmpty

(Measures.[Score],0))

member

 

[Measures].[Min Score] as Min([Protocol].[r Parent].[Level1], CoalesceEmpty(Measures.[Score],0))

member

 

[Measures].[Protocol Code] as [Protocol].[r Parent].CurrentMember.Properties( "m Code" )

select

 

{[Measures].[% Score],[Measures].[Score],[Measures].[Max Score], [Measures].[Available Score], [Measures].[Protocol Code],Measures.[Average Score],Measures.[Min Score]} on columns,

-- {[Protocol].[r Parent].[All], [Protocol].[r Parent].[Level1]} on rows

{[Aspect].[m Name].[All], [Aspect].[m Name].[m Name]}

on rows

 

from ( select ( { [Question Property].[Question Property].[Process] } ) on columns

 

from [Scoring Balfour Beatty Group Audit Protocol 2009-10 110909 Rev 15b PR200201])

 

where [Assessment].[r Parent].&[{E93AFB4A-79D2-47D2-92FB-250CC42808F3}]