Getting a proper COUNT

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.

Getting a error in DTS Active x script

Can anybody help me with this?

I'm trying to update three fields in one table from an import table. Is the anything you see missing in my code:

Dim rs1, strSQL

strSQL = "Select safety_valve_cap, hydro_date, hydro_psi, state_No From HBC_ZImport"
set rs1 = objConn.Execute(strSQL)

Do While NOT rs1.EOF

strSQL = "Update HBC_Boiler_Inspection set Safety_Valve_Cap = '" & rs1 ("Safety_Valve_Cap") & "', "
strSQL = strSQL & " Hydro_PSI = '" & rs1 ("Hydro_PSI") & "', "
strSQL = strSQL & " Hydro_Date = '" & rs1 ("Hydro_Date") & "' "
strSQL = strSQL & " where Boiler_ID = (Select ID from HBC_Boiler where State_No = ' " & rs1 ( "State_No") & " ') & " ' "
objConn.Execute(strSQL)

rs1.MoveNext()
Loop

Function Main()
Main = DTSTaskExecResult_Success
End FunctionLook at http://dbforums.com/t673820.html

Hugh Scott

Originally posted by Bigced_21
Can anybody help me with this?

I'm trying to update three fields in one table from an import table. Is the anything you see missing in my code:

Dim rs1, strSQL

strSQL = "Select safety_valve_cap, hydro_date, hydro_psi, state_No From HBC_ZImport"
set rs1 = objConn.Execute(strSQL)

Do While NOT rs1.EOF

strSQL = "Update HBC_Boiler_Inspection set Safety_Valve_Cap = '" & rs1 ("Safety_Valve_Cap") & "', "
strSQL = strSQL & " Hydro_PSI = '" & rs1 ("Hydro_PSI") & "', "
strSQL = strSQL & " Hydro_Date = '" & rs1 ("Hydro_Date") & "' "
strSQL = strSQL & " where Boiler_ID = (Select ID from HBC_Boiler where State_No = ' " & rs1 ( "State_No") & " ') & " ' "
objConn.Execute(strSQL)

rs1.MoveNext()
Loop

Function Main()
Main = DTSTaskExecResult_Success
End Function

Get Missing Dates

Hi,
I have 2 tables, one a list of people, and the other a table of daily
diary entries for those people.
Now i need to get a list of all people and dates that don't have an entry.
I have created a calendar table in order to assist but i can't figure
out how to pull back the results as i need.
What i need is something along the lines of
PersonId | Date
--
1 | '2006-01-01'
1 | '2006-01-02'
1 | '2006-01-12'
2 | '2006-01-01'
2 | '2006-01-21'
etc... | etc...
Any help would be greatly appreciated.
Many thanks
MattSo we have some idea...
http://www.aspfaq.com/5006
"Matt Brailsford" <matt@.gradiation.co.uk> wrote in message
news:OkGE6nvQGHA.3972@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have 2 tables, one a list of people, and the other a table of daily
> diary entries for those people.
> Now i need to get a list of all people and dates that don't have an entry.
> I have created a calendar table in order to assist but i can't figure out
> how to pull back the results as i need.
> What i need is something along the lines of
> PersonId | Date
> --
> 1 | '2006-01-01'
> 1 | '2006-01-02'
> 1 | '2006-01-12'
> 2 | '2006-01-01'
> 2 | '2006-01-21'
> etc... | etc...
> Any help would be greatly appreciated.
> Many thanks
> Matt|||Try this:
declare @.Person table (personid int)
insert @.person values (1)
insert @.person values (2)
declare @.Date table (dt datetime)
insert @.Date values ('01 Jan 2006')
insert @.Date values ('02 Jan 2006')
insert @.Date values ('03 Jan 2006')
insert @.Date values ('04 Jan 2006')
insert @.Date values ('05 Jan 2006')
insert @.Date values ('06 Jan 2006')
insert @.Date values ('07 Jan 2006')
declare @.Diary table (personid int, dt datetime)
insert @.Diary values(1, '01 Jan 2006')
insert @.Diary values(1, '03 Jan 2006')
insert @.Diary values(1, '04 Jan 2006')
insert @.Diary values(2, '05 Jan 2006')
insert @.Diary values(2, '06 Jan 2006')
select p.personid, d.dt
from @.Person p
cross join @.Date d
left outer join @.Diary e
on p.personid = e.personid and d.dt = e.dt
where e.personid is null|||Excellent,
That looks exactly what i need.
I'll give it a try.
jeff.bolton@.citigatehudson.com wrote:
> Try this:
> declare @.Person table (personid int)
> insert @.person values (1)
> insert @.person values (2)
> declare @.Date table (dt datetime)
> insert @.Date values ('01 Jan 2006')
> insert @.Date values ('02 Jan 2006')
> insert @.Date values ('03 Jan 2006')
> insert @.Date values ('04 Jan 2006')
> insert @.Date values ('05 Jan 2006')
> insert @.Date values ('06 Jan 2006')
> insert @.Date values ('07 Jan 2006')
> declare @.Diary table (personid int, dt datetime)
> insert @.Diary values(1, '01 Jan 2006')
> insert @.Diary values(1, '03 Jan 2006')
> insert @.Diary values(1, '04 Jan 2006')
> insert @.Diary values(2, '05 Jan 2006')
> insert @.Diary values(2, '06 Jan 2006')
> select p.personid, d.dt
> from @.Person p
> cross join @.Date d
> left outer join @.Diary e
> on p.personid = e.personid and d.dt = e.dt
> where e.personid is null
>

Get message : The query cannot be excuted because some files are missing...

Hi

I can't work with my sql server 2000,
When I try to open a table I get the message:
"The query cannot be executed because some files are missing or not registered.

run setup again to make sure the required files are registered.

I uninstall and install again (few time)
But all the time i get this message.

Does any one know who to fix it??

Thanks
Efrat"Efrat" <shachare@.bgumail.bgu.ac.il> wrote in message
news:bcb64ccd.0405110145.62ff7855@.posting.google.c om...
> Hi
> I can't work with my sql server 2000,
> When I try to open a table I get the message:
> "The query cannot be executed because some files are missing or not
registered.
> run setup again to make sure the required files are registered.
> I uninstall and install again (few time)
> But all the time i get this message.
> Does any one know who to fix it??
> Thanks
> Efrat

Perhaps this applies?

http://support.microsoft.com/defaul...kb;en-us;315868

Simon