第二个 MDX 查询必须返回第一个 MDX 查询返回的每一行的结果
我遇到了一种情况，正在寻找 MDX 指南。
环境：SQL Server 2008 R2、SSRS 2008 R2
维度：列 DimMainProvider :提供商名称 DimAcademicYear:学年 DimSectorSubjectArea:Estyn DimLearningAim：学习目标
参考事实：列 Learnings:MainProviderKey,AgeBandKey,LearningAimKey,SuccessFlag
我想要的是 1) 对于特定的 MainProvider，按成功率获取前 10 个学习目标并将其显示在 tablix 中 2）对于 tablix 上的每一行，还显示箱形图，该图本质上需要所有 Mainproviders 中特定 LearningAim 的 Min、Max、Quartile1、Quartile3 成功率
我拥有的是 2 个 MDX 查询
a) 获得主要提供商前 10 项活动的一项（简化版） 下面是 MDX 版本）
WITH
SET Top10LearningAimsForSuccessRate
AS
NonEmpty(
TOPCOUNT([ReportedLearningAims],10,[Measures].[SuccessRate]),
[Measures].[SuccessRate]
)
SELECT
{
[Measures].[SuccessRate]
} ON COLUMNS
,NON EMPTY
{
EXISTS(
Top10LearningAimsForSuccessRate
,,"Learnings")
} ON ROWS
FROM
(
SELECT {[Measures].[TerminatedAssessableLASum]
,[Measures].[SuccessfulLASum]} ON COLUMNS
,{(StrToSet("[DimMainProvider].[ProviderName].&[44]",CONSTRAINED))} On ROWS
FROM [FECube]
)
WHERE
( StrToSet("[DimAcademicYear].[AcademicYear].[AcademicYear].[2009/10]",CONSTRAINED),
StrToSet("[DimSectorSubjectArea].[Estyn].&[2]",CONSTRAINED)
)
b) 另一个查询接受 LearningAim 作为参数并执行 统计计算并给我值（简化版 MDX 如下）
WITH
SET ProviderwideLearningAims
AS
ORDER(
NonEmpty( [DimMainProvider].[ProviderName].[ProviderName],
[Measures].[SuccessRate]
)
,[Measures].[SuccessRate],BASC
)
MEMBER [Measures].[MaxValue]
AS
Max(ProviderwideLearningAims,[SuccessRate]),FORMAT_STRING = "Percent"
MEMBER [Measures].[MinValue]
AS
Min(ProviderwideLearningAims,[SuccessRate]),FORMAT_STRING = "Percent"
MEMBER [Measures].[MedianValue]
AS
Median(ProviderwideLearningAims,[SuccessRate]),FORMAT_STRING = "Percent"
Member [Measures].[ProviderCount] As [ProviderwideLearningAims].Count
MEMBER [Measures].[MeanValue]
AS
(SUM(ProviderwideLearningAims,[SuccessRate])/[Measures].[ProviderCount]),FORMAT_STRING = "Percent"
MEMBER [Measures].[LearningAimUniqueName]
AS
[DimLearningAim].[LearningAimReference].CurrentMember.UniqueName
Select
{ [Measures].[LearningAimUniqueName]
,[Measures].[MinValue]
,[Measures].[MaxValue]
,[Measures].[MedianValue]
,[Measures].[MeanValue]
} ON COLUMNS,
{
NonEmpty([DimLearningAim].[LearningAimReference].[LearningAimReference],ProviderwideLearningAims)
} ON ROWS
FROM
(
SELECT
StrToSet("[DimLearningAim].[LearningAimReference].&[50024991]",CONSTRAINED) ON COLUMNS
FROM [FECube]
)
WHERE
( StrToSet("[DimAcademicYear].[AcademicYear].[AcademicYear].[2009/10]",CONSTRAINED),
StrToSet("[DimSectorSubjectArea].[Estyn].&[2]",CONSTRAINED)
)
我最初的想法是为收到的每个学习目标触发第二个查询 来自第一个查询，但我无法在 SSRS 数据集中实现这一点 模型。 所以现在我回到了 MDX 级别并希望以某种方式合并这些 两个。
I have a Situation where I am looking for MDX Guidance.
Environment: SQL Server 2008 R2,SSRS 2008 R2
Dimension:Columns
DimMainProvider :ProviderName
DimAcademicYear:AcademicYear
DimSectorSubjectArea:Estyn
DimLearningAim:LearningAimReference
Fact:Columns
Learnings:MainProviderKey,AgeBandKey,LearningAimKey,SuccessFlag
What I want is
1) For a particular MainProvider Get Top 10 LearningAims by Success Rate and show it in tablix
2) For a each Row on tablix also show Box plot graph which essentially needs Min,Max,Quartile1,Quartile3 success rates of that particular LearningAim across all Mainproviders
What I have is 2 MDX queries
a) one which gets top 10 Activities for a Main Provider (Simplified
Version of MDX below)
WITH
SET Top10LearningAimsForSuccessRate
AS
NonEmpty(
TOPCOUNT([ReportedLearningAims],10,[Measures].[SuccessRate]),
[Measures].[SuccessRate]
)
SELECT
{
[Measures].[SuccessRate]
} ON COLUMNS
,NON EMPTY
{
EXISTS(
Top10LearningAimsForSuccessRate
,,"Learnings")
} ON ROWS
FROM
(
SELECT {[Measures].[TerminatedAssessableLASum]
,[Measures].[SuccessfulLASum]} ON COLUMNS
,{(StrToSet("[DimMainProvider].[ProviderName].&[44]",CONSTRAINED))} On ROWS
FROM [FECube]
)
WHERE
( StrToSet("[DimAcademicYear].[AcademicYear].[AcademicYear].[2009/10]",CONSTRAINED),
StrToSet("[DimSectorSubjectArea].[Estyn].&[2]",CONSTRAINED)
)
b) Another query which accepts LearningAim as a Parameter and does
Statistical Calculations and gives me values (Simplified Version of
MDX below)
WITH
SET ProviderwideLearningAims
AS
ORDER(
NonEmpty( [DimMainProvider].[ProviderName].[ProviderName],
[Measures].[SuccessRate]
)
,[Measures].[SuccessRate],BASC
)
MEMBER [Measures].[MaxValue]
AS
Max(ProviderwideLearningAims,[SuccessRate]),FORMAT_STRING = "Percent"
MEMBER [Measures].[MinValue]
AS
Min(ProviderwideLearningAims,[SuccessRate]),FORMAT_STRING = "Percent"
MEMBER [Measures].[MedianValue]
AS
Median(ProviderwideLearningAims,[SuccessRate]),FORMAT_STRING = "Percent"
Member [Measures].[ProviderCount] As [ProviderwideLearningAims].Count
MEMBER [Measures].[MeanValue]
AS
(SUM(ProviderwideLearningAims,[SuccessRate])/[Measures].[ProviderCount]),FORMAT_STRING = "Percent"
MEMBER [Measures].[LearningAimUniqueName]
AS
[DimLearningAim].[LearningAimReference].CurrentMember.UniqueName
Select
{ [Measures].[LearningAimUniqueName]
,[Measures].[MinValue]
,[Measures].[MaxValue]
,[Measures].[MedianValue]
,[Measures].[MeanValue]
} ON COLUMNS,
{
NonEmpty([DimLearningAim].[LearningAimReference].[LearningAimReference],ProviderwideLearningAims)
} ON ROWS
FROM
(
SELECT
StrToSet("[DimLearningAim].[LearningAimReference].&[50024991]",CONSTRAINED) ON COLUMNS
FROM [FECube]
)
WHERE
( StrToSet("[DimAcademicYear].[AcademicYear].[AcademicYear].[2009/10]",CONSTRAINED),
StrToSet("[DimSectorSubjectArea].[Estyn].&[2]",CONSTRAINED)
)
My original Idea is to fire 2nd query for each Learning Aim received
from 1st query,but I am not able to implement this in SSRS Dataset
Model.
So Now I am back at MDX level and want somehow to merge these
two.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
以下是我在 MSDN 论坛上 Deepak Puri 的帮助下修复该问题的方法
Here is how I fixed it with help from Deepak Puri on MSDN forums