I will show you my thoughts about an MDX request, these thoughts are wrong because I don't get the expected result, but why?
The select statement is as follow:
SELECT {nballPatsTransfusedOneSpe , nballPatsOneSpe,nballPatsTransfusedOfSameSpes ,nballPatsOfSameSpes ,localRate} ON 0
FROM [BDD PBM]
Only one variable is of interest: localRate.
It is defined as:
member localRate as ([Dim misc].[Speciality Id].&[{754EFA13-AA1B-4E57-AB73-893AF7C52127}], rate)
I think of localRate as a tuple containing 2 explicit informations : one for a Speciality Id, and the other as a measure(even it doesn't belong to [Measures]), the implicit dimensions of the cube being calculated following standard rules(Default member, All members, First member). Both produce a number, as rate produces an integer. And I think that until the end of the request, the defaultmember of the 'Speciality Id' attribute is the Guid shown above(the hexa number), anywhere it could appears in 'Dim misc'(in an attribute hierarchy, or in all the user hierarchies).
rate is defined as:
MEMBER rate AS (existing([Dim misc].[SpePats2].CurrentMember, allPatsOneSpe )).count
It filters the list of patients (allPatsOneSpe) by the speciality given in localRate, and then count the number of rows. Globally, it returns the number of patients in the given speciality. I can't set a request with the 'speciality Id' in rows and the rate in columns as I will have to compute percents with a set of speciality ids, so I must have a list of specialities and a member rate which, given a speciality id, returns a numerator divided by a denominator(only the denominator is given here).
allPatsOneSpe is as follow:
SET allPatsOneSpe AS Descendants([Dim misc].[SpePats].currentmember,2)
And this is surely where the error is located. Do these ideas right?
thank you.
Here is the entire request:
with
MEMBER spename AS [DimTransfusion].[TransfusionPatients].[Speciality Id].&[{7XXXXXX7-AA1B-4E57-AB73-89XXXXXXXX27}].FirstChild.MemberValue
SET spesSame AS filter([Dim misc].[SpePats].[Speciality Id].MEMBERS
,[Dim misc].[SpePats].CurrentMember.FirstChild.MemberValue = spename)--filter
SET spesSameTransfusion AS filter([DimTransfusion].[TransfusionPatients].[Speciality Id].MEMBERS
,[DimTransfusion].[TransfusionPatients].CurrentMember.FirstChild.MemberValue = spename)--filter
SET allPatsOfSameSpes AS Generate(spesSame, Descendants([Dim misc].[SpePats].CurrentMember,2))
SET allPatsTransfusedOfSameSpes AS Generate(spesSameTransfusion , Descendants([DimTransfusion].[TransfusionPatients].CurrentMember,3))
MEMBER nballPatsOfSameSpes AS allPatsOfSameSpes.count
MEMBER nballPatsTransfusedOfSameSpes AS allPatsTransfusedOfSameSpes.count
SET allPatsOneSpe AS Descendants([Dim misc].[SpePats].CurrentMember,2)
SET allPatsTransfusedOneSpe AS Descendants([DimTransfusion].[TransfusionPatients].[Speciality Id].[{XXXXXXX7-AA1B-4E57-AB73-8XXXXXXXX}],2)
MEMBER nballPatsOneSpe AS allPatsOneSpe.count
MEMBER nballPatsTransfusedOneSpe AS allPatsTransfusedOneSpe.count
MEMBER rateOneSpe AS nballPatsTransfusedOneSpe / nballPatsOneSpe
MEMBER [Measures].rate AS --(existing([Dim misc].[SpePats2].CurrentMember, filter(allPatsTransfusedOneSpe ,true ) )).count
-- /
(existing([Dim misc].[SpePats2].CurrentMember, allPatsOneSpe )).count
SET OrderedData AS ORDER(
NONEMPTY(spesSame , [Measures].rate),[Measures].rate, BASC)
MEMBER [Measures].[RowCount] AS COUNT (OrderedData)
MEMBER [Measures].[i0p02] AS ( .02* ( [RowCount] - 1 ) ) + 1
MEMBER [Measures].[i0p02Lo] AS FIX([i0p02]) - 1
MEMBER [Measures].[i0p02Rem] AS ([i0p02] - FIX([i0p02]))
MEMBER [Measures].[n0p02Lo] AS (OrderedData.Item([i0p02Lo]), [Measures].[rate])
MEMBER [Measures].[n0p02Hi] AS (OrderedData.Item([i0p02Lo] + 1), [Measures].[rate])
MEMBER [Measures].[PCT0p02] AS [n0p02Lo] + ( [i0p02Rem] * ( [n0p02Hi] - [n0p02Lo] ))
MEMBER [Measures].[i0p2] AS ( .2* ( [RowCount] - 1 ) ) + 1
MEMBER [Measures].[i0p2Lo] AS FIX([i0p2]) - 1
MEMBER [Measures].[i0p2Rem] AS ([i0p2] - FIX([i0p2]))
MEMBER [Measures].[n0p2Lo] AS (OrderedData.Item([i0p2Lo]), [Measures].[rate])
MEMBER [Measures].[n0p2Hi] AS (OrderedData.Item([i0p2Lo] + 1), [Measures].[rate])
MEMBER [Measures].[PCT0p2] AS [n0p2Lo] + ( [i0p2Rem] * ( [n0p2Hi] - [n0p2Lo] ))
MEMBER [Measures].[i0p5] AS ( .5* ( [RowCount] - 1 ) ) + 1
MEMBER [Measures].[i0p5Lo] AS FIX([i0p5]) - 1
MEMBER [Measures].[i0p5Rem] AS ([i0p5] - FIX([i0p5]))
MEMBER [Measures].[n0p5Lo] AS (OrderedData.Item([i0p5Lo]), [Measures].[rate])
MEMBER [Measures].[n0p5Hi] AS (OrderedData.Item([i0p5Lo] + 1), [Measures].[rate])
MEMBER [Measures].[PCT0p5] AS [n0p5Lo] + ( [i0p5Rem] * ( [n0p5Hi] - [n0p5Lo] ))
--MEMBER [Measures].[PCT0p5] AS Median(OrderedData, [Measures].[Weight])
MEMBER [Measures].[i0p8] AS ( .8* ( [RowCount] - 1 ) ) + 1
MEMBER [Measures].[i0p8Lo] AS FIX([i0p8]) - 1
MEMBER [Measures].[i0p8Rem] AS ([i0p8] - FIX([i0p8]))
MEMBER [Measures].[n0p8Lo] AS (OrderedData.Item([i0p8Lo]), [Measures].[rate])
MEMBER [Measures].[n0p8Hi] AS (OrderedData.Item([i0p8Lo] + 1), [Measures].[rate])
MEMBER [Measures].[PCT0p8] AS [n0p8Lo] + ( [i0p8Rem] * ( [n0p8Hi] - [n0p8Lo] ))
MEMBER [Measures].[i0p98] AS ( .98* ( [RowCount] - 1 ) ) + 1
MEMBER [Measures].[i0p98Lo] AS FIX([i0p98]) - 1
MEMBER [Measures].[i0p98Rem] AS ([i0p98] - FIX([i0p98]))
MEMBER [Measures].[n0p98Lo] AS (OrderedData.Item([i0p98Lo]), [Measures].[rate])
MEMBER [Measures].[n0p98Hi] AS (OrderedData.Item([i0p98Lo] + 1), [Measures].[rate])
MEMBER [Measures].[PCT0p98] AS [n0p98Lo] + ( [i0p98Rem] * ( [n0p98Hi] - [n0p98Lo] ))
member Ecart1 as [Measures].[PCT0p2]-[Measures].[PCT0p02]
member Ecart2 as [Measures].[PCT0p5]-[Measures].[PCT0p2]
member Ecart3 as [Measures].[PCT0p8] - [Measures].[PCT0p5]
member Ecart4 as [Measures].[PCT0p98] -[Measures].[PCT0p8]
member localRate as ([Dim misc].[Speciality Id].&[{77-AA1B-4E57-AB73-27}], [Measures].rate)
SELECT{ PCT0p02, Ecart1, Ecart2, Ecart3, Ecart4 ,PCT0p98 ,localRate } on 0
--SELECT {nballPatsTransfusedOneSpe , nballPatsOneSpe,nballPatsTransfusedOfSameSpes ,nballPatsOfSameSpes ,localRate} ON 0
--SELECT {} ON 0, {OrderedData} ON 1
FROM [BDD PBM]
--[Dim misc].[SpePats].[Hospital Id].&[{274DED7-8605-EA4E741DF116}].&[CH de ABCD].&[{26FBAA2E661}]
--WHERE [Dim misc].[SpePats].&[{7-AA1B-4E57-AB73-89127}]