Hi,
I am new to MDX, so apologies if I am missing anything obvious and any help is greatly appreciated.
I have built a cube designed to display information on patient appointments. My only two measures are [Appointment Minutes] and [Appointment Count]. My clients want information presented in the following format:
Measures [Current Time Period] [Comparative Time Period] [% Change]
Hours Booked a hours b hours c percent
Patients Seen d patients e patients f percent
I have produced the first line of data with the following query:
WITH
MEMBER [Start Date].[Month Hierarchy].[Current] AS
'Aggregate(NonEmpty({[Start Date].[Month Hierarchy].[Date].[2006-10-11 00:00:00]:
[Start Date].[Month Hierarchy].[Date].[2006-11-11 00:00:00]}))'
MEMBER [Start Date].[Month Hierarchy].[Comparison] AS
'Aggregate(NonEmpty({ParallelPeriod([Start Date].[Month Hierarchy].[Year], 1,
[Start Date].[Month Hierarchy].[Date].[2006-10-11 00:00:00]):
ParallelPeriod([Start Date].[Month Hierarchy].[Year], 1,
[Start Date].[Month Hierarchy].[Date].[2006-11-11 00:00:00])}))'
MEMBER [Start Date].[Month Hierarchy].[% Change]
AS '([Current] - [Comparison])/[Comparison]', FORMAT_STRING = '#0.0#%', SOLVE_ORDER = 3
MEMBER [Measures].[Booked Hours] AS
'Measures].[Appointment Minutes])/60', SOLVE_ORDER = 2
SELECT {[Current], [Comparison], [% Change], [Group Average], [Benchmark %]} ON 0,
{[Booked Hours]} ON 1
FROM [Diary]
WHERE [Branch].[Branch Name].[Head Office]
This works fine. I run into trouble, though, when trying to count the patients properly in the current and comparison time columns. There is a [Patient] dimension with an [ID] attribute, and what I really want is a distinct count of how many patients have one or more appointments booked in each time column. So far, all my attempts with Distinct(), Count(), Filter(), NonEmpty() and NonEmptyCrossJoin have come to nothing. If anyone can help here, then I would be really grateful.
If you're using AS 2005, and there is a [PatientID] foreign key in the fact table, you could create a "distinct count" measure like [Patient Count] on the [PatientID] field.|||Thanks for replying, Deepak.
That is what I have been trying to do, but I must be getting the MDX wrong.
MEMBER [No Of Patients] AS 'DISTINCTCOUNT(Filter([Patient].[Public ID], [Measures].[Appointment Count] > 0))' just returns an error. Changing DistinctCount to Count just returns 1, when I know that 12 patients should be returned by the example.
'COUNT(Filter(NonEmpty({[Patient].[Public ID].CHILDREN}), [Measures].[Appointment Count] > 0))' returns a number (the wrong one) and takes a long time to run.
Any suggestions on an expression I could use that would work?
Many thanks,
Ed.
Edit: 'COUNT(Filter(NonEmpty({[Patient].[Public ID].CHILDREN}), [Measures].[Appointment Count] > 0))' does in fact return the right result (apologies - there was an error in the test code), but takes 1 minute, 40 seconds to run. My clients are never going to accept that. I have cut dimensions and attributes down as far as I can. Can anyone suggest a way of querying the count more efficiently?
Any help greatly appreciated,
Ed.
