September 3, 2009 at 3:05 pm
We have the following tables:
CREATE TABLE [DimDoctor](
[DoctorNum] [int] IDENTITY(1,1) NOT NULL,
[DoctorName] [varchar](100) NOT NULL,
[Specialty] [varchar](50) NOT NULL
CONSTRAINT [PK_DimResource] PRIMARY KEY CLUSTERED
(
[DoctorNum] ASC
)
CREATE TABLE [Appointment](
[LocationNumber] [int] NOT NULL,
[DoctorNum] [int] NOT NULL,
[patient] varchar(100) NOT NULL,
[ApptDate] [datetime] NOT NULL,
[ApptTime] [char](4) NOT NULL,
[AppointmentStatus] [varchar](20) NOT NULL,
[ConfirmationStatus] [varchar](20) NOT NULL
)
CONSTRAINT [PK_Appointment] PRIMARY KEY CLUSTERED
(
[LocationNumber] ASC,
[DoctorNum] ASC,
[ApptDate] ASC,
[ApptTime] ASC
)
CREATE TABLE [DimDate](
[PK_Date] [datetime] NOT NULL,
[Date_Name] [nvarchar](50) NULL,
[Year] [datetime] NULL,
[Year_Name] [nvarchar](50) NULL,
[Quarter] [datetime] NULL,
[Quarter_Name] [nvarchar](50) NULL,
[Month] [datetime] NULL,
[Month_Name] [nvarchar](50) NULL,
[Week] [datetime] NULL,
[Week_Name] [nvarchar](50) NULL,
[Day_Of_Year] [int] NULL,
[Day_Of_Year_Name] [nvarchar](50) NULL,
[Day_Of_Quarter] [int] NULL,
[Day_Of_Quarter_Name] [nvarchar](50) NULL,
[Day_Of_Month] [int] NULL,
[Day_Of_Month_Name] [nvarchar](50) NULL,
[Day_Of_Week] [int] NULL,
[Day_Of_Week_Name] [nvarchar](50) NULL,
[Week_Of_Year] [int] NULL,
[Week_Of_Year_Name] [nvarchar](50) NULL,
[Month_Of_Year] [int] NULL,
[Month_Of_Year_Name] [nvarchar](50) NULL,
[Month_Of_Quarter] [int] NULL,
[Month_Of_Quarter_Name] [nvarchar](50) NULL,
[Quarter_Of_Year] [int] NULL,
[Quarter_Of_Year_Name] [nvarchar](50) NULL,
CONSTRAINT [PK_DimTime] PRIMARY KEY CLUSTERED
(
[PK_Date] ASC
)
I am trying to get the following output using MDX script for all the patients whose AppointmentStatus = 'Kept'
CardiologyDr.ATotPatientsseen/Hrsworked
Dr.BTotPatientsseen/Hrsworked
DentalDr.CTotPatientsseen/Hrsworked
Dr.XTotPatientsseen/Hrsworked
So far, I have the following script written:
with set [Hrsused] as
'filter({[Dim Time].[Hour].Children},[measures].[Appt count] > 0)'
member
[dim time].[hour].[Hrsworked]
as 'count({[Hrsused]})'
please let me know how I can get the above result using mdx script. Thanks.
September 3, 2009 at 5:02 pm
Mh (9/3/2009)
So far, I have the following script written:with set [Hrsused] as
'filter({[Dim Time].[Hour].Children},[measures].[Appt count] > 0)'
member
[dim time].[hour].[Hrsworked]
as 'count({[Hrsused]})'
please let me know how I can get the above result using mdx script. Thanks.
The set [Hrsused] is going to give you the hours that ANY doctor had an appointment. You want the hours that a specific doctor had an appointment. I also don't think that you want this to be a member of the time dimension. I think that it should be a member of the measures "dimension". Try the following instead
WITH MEMBER [HrsWorked] AS
Count(
Filter([dim time].[hour].children, ( [dimDoctor].currentMember, [measures].[Appt count] ) )
)
You might need to tweak that a little.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 3, 2009 at 7:46 pm
Hopefully not over-simplifying the problem here but it would appear you're simply counting the appointments and determining that each of these is therefore an hour.
So, why not simply add an appointment duration field to the fact and sum it for the hours.
In the future, when you want to get more sophisticated, you could save the start and end times for appointments, maybe even have rules that take into account things like lunch etc, or have a process thta looks at the appt time and determines that a following appt exists and subtracts the time difference and sets this as the first appt duration - regardless, you can probably get more accurate than esimating an hour per appt.
Assuming you add this field (and today, you could do it easily, simply add a constant '1' as a calc field in the DSV) then to get the number of patients, use the Patient FK from the fact as a measure source, set the aggregation method to count - you now have the count of 'seen' patients (as opposed to # of patients on the books).
You could add another measure (using the same field as the source0 and set the aggregation to distinct count, this would cater for those instances where a patient might have visited >1 Dr in a day.
HTH,
Steve.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply