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 list of the queried parameters for a report (via soap)

What I'd liek to be able to do is display dropdowns (like the dropdowns in
the standard reportviewer component with queried parameters (eg, values come
from a datasource).
For example Employee Sales Summary in SampleReports includes a field for
employee name/id... which is automatically populated.
When i run GetReportParameters, I end up with nothing other dthan a type.
Thanks
Weston WeemsWeston,
Your code to call GetReportParameters should look similar to this:
bool forRendering = true;
string historyID = null;
ParameterValue[] values = null;
DataSourceCredentials[] credentials = null;
ReportParameter[] parameters = null;
parameters = rs.GetReportParameters(report, historyID, forRendering, values,
credentials);
Then you just have to loop through the ValidValues of parameters.
foreach (ReportParameter parameter in parameters)
{
/// insert code to evaluate which parameter type/name, if dropdown
foreach (ValidValue vv in parameter.ValidValues)
{
LI = new System.Web.UI.WebControls.ListItem(vv.Label, vv.Value);
///see if this value is the same with the default value
///in which case we make the current list item selected
if (vv.Value == parameter.DefaultValues[0] && parameter.State ==ParameterStateEnum.HasValidValue)
{
LI.Selected = true;
}
this.ProcessorID.Items.Add(LI);
}
This code works for the one parameter that I have setup as a query based
parameter in the report.
Hope that helps,
Steve
"Weston Weems" wrote:
> What I'd liek to be able to do is display dropdowns (like the dropdowns in
> the standard reportviewer component with queried parameters (eg, values come
> from a datasource).
> For example Employee Sales Summary in SampleReports includes a field for
> employee name/id... which is automatically populated.
> When i run GetReportParameters, I end up with nothing other dthan a type.
> Thanks
> Weston Weems
>
>sql

Getting 0 padded values in the columns.

Getting 0 padded values in the columns.

Hi All,

I have a requirement to convert a integer to string and display it in
Sql server with fixed length say 3 chars. (in c, we wud use %03d in
printf)

If the number is small say, 9 then it has to be displayed as 009,
56 -> 056, 897-> 897, 6786 -> xxx

Checked through STR and CAST functions, couldn't find any relevant
paramters.

if you have any ideas, please mail me.

Thanks & Regards,
Chandra MohanDo:

SELECT CASE WHEN LEN(@.n) <= 3
THEN RIGHT('000' + CAST(@.n AS VARCHAR), 3)
ELSE 'xxx'
END ;

--
- Anith
( Please reply to newsgroups only )|||bschandramohan@.yahoo.com (Chandra Mohan) wrote in message news:<bb0ef6.0308200036.236c3321@.posting.google.com>...
> Getting 0 padded values in the columns.
> Hi All,
> I have a requirement to convert a integer to string and display it in
> Sql server with fixed length say 3 chars. (in c, we wud use %03d in
> printf)
> If the number is small say, 9 then it has to be displayed as 009,
> 56 -> 056, 897-> 897, 6786 -> xxx
> Checked through STR and CAST functions, couldn't find any relevant
> paramters.
> if you have any ideas, please mail me.
> Thanks & Regards,
> Chandra Mohan

Hi ,

You could use this :

select replicate('0', 3-datalength(cast(column as varchar(10)))) +
cast (column as varchar(10)) from table

Replace the column and table with the right values and here the
assumption is the column is of int datatype.

Regards,
-Manoj Rajshekar

getteing table name

hi,

i have list of database name in drop down list, when i choose database i want the related table to be display in grid.?
how can i do this?how to set connection string for this?

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME

using this code i can display all tables in a particular databse in SQL quey analyser, it displays all tables from selected database in toolbar

but in my coding part

server=localhost;Trusted_Connection=true;database=Reports...

this is my connection string ...but it refers to only reports database,so the query will dispaly all table in reports DB.but i need to dispaly table for selected database in DDL.how cxan i set connection str then?or how can i write query to pass database name also in where clause?

Thanks for any help

You can set the value of the drop down list items to be the connection string to be used itself for the respective database. When the user changes the selection in the drop down list, you can put the code in the selected index change event of the drop down list and you can get the value of the currently selected item which will be the connection string to be used. Pass the value (i.e., the connection string) in another method and at the time of creating the connection object use that variable as the connection string. Feel free to ask about any queries for this approach.

Hope this helps.

Getdate() in UDF column workaround

I have a column that needs to display the number of Status hours between
Start_Date and either Stop_Date or Getdate() if StopDate is empty. I am usin
g
an Access project as my front end and SQL Server 2000 as my back end. I have
tried using the following as a row source in my function:
CASE WHEN STATUS_STOP_DATE IS NULL THEN datediff([HH] , STATUS_START_DATE +
STATUS_START_TIME , Getdate()) ELSE datediff([HH] , STATUS_START_DATE +
STATUS_START_TIME , STATUS_STOP_DATE + STATUS_STOP_TIME) END
I get an Invalid use of Getdate() in a function. Ok so I can't use getdate
like that. How can I display the status time on my form? I was thinking mayb
e
the text box record source could be a select statement but not sure how to
write it, any ideas?DateDiff(HH, STATUS_START_DATE + STATUS_START_TIME,
COALESCE(STATUS_STOP_DATE + STATUS_STOP_TIME,
GETDATE())
Roy
On Sat, 4 Mar 2006 14:08:27 -0800, AkAlan
<AkAlan@.discussions.microsoft.com> wrote:

>I have a column that needs to display the number of Status hours between
>Start_Date and either Stop_Date or Getdate() if StopDate is empty. I am usi
ng
>an Access project as my front end and SQL Server 2000 as my back end. I hav
e
>tried using the following as a row source in my function:
>CASE WHEN STATUS_STOP_DATE IS NULL THEN datediff([HH] , STATUS_START_DATE +
>STATUS_START_TIME , Getdate()) ELSE datediff([HH] , STATUS_START_DATE +
>STATUS_START_TIME , STATUS_STOP_DATE + STATUS_STOP_TIME) END
>I get an Invalid use of Getdate() in a function. Ok so I can't use getdate
>like that. How can I display the status time on my form? I was thinking may
be
>the text box record source could be a select statement but not sure how to
>write it, any ideas?|||Hi
CREATE FUNCTION dbo.Get_Getdate
(@.dt DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN @.dt
END
SELECT dbo.Get_Getdate (GETDATE())
SELECT dbo.Get_Getdate ('20050101')
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:30B29109-1B8E-4716-A506-EEDB943F4B64@.microsoft.com...
>I have a column that needs to display the number of Status hours between
> Start_Date and either Stop_Date or Getdate() if StopDate is empty. I am
> using
> an Access project as my front end and SQL Server 2000 as my back end. I
> have
> tried using the following as a row source in my function:
> CASE WHEN STATUS_STOP_DATE IS NULL THEN datediff([HH] , STATUS_START_DATE
> +
> STATUS_START_TIME , Getdate()) ELSE datediff([HH] , STATUS_START_DATE +
> STATUS_START_TIME , STATUS_STOP_DATE + STATUS_STOP_TIME) END
> I get an Invalid use of Getdate() in a function. Ok so I can't use getdate
> like that. How can I display the status time on my form? I was thinking
> maybe
> the text box record source could be a select statement but not sure how
> to
> write it, any ideas?sql

GetDate()

G'day,
I want to display the current time minus 5 minutes. say
2004-03-29 11:43:49.123 to be 2004-03-29 11:38:49.123.

Is there any Equivalent to the DB2's CURRENT TIMESTAMP - 5 MINUTES which will display the current day say 2004-03-29 11:43:49.123 as
2004-03-29 11:38:49.123

cheers
Melbselect dateadd(mi, -5, getdate())

but: "Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds)." from BOL 'datetime and smalldatetime'.|||Thanks Kaiowas

cheers
Melb

getdate - only want the date

when I am doing a query I want to display just the date.
when I use the getdate function it returns the date and
time from each of the fields...is there a way to just
display the date from the fields and leave out the time?Use CONVERT or CAST. You can find some examples in books online.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Jamie Elliott" <jelliott@.alexlee.com> wrote in message
news:0b7601c39e63$2293d350$a001280a@.phx.gbl...
> when I am doing a query I want to display just the date.
> when I use the getdate function it returns the date and
> time from each of the fields...is there a way to just
> display the date from the fields and leave out the time?|||You can use following to display only date.
select convert(char(11),getdate())
If you want to display in different format , then extract
dd, mm, yy from getdate and concatinate it.
Suryakant
>--Original Message--
>when I am doing a query I want to display just the date.
>when I use the getdate function it returns the date and
>time from each of the fields...is there a way to just
>display the date from the fields and leave out the time?
>.
>|||The only way is to convert it to a string. There are several formats so you
should check out CONVERT() in BooksOnLine for the one your after.
--
Andrew J. Kelly
SQL Server MVP
"Jamie Elliott" <jelliott@.alexlee.com> wrote in message
news:0b7601c39e63$2293d350$a001280a@.phx.gbl...
> when I am doing a query I want to display just the date.
> when I use the getdate function it returns the date and
> time from each of the fields...is there a way to just
> display the date from the fields and leave out the time?|||worked fine thank you

Get the month from a date field

Hi,

There's probably a really simple asnwer to this but i cant seem to find it.

I have a date field and i want to display just the month in words.

At the moment i have

DatePart(m,PressreleaseDate) AS stMonth

but that returns a value of 1-12, i want January to December

can anyone help

Regards,

LukeNevermind i've worked it ut

Datename(month,([PressreleaseDate])) as 'MonthName'

Easy!

Get the Display value of a variable drop-down to display on report

I have a report where the drop-down list is presented to the User at runtime.
This drop-down list displays the User First and Last Name as the option of
the drop-down. But, the value of their selection is set to the EmployeeID.
How do I get the select Full Name to appear on the report for the selected
name from the variable drop-down list?
thanks,
SeanHi Sean.
You need to return the full name in your result set or pass it in as another
parameter.
--
Regards,
Tim Ellison, MCP
Ironworks Consulting, LLC
(m) 804.405.4874
"Sean" <Sean@.discussions.microsoft.com> wrote in message
news:00CC804C-31A7-4740-A670-C605BF162A05@.microsoft.com...
> I have a report where the drop-down list is presented to the User at
runtime.
> This drop-down list displays the User First and Last Name as the option of
> the drop-down. But, the value of their selection is set to the
EmployeeID.
> How do I get the select Full Name to appear on the report for the selected
> name from the variable drop-down list?
> thanks,
> Sean
>

get the description of a column

Okay guys heres the senario.

I have written a kick butt asp application that allows me to test sql
statements and manage/display all my databases from the web but I have
a feature I want to include that I can't figure out how. In Enterprise
Manager, one of the column editable properties is the Column
Description. I can't find it in sql server itself. only in the
Enterprise Manager. I need to access it using a sql statement so that
it will display in my table definiation view that I create in the asp
app.These descriptions are kept as extended properties in sysproperties.
Look up sp_addextendedproperty in the help file for more information.
To figure out what Enterprise Manager is doing in situations like
these, you can use Profiler to see what SQL code it is sending to the
server.

-Tom.