Second MDX Query has to return result for each row returned by 1st MDX Query

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].[Provider开发者_运维百科Name].&[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.

---

Here is how I fixed it with help from Deepak Puri on MSDN forums

With Set [Top10LearningAims] as TOPCOUNT(

Filter(([ReportedLearningAims]*[DimMainProvider].[ProviderName].MEMBERS),[DimMainProvider].[ProviderName].CURRENTMEMBER IS STRTOMEMBER("[DimMainProvider].[ProviderName].&[44]") ) ,10,[Measures].[TerminatedAssessableLASum] )

MEMBER [Measures].[MaxValue] AS Max(NonEmpty([DimMainProvider].[ProviderName].[ProviderName], [Measures].[SuccessRate]), [Measures].[SuccessRate]),FORMAT_STRING = "Percent" MEMBER [Measures].[MinValue] AS Min(NonEmpty([DimMainProvider].[ProviderName].[ProviderName], [Measures].[SuccessRate]), [Measures].[SuccessRate]),FORMAT_STRING = "Percent" MEMBER [Measures].[MedianValue] AS Median(NonEmpty([DimMainProvider].[ProviderName].[ProviderName], [Measures].[SuccessRate]), [Measures].[SuccessRate]), FORMAT_STRING = "Percent"

MEMBER [Measures].[MeanValue] AS Avg(NonEmpty([DimMainProvider].[ProviderName].[ProviderName], [Measures].[SuccessRate]), [Measures].[SuccessRate]), FORMAT_STRING = "Percent"

Member [Measures].[ProviderCount] as Count(NonEmpty([DimMainProvider].[ProviderName].[ProviderName], [Measures].[SuccessRate])) Member [Measures].[PercentileInt25] as Int((([Measures].[ProviderCount] - 1) * 25) / 100) Member [Measures].[PercentileFrac25] as (([Measures].[ProviderCount] - 1) * 25) / 100 - [Measures].[PercentileInt25] Member [Measures].[PercentileLo25] as ([Measures].[SuccessRate], Order(NonEmpty([DimMainProvider].[ProviderName].[ProviderName], [Measures].[SuccessRate]), [Measures].[SuccessRate], BASC).Item([Measures].[PercentileInt25]).Item(0)), FORMAT_STRING = "Percent" Member [Measures].[PercentileHi25] as ([Measures].[SuccessRate], Order(NonEmpty([DimMainProvider].[ProviderName].[ProviderName], [Measures].[SuccessRate]), [Measures].[SuccessRate], BASC).Item([Measures].[PercentileInt25] + 1).Item(0)), FORMAT_STRING = "Percent" Member [Measures].[Percentile25Value] as ([Measures].[PercentileLo25] * (1 - [Measures].[PercentileFrac25])) + ([Measures].[PercentileHi25] * [Measures].[PercentileFrac25]), FORMAT_STRING = "Percent" Member [Measures].[PercentileInt75] as Int((([Measures].[ProviderCount] - 1) * 75) / 100) Member [Measures].[PercentileFrac75] as (([Measures].[ProviderCount] - 1) * 75) / 100 - [Measures].[PercentileInt75] Member [Measures].[PercentileLo75] as ([Measures].[SuccessRate], Order(NonEmpty([DimMainProvider].[ProviderName].[ProviderName], [Measures].[SuccessRate]), [Measures].[SuccessRate], BASC).Item([Measures].[PercentileInt75]).Item(0)), FORMAT_STRING = "Percent" Member [Measures].[PercentileHi75] as ([Measures].[SuccessRate], Order(NonEmpty([DimMainProvider].[ProviderName].[ProviderName], [Measures].[SuccessRate]), [Measures].[SuccessRate], BASC).Item([Measures].[PercentileInt75] + 1).Item(0)), FORMAT_STRING = "Percent" Member [Measures].[Percentile75Value] as ([Measures].[PercentileLo75] * (1 - [Measures].[PercentileFrac75])) + ([Measures].[PercentileHi75] * [Measures].[PercentileFrac75]), FORMAT_STRING = "Percent"

select { [Measures].[TerminatedAssessableLASum] ,[Measures].[SuccessfulLASum] ,[Measures].[SuccessRate] ,[Measures].[SectorTerminatedAssessableLASum] ,[Measures].[SectorSuccessfulLASum] ,[Measures].[SectorSuccessRate] ,[Measures].[ProviderCount] ,[Measures].[MinValue]
,[Measures].[MaxValue] ,[Measures].[MeanValue]
,[Measures].[Percentile25Value] ,[Measures].[MedianValue]
,[Measures].[Percentile75Value]} on COLUMNS, EXISTS(NonEmpty(([DimLearningAim].[LearningAimReference].[LearningAimReference],[DimLearningAim].[LearningAimTitle].[LearningAimTitle]),([Top10LearningAims],[Measures].[SuccessRate]))*NonEmpty([DimMainProvider].[ProviderName].[ProviderName], [Measures].[SuccessRate]),,"Learnings") DIMENSION PROPERTIES MEMBER_CAPTION,MEMBER_UNIQUE_NAME ON ROWS from FECube WHERE
( StrToSet("[DimAcademicYear].[AcademicYear].[AcademicYear].[2009/10]",CONSTRAINED), StrToSet("[DimLearnerAgeBand].[AgeBand].[All]",CONSTRAINED), StrToSet("[DimLearningCourseLength].[CourseLength].[All]",CONSTRAINED), StrToSet("[DimLearnerEthnicity].[Ethnicity].[All]",CONSTRAINED), StrToSet("[DimLearnerGender].[Gender].[All]",CONSTRAINED), StrToSet("[DimDeprivationDecile].[Decile].[All]",CONSTRAINED), StrToSet("[DimSectorSubjectArea].[Estyn].&[2]",CONSTRAINED), StrToSet("[DimLearningActivityLevel].[ActivityLevel].[All]",CONSTRAINED), StrToSet("[DimLearningActivityType].[ActivityType].[All]",CONSTRAINED) ) CELL PROPERTIES VALUE ,BACK_COLOR ,FORE_COLOR ,FORMATTED_VALUE ,FORMAT_STRING ,FONT_NAME ,FONT_SIZE ,FONT_FLAGS