SQL,tables in a DB: value

Showing posts with label value. Show all posts

Sunday, March 25, 2012

(nothing = 0) = true !

I have a bit type field that can have values 0, 1 and NULL in SQL database.
I want to hide an image when the value is different from 0.
The expression used in the visibility.Hidden property is
"=First(Fields!myfield.Value, "DataSet1") <>0"
And when the database value is NULL this expression returns false.
Please help !
(a French user...)I really canâ't understand why something like that does not work. I always
have to use something like the following:
=iif(Expression, 1=1, 1=0)
I hope it helps you!
> I have a bit type field that can have values 0, 1 and NULL in SQL database.
> I want to hide an image when the value is different from 0.
> The expression used in the visibility.Hidden property is
> "=First(Fields!myfield.Value, "DataSet1") <>0"
> And when the database value is NULL this expression returns false.
> Please help !
> (a French user...)
>|||thanks for you answer but it doen't help me...
I made this test : expression "= nothing = 0" in a new field, it's return
true !
"Soan" wrote:
> I really canâ't understand why something like that does not work. I always
> have to use something like the following:
> =iif(Expression, 1=1, 1=0)
> I hope it helps you!
>
> > I have a bit type field that can have values 0, 1 and NULL in SQL database.
> > I want to hide an image when the value is different from 0.
> > The expression used in the visibility.Hidden property is
> > "=First(Fields!myfield.Value, "DataSet1") <>0"
> > And when the database value is NULL this expression returns false.
> >
> > Please help !
> >
> > (a French user...)
> >|||I haven't tried it myself, but you may want to try the following:
"=(First(Fields!myfield.Value, "DataSet1") <> 0) OR
(First(Fields!myfield.Value, "DataSet1") IS DBNull.Value)"
Hope this helps.
Andy
tonio932 wrote:
> I have a bit type field that can have values 0, 1 and NULL in SQL database.
> I want to hide an image when the value is different from 0.
> The expression used in the visibility.Hidden property is
> "=First(Fields!myfield.Value, "DataSet1") <>0"
> And when the database value is NULL this expression returns false.
> Please help !
> (a French user...)
>|||When comparing with NULLs you have to use "is Nothing".
E.g. =iif(Fields!myField.Value is Nothing, True-Part, False-Part)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"tonio932" <tonio932@.discussions.microsoft.com> wrote in message
news:C4E4FDE8-81C8-41E4-AE37-09671864812A@.microsoft.com...
>I have a bit type field that can have values 0, 1 and NULL in SQL database.
> I want to hide an image when the value is different from 0.
> The expression used in the visibility.Hidden property is
> "=First(Fields!myfield.Value, "DataSet1") <>0"
> And when the database value is NULL this expression returns false.
> Please help !
> (a French user...)
>|||I have noticed that when a column value from the database is null, doing
myfield.Value is the same as doing null.Value, which makes RS blow.
Andy Hardin wrote:
> I haven't tried it myself, but you may want to try the following:
> "=(First(Fields!myfield.Value, "DataSet1") <> 0) OR
> (First(Fields!myfield.Value, "DataSet1") IS DBNull.Value)"
> Hope this helps.
> Andy
>
> tonio932 wrote:
>> I have a bit type field that can have values 0, 1 and NULL in SQL
>> database.
>> I want to hide an image when the value is different from 0.
>> The expression used in the visibility.Hidden property is
>> "=First(Fields!myfield.Value, "DataSet1") <>0"
>> And when the database value is NULL this expression returns false.
>> Please help !
>> (a French user...)sql

Thursday, March 22, 2012

((0)) set as Default Value or Binding for an int Data Type

Hi there,

I'm trying to set an int type attribute to 0 for its Default Value, but it keeps reverting to ((0)). What is causing this?

Every int type attribute on that table does the same. There is one bit type attribute and a bunch of other type of attributes, but non of them are giving me a problem. The table is a copy from another database and I did check all the constraints and properties to make sure they're the same.

Thank you,

--Alex

That is normal behavior for SSMS.

Monday, March 19, 2012

"Static variable" in function

Hi!

I have a function that uses a constant value on its calculations. This value is defined on a table. I don't want to query this table everytime I call the function (I call it on a loop from my Java code). Is there anything like a static variable I could use?

Thank you!

No. There is no such functionality in TSQL. Your approach sounds fine. Alternatively, you can define a scalar UDF that returns the constant value instead of storing it in the table.

Sunday, March 11, 2012

"One or more DataSources is missing credentials"

Get this when selecting parameter value in Report Manager

Report is working fine in preview

Up to me, it has nothing to do with credentials

I have some analysis server reports.

I have a single value report parameter, the report runs fine with the default value, when I select another value than the default one, I get above message immediately.

I got the error since I rebuild the report with SP2 CTP.

Strange thing is that the other single value parameter of the report works fine (year)

And that I get the error also with other reports, and on the same dimension (Organisation)

And that I do not have the error when I change the parameter to multi-value (but as the report is not written for multiple values...)

MDX queries are as follows:

SELECT NON EMPTY { KPIValue("KPINewBiz"), KPIGoal("KPINewBiz"), KPIStatus("KPINewBiz"), KPITrend("KPINewBiz") } ON COLUMNS,

NONEMPTY

([Reps].[Organisation].[Organisation].ALLMEMBERS

*[Reps].[Rep].[Rep].ALLMEMBERS

*[Date].[Year].[Year].ALLMEMBERS

*[Date].[Month].[Month].ALLMEMBERS

,[Measures].[Gross Sales]

)

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM ( SELECT ( STRTOSET(@.ByYear, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.ByOrganisation, CONSTRAINED) ) ON COLUMNS FROM [Sales])) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

WITH MEMBER [Measures].[ParameterCaption] AS '[Reps].[Organisation].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Reps].[Organisation].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Reps].[Organisation].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,

NONEMPTY([Reps].[Organisation].[Organisation].ALLMEMBERS,[Measures].[Gross Sales]) ON ROWS FROM [Sales]

WITH MEMBER [Measures].[ParameterCaption] AS '[Date].[Year].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Date].[Year].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Date].[Year].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , NONEMPTY([Date].[Year].[Year].ALLMEMBERS,[Measures].[Gross Sales]) ON ROWS FROM [Sales]

Discovered something else that is very strange, you change the value of the parameter, you get the error, you hit the back button, and you can run the report with the new parameter

Did one more test, changed the order of the parameters, is now going wrong on the other parameter, Year.

So, it is always on the first parameter.

Correction, I have put a multi-value parameter as first, I still have the problem, it is always on the first single value parameter

I have the same problem.

Up to me, it has nothing to do with credentials

I have some analysis server reports.

I have a single value report parameter, the report runs fine with the default value, when I select another value than the default one, I get above message immediately.

I got the error since I rebuild the report with SP2 CTP.

Strange thing is that the other single value parameter of the report works fine (year)

And that I get the error also with other reports, and on the same dimension (Organisation)

And that I do not have the error when I change the parameter to multi-value (but as the report is not written for multiple values...)

MDX queries are as follows:

SELECT NON EMPTY { KPIValue("KPINewBiz"), KPIGoal("KPINewBiz"), KPIStatus("KPINewBiz"), KPITrend("KPINewBiz") } ON COLUMNS,

NONEMPTY

([Reps].[Organisation].[Organisation].ALLMEMBERS

*[Reps].[Rep].[Rep].ALLMEMBERS

*[Date].[Year].[Year].ALLMEMBERS

*[Date].[Month].[Month].ALLMEMBERS

,[Measures].[Gross Sales]

)

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

NONEMPTY([Reps].[Organisation].[Organisation].ALLMEMBERS,[Measures].[Gross Sales]) ON ROWS FROM [Sales]

Discovered something else that is very strange, you change the value of the parameter, you get the error, you hit the back button, and you can run the report with the new parameter

Microsoft released a December update to the CTP yesterday. The update fixes the problem.

http://www.microsoft.com/downloads/details.aspx?FamilyId=D2DA6579-D49C-4B25-8F8A-79D14145500D&displaylang=en

|||thanks

Thursday, March 8, 2012

"Less Than or Equal To" MDX query

The following MDX query is generated by the Reporting Services? I need to edit the this MDX query so that it will accept the parameter value "Less Than Or Equal To" the @.AccountPeriodAccountPeriod parameter to filter teh cube data. Thanks.

SELECT NON EMPTY { [Measures].[Costing], [Measures].[Distinct Count] } ON COLUMNS, NON EMPTY { ([AgencyID].[Agency Id].[Agency Id].ALLMEMBERS * [Account Period].[Account Period].[Account Period].ALLMEMBERS * [Account Period].[Prescription Date].[Prescription Date].ALLMEMBERS * [Drug].[Protocol Code].[Protocol Code].ALLMEMBERS * [Drug].[Drug Name].[Drug Name].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT (STRTOSET(@.AccountPeriodAccountPeriod, CONSTRAINED)) ON COLUMNS FROM ( SELECT ( STRTOSET(@.DrugDrugFirstWord, CONSTRAINED) ) ON COLUMNS FROM [Drug Cost By Account Period])) WHERE ( IIF( STRTOSET(@.DrugDrugFirstWord, CONSTRAINED).Count = 1, STRTOSET(@.DrugDrugFirstWord, CONSTRAINED), [Drug].[Drug First Word].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Assuming that there is an [AccountPeriod].[AccountPeriod] attribute, ordered ascending by time:

SELECT (Filter([AccountPeriod].[AccountPeriod].[AccountPeriod].Members,

Rank([AccountPeriod].[AccountPeriod].CurrentMember,

[AccountPeriod].[AccountPeriod].[AccountPeriod].Members) < =

Rank(STRTOSET(@.AccountPeriodAccountPeriod, CONSTRAINED).Item(0).Item(0),

[AccountPeriod].[AccountPeriod].[AccountPeriod].Members)))

|||Thank you Deepak Puri and it is worked beautifully. However I forgot to include one other condition that only the AgencyID has the Account Period equal to the @.AccountPeriod should be allowed in the final dataset. AgencyID is from another dimension different from the Account Period dimension. Thanks.|||

If you want only those AgencyID members with [Measures].[Costing] data for the selected Account Period, try replacing:

[AgencyID].[Agency Id].[Agency Id].ALLMEMBERS

with:

NonEmpty([AgencyID].[Agency Id].[Agency Id].ALLMEMBERS,

{[Measures].[Costing]} *

STRTOSET(@.AccountPeriodAccountPeriod, CONSTRAINED))

Saturday, February 25, 2012

"Cannot apply value null to property Login: Value cannot be null..".

I'm trying to use a transfer "SQL Server Objects Task" between a 2005 SQL Server Database and a 2000 SQL Sever Database. I have the login info specified in the connection I'm using.
According to the Debug Output the process truncates the destination tables but then stops with the: "Cannot apply value null to property Login: Value cannot be null..".
Any help anybody?
NFThis probably means that you need to specify a login to use in the transfer.

Could you repost this in the SSIS forum? (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=80&SiteID=1)|||

I had the same problem and I was specifying the sa login for my connection. When I tested the connection everything worked fine. But I received the same error message when I attempted to execute the package. I changed it to Windows Authentication and it worked fine. Unfortunately, I have to be able to specify a SQL login when I roll this into production.

I have tried all of the PackageProtectionLevels, but none seem to work. Any help would be greatly appreciated.

|||

I had the same problem. I installed SQL 2005 SP1 and it worked for me with SQL authentication.

|||

Unfortunately I’m using SSIS between a SQL 2000 (local) & SQL 2005 (remote). If I install SQL 2005 SP1 on my machine will that cure this problem – meaning, will the SSIS tools work properly?

-Cheers

|||Try changing your compatability level of the database to 2005

"Cannot apply value null to property Login: Value cannot be null..".

Could you repost this in the SSIS forum? (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=80&SiteID=1)|||

I have tried all of the PackageProtectionLevels, but none seem to work. Any help would be greatly appreciated.

|||

I had the same problem. I installed SQL 2005 SP1 and it worked for me with SQL authentication.

|||

Unfortunately I’m using SSIS between a SQL 2000 (local) & SQL 2005 (remote). If I install SQL 2005 SP1 on my machine will that cure this problem – meaning, will the SSIS tools work properly?

-Cheers

|||Try changing your compatability level of the database to 2005

Friday, February 24, 2012

"Cannot apply value null to property Login: Value cannot be null..".

Could you repost this in the SSIS forum? (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=80&SiteID=1)|||

I have tried all of the PackageProtectionLevels, but none seem to work. Any help would be greatly appreciated.

|||

I had the same problem. I installed SQL 2005 SP1 and it worked for me with SQL authentication.

|||

Unfortunately I’m using SSIS between a SQL 2000 (local) & SQL 2005 (remote). If I install SQL 2005 SP1 on my machine will that cure this problem – meaning, will the SSIS tools work properly?

-Cheers

|||Try changing your compatability level of the database to 2005

"Cannot apply value null to property Login: Value cannot be null..".

I'm trying to use a transfer "SQL Server Objects Task" between a 2005 SQL Server Database and a 2000 SQL Sever Database. I have the login info specified in the connection I'm using.
According to the Debug Output the process truncates the destination tables but then stops with the: "Cannot apply value null to property Login: Value cannot be null..".
Any help anybody?
NF
This probably means that you need to specify a login to use in the transfer.

Could you repost this in the SSIS forum? (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=80&SiteID=1)|||

I have tried all of the PackageProtectionLevels, but none seem to work. Any help would be greatly appreciated.

|||

I had the same problem. I installed SQL 2005 SP1 and it worked for me with SQL authentication.

|||

Unfortunately I’m using SSIS between a SQL 2000 (local) & SQL 2005 (remote). If I install SQL 2005 SP1 on my machine will that cure this problem – meaning, will the SSIS tools work properly?

-Cheers

|||Try changing your compatability level of the database to 2005

Sunday, February 19, 2012

<Select all> disappear on server

Hello.
I have a parameter with multi value option.
When I run the report in Visual studio, The first value in the drop
down of this parameter is (Select All).
When I check this option it check all the other values for this
parameter and when I unCheck this value it uncheck all the ther values
for this parameter.
The problem is thet when I'm publishing the report to the server and
then open it in IE the (Select All) value does't appear and I only get
all the other values.
Any body know how to fix this?
Thanks.On Nov 27, 11:08 pm, nicknack <roezo...@.gmail.com> wrote:
> Hello.
> I have a parameter with multi value option.
> When I run the report in Visual studio, The first value in the drop
> down of this parameter is (Select All).
> When I check this option it check all the other values for this
> parameter and when I unCheck this value it uncheck all the ther values
> for this parameter.
> The problem is thet when I'm publishing the report to the server and
> then open it in IE the (Select All) value does't appear and I only get
> all the other values.
> Any body know how to fix this?
> Thanks.
Hi
It might be a service pack issue, here's a link to Microsoft.
http://download.microsoft.com/download/2/B/5/2B5E5D37-9B17-423D-BC8F-B11ECD4195B4/WhatsNewSQL2005SP2.htm

Thursday, February 16, 2012

>= in Case Statement - Stuck

I'm struggling with a Case statement. The problem I has is with doing
>= I can use any value in there, but need to check if it's greater or
equal to 1. I'm sure I'm missing something but can't figure out what.

I've put the line below in case anyone has any suggestions. I've
limited it to the offending line, but can add more if needed.

This version works
------
CASE m.Splinter_Status WHEN 'SUR' THEN 0 ELSE CASE m.Sacrifice WHEN 0
THEN SUM(3*m.Premium/100) - (Introducer *
(SUM(3*m.Premium)/100)/m.Estimated_Initial_Comm_Amt) ELSE 0 END END AS
Bond2,

This version fails
------
CASE m.Splinter_Status WHEN 'SUR' THEN 0 ELSE CASE m.Sacrifice WHEN
>=1 THEN SUM(3*m.Premium/100) - (Introducer *
(SUM(3*m.Premium)/100)/m.Estimated_Initial_Comm_Amt) ELSE 0 END END AS
Bond2,

Thanks in advance.

Ryanryanofford@.hotmail.com (Ryan) wrote in news:7802b79d.0311190628.73e93bb0
@.posting.google.com:

> I'm struggling with a Case statement. The problem I has is with doing
>>= I can use any value in there, but need to check if it's greater or
>>equal to 1. I'm sure I'm missing something but can't figure out what.
> I've put the line below in case anyone has any suggestions. I've
> limited it to the offending line, but can add more if needed.
> This version works
> ------
> CASE m.Splinter_Status WHEN 'SUR' THEN 0 ELSE CASE m.Sacrifice WHEN 0
> THEN SUM(3*m.Premium/100) - (Introducer *
> (SUM(3*m.Premium)/100)/m.Estimated_Initial_Comm_Amt) ELSE 0 END END AS
> Bond2,
> This version fails
> ------
> CASE m.Splinter_Status WHEN 'SUR' THEN 0 ELSE CASE m.Sacrifice WHEN
>>=1 THEN SUM(3*m.Premium/100) - (Introducer *
> (SUM(3*m.Premium)/100)/m.Estimated_Initial_Comm_Amt) ELSE 0 END END AS
> Bond2,

From BOL:

Syntax
Simple CASE function:

CASE input_expression
WHEN when_expression THEN result_expression
[...n]
[
ELSE else_result_expression
]
END

Searched CASE function:

CASE
WHEN Boolean_expression THEN result_expression
[...n]
[
ELSE else_result_expression
]
END

Don't try to mix the two. Use the second form, i.e.

... CASE WHEN m.Sacrifice>=1 THEN ...

HTH|||What error are you getting?

You are mixing scalar and aggregate values, which is fine if you are
grouping the data appropriately but erroneous otherwise.

An error message would help us to help you...

HTH

Steve

=======================================
Everyone here speaks SQL; some are more fluent, others less. When
describing your SQL object (table, etc.), do so in the language that we
all understand - SQL, not English. It makes it easier to understand
your issue and makes it more likely that you will get the assistance
that you are asking for.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hi Ryan,

CASE actually has 2 flavors. Those tricky microsofties. I think you
want the second flavor. - Louis

a) CASE variable WHEN literal value THEN this expression ...
b) CASE WHEN expression THEN this expression...|||The error message is.

Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '>'.

The full version of the query is as follows (for those who want to
read it).

The only parts that cause me problems is using the greater than or
equal to expression. Each offending line bar the first problem is
commented out, but it's the same problem on each. At least I'm
consistent :-)

SELECT
CASE m.Splinter_Status WHEN 'SUR' THEN 0 ELSE CASE m.Rebate WHEN
NULL THEN 0 ELSE SUM(3*m.Premium/100) END END AS Bond1,
CASE m.Splinter_Status WHEN 'SUR' THEN 0 ELSE CASE m.Sacrifice WHEN
>=1 THEN SUM(3*m.Premium/100) - (Introducer *
(SUM(3*m.Premium)/100)/m.Estimated_Initial_Comm_Amt) ELSE 0 END END AS
Bond2,
-- CASE m.Splinter_Status WHEN 'SUR' THEN 0 ELSE CASE m.Rebate WHEN
>=1 THEN SUM(3*m.Premium/100) - (Introducer *
(SUM(3*m.Premium)/100)/m.Estimated_Initial_Comm_Amt) ELSE 0 END END AS
Bond3,
-- CASE m.Splinter_Status WHEN 'SUR' THEN 0 ELSE CASE m.Introducer
WHEN >=1 THEN SUM(3*m.Premium/100) - (Introducer *
(SUM(3*m.Premium)/100)/m.Estimated_Initial_Comm_Amt) ELSE 0 END END AS
Bond4,
0 AS Bond2,
0 AS Bond3,
0 AS Bond4,
CASE m.Freq WHEN '' THEN m.Gross ELSE 0 END AS ISA1,
-- CASE SUM(m.Gross * 60) WHEN (SUM(m.Gross * 60) >= 360) THEN 360
ELSE 0 END AS ISA2,
0 AS ISA2,
0 AS ISA3, /*Empty*/
0 AS ISA4, /*Empty*/
CASE m.Freq WHEN '' THEN m.Gross ELSE 0 END AS AccSick1,
-- CASE m.PaymentMethod WHEN 'L' THEN CASE SUM(Gross*12) WHEN
(Gross*12) = 360 THEN 360 ELSE m.Gross END ELSE 0 END AS AccSick2,
0 AS AccSick2,
CASE m.PaymentMethod WHEN 'L' THEN 0 ELSE SUM(Gross-Payaway)*100 END
AS AccSick3,
0 AS AccSick4, /*Empty*/
SUM(m.Gross-m.Payaway) * 100 AS General1,
0 AS General2, /*Empty*/
0 AS General3, /*Empty*/
0 AS General4, /*Empty*/
CASE m.PaymentMethod WHEN 'L' THEN SUM(m.Gross*36) ELSE 0 END AS
Pensions1,
CASE m.PaymentMethod WHEN 'L' THEN 0 ELSE SUM((m.Gross - m.Payaway)
* 100) END AS Pensions2,
0 AS Pensions3, /*Empty*/
0 AS Pensions4, /*Empty*/
m.YearNo,
m.PeriodNo,
m.Weekno,
m.Auditref,
m.AuditReflocation,
m.Adjustaudit,
m.Element,
m.Elementlocation,
m.Elementtype,
m.AssignDate,
m.EarnComm,
m.Introducer,
m.Gross,
m.Provision,
m.Rebate,
m.RetBranch,
m.Sacrifice,
m.Payaway,
m.Premium,
m.MyCredit,
m.Credit,
m.freq,
m.Costcode,
m.Product_group,
m.TransStatus,
m.Splinter_status,
m.Provider_ref,
m.Brand_ref,
m.Source_ref,
m.Source_desc,
m.Client_ref,
m.client,
m.Provider,
m.Holding_ref,
m.ProdDesc,
m.Prodcode,
m.Class,
m.Classdesc,
m.ConHierLevRef1,
m.ConHierLevDes1,
m.ConHierLevRef2,
m.ConHierLevDes2,
m.ConHierLevRef3,
m.ConHierLevDes3,
m.ConHierLevRef4,
m.ConHierLevDes4,
m.RetHierLevRef1,
m.RetHierLevDes1,
m.RetHierLevRef2,
m.RetHierLevDes2,
m.RetHierLevRef3,
m.RetHierLevDes3,
m.RetHierLevRef4,
m.RetHierLevDes4,
m.Analysis_1_reference,
m.analysis_2_reference,
m.StreamType,
m.Start_date,
m.Leave_date,
m.Pia_adv_comp_achieve,
m.Effectivedate,
m.Statusatcreate,
m.Policy_contract_number,
m.Commencementdate,
m.User_ID,
m.Date_time,
m.Estimated_initial_comm_amt,
m.Admin_Status,
m.Admin_Status_Date,
m.Campaign_ref,
m.Campaign,
m.ManualJournalRef,
m.Notes,
m.UserID,
m.JournalDate,
m.FormulaApplied,
m.UpliftApplied,
m.Date,
m.Retainer,
m.CommInitialPeriod,
m.PaymentMethod

FROM
CBFA_MISData_Local m

WHERE
m.AssignDate = 20031031 AND
m.Element = 183519 AND
m.ConHierLevRef1 = 6093138 AND
m.Gross = 625.0

GROUP BY
m.YearNo,
m.PeriodNo,
m.Weekno,
m.Auditref,
m.AuditReflocation,
m.Adjustaudit,
m.Element,
m.Elementlocation,
m.Elementtype,
m.AssignDate,
m.EarnComm,
m.Introducer,
m.Gross,
m.Provision,
m.Rebate,
m.RetBranch,
m.Sacrifice,
m.Payaway,
m.Premium,
m.MyCredit,
m.Credit,
m.freq,
m.Costcode,
m.Product_group,
m.TransStatus,
m.Splinter_status,
m.Provider_ref,
m.Brand_ref,
m.Source_ref,
m.Source_desc,
m.Client_ref,
m.client,
m.Provider,
m.Holding_ref,
m.ProdDesc,
m.Prodcode,
m.Class,
m.Classdesc,
m.ConHierLevRef1,
m.ConHierLevDes1,
m.ConHierLevRef2,
m.ConHierLevDes2,
m.ConHierLevRef3,
m.ConHierLevDes3,
m.ConHierLevRef4,
m.ConHierLevDes4,
m.RetHierLevRef1,
m.RetHierLevDes1,
m.RetHierLevRef2,
m.RetHierLevDes2,
m.RetHierLevRef3,
m.RetHierLevDes3,
m.RetHierLevRef4,
m.RetHierLevDes4,
m.Analysis_1_reference,
m.analysis_2_reference,
m.StreamType,
m.Start_date,
m.Leave_date,
m.Pia_adv_comp_achieve,
m.Effectivedate,
m.Statusatcreate,
m.Policy_contract_number,
m.Commencementdate,
m.User_ID,
m.Date_time,
m.Estimated_initial_comm_amt,
m.Admin_Status,
m.Admin_Status_Date,
m.Campaign_ref,
m.Campaign,
m.ManualJournalRef,
m.Notes,
m.UserID,
m.JournalDate,
m.FormulaApplied,
m.UpliftApplied,
m.Date,
m.Retainer,
m.CommInitialPeriod,
m.PaymentMethod

-------

Stephen Hendricks <happy@.londonfg.com> wrote in message news:<3fbba41e$0$202$75868355@.news.frii.net>...
> What error are you getting?
> You are mixing scalar and aggregate values, which is fine if you are
> grouping the data appropriately but erroneous otherwise.
> An error message would help us to help you...
> HTH
> Steve
> =======================================
> Everyone here speaks SQL; some are more fluent, others less. When
> describing your SQL object (table, etc.), do so in the language that we
> all understand - SQL, not English. It makes it easier to understand
> your issue and makes it more likely that you will get the assistance
> that you are asking for.
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Ah ha ! That does it. Knew it was something simple. Thanks for your help !

Chris Cheney <cjc1@.nospam%ucs.cam.ac.uk%no%spam%please> wrote in message news:<Xns9438ACB8BFE0Ccjc1nospamucscamacuk@.131.111.8.69>...
> ryanofford@.hotmail.com (Ryan) wrote in news:7802b79d.0311190628.73e93bb0
> @.posting.google.com:
> > I'm struggling with a Case statement. The problem I has is with doing
> >>= I can use any value in there, but need to check if it's greater or
> >>equal to 1. I'm sure I'm missing something but can't figure out what.
> > I've put the line below in case anyone has any suggestions. I've
> > limited it to the offending line, but can add more if needed.
> > This version works
> > ------
> > CASE m.Splinter_Status WHEN 'SUR' THEN 0 ELSE CASE m.Sacrifice WHEN 0
> > THEN SUM(3*m.Premium/100) - (Introducer *
> > (SUM(3*m.Premium)/100)/m.Estimated_Initial_Comm_Amt) ELSE 0 END END AS
> > Bond2,
> > This version fails
> > ------
> > CASE m.Splinter_Status WHEN 'SUR' THEN 0 ELSE CASE m.Sacrifice WHEN
> >>=1 THEN SUM(3*m.Premium/100) - (Introducer *
> > (SUM(3*m.Premium)/100)/m.Estimated_Initial_Comm_Amt) ELSE 0 END END AS
> > Bond2,
> From BOL:
> Syntax
> Simple CASE function:
> CASE input_expression
> WHEN when_expression THEN result_expression
> [...n]
> [
> ELSE else_result_expression
> ]
> END
> Searched CASE function:
> CASE
> WHEN Boolean_expression THEN result_expression
> [...n]
> [
> ELSE else_result_expression
> ]
> END
> Don't try to mix the two. Use the second form, i.e.
> ... CASE WHEN m.Sacrifice>=1 THEN ...
> HTH

Monday, February 13, 2012

%Disk Time value over 100

Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:eKaIHNaqFHA.4044@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I have collected counters using perfmon from our Production SQL Server.
> I have noticed that %Disk Time counter averaging 111 with maximum value
> being 2249 for particular drive. I was wondering how I detect whether this
> value indicates a bottleneck. Drive in question is RAID 10. Is there
> conversion formula I should use?
> I know that value over 60 indicates a potential problem. Average Disk
> Queue lenght is 1 which means to me that disk is OK. I am confused by high
> %Disk Time value.
>
> Any advice is appreciated,
> Igor
>
|||Jeffrey,
Thanks a lot!
Igor
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:22B02C35-4D78-47A1-B273-88ED2F5EDACA@.microsoft.com...[vbcol=seagreen]
> Used 100 - %disk idle time to get your utilization.
> "imarchenko" wrote:
|||Thanks, Geoff. I really appreciate your elaborate reply.
Igor
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:eSP65saqFHA.272@.TK2MSFTNGP15.phx.gbl...
> %Disk time is a useless counter on high-end IO systems. It was designed
> for sequential command IO systems, not current SCSI systems that support
> Command Tag Queuing or the new SATA Native Command Queuing options. These
> are the device-level commands that support scatter-gather IO. Short
> description is that they full-duplex command and response for IO. The
> device can queue a large number of IO requests, sort them optimally, and
> respond to them asychronously and asequentially. RAID subsystems further
> reduced the effectiveness of this counter buy abstracting a large number
> of physical devices into one logical device presented to the OS..
> I use Physical disk | Disk Read Bytes/sec, Disk Write Bytes/sec, Disk
> Reads/sec, Disk Writes/sec, and Disk Queue Length to determine if I am IO
> bound. Of course, you need to know the maximum capabilities of your IO
> subsystem to use these numbers effectively. But since you benchmarked the
> IO system with IOMeter when you built the server that is an easy
> comparison.

> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:eKaIHNaqFHA.4044@.TK2MSFTNGP09.phx.gbl...
>

%Disk Time value over 100

Hello!
I have collected counters using perfmon from our Production SQL Server. I
have noticed that %Disk Time counter averaging 111 with maximum value being
2249 for particular drive. I was wondering how I detect whether this value
indicates a bottleneck. Drive in question is RAID 10. Is there conversion
formula I should use?
I know that value over 60 indicates a potential problem. Average Disk Queue
lenght is 1 which means to me that disk is OK. I am confused by high %Disk
Time value.
Any advice is appreciated,
IgorUsed 100 - %disk idle time to get your utilization.
"imarchenko" wrote:
> Hello!
> I have collected counters using perfmon from our Production SQL Server. I
> have noticed that %Disk Time counter averaging 111 with maximum value being
> 2249 for particular drive. I was wondering how I detect whether this value
> indicates a bottleneck. Drive in question is RAID 10. Is there conversion
> formula I should use?
> I know that value over 60 indicates a potential problem. Average Disk Queue
> lenght is 1 which means to me that disk is OK. I am confused by high %Disk
> Time value.
>
> Any advice is appreciated,
> Igor
>
>|||%Disk time is a useless counter on high-end IO systems. It was designed for
sequential command IO systems, not current SCSI systems that support Command
Tag Queuing or the new SATA Native Command Queuing options. These are the
device-level commands that support scatter-gather IO. Short description is
that they full-duplex command and response for IO. The device can queue a
large number of IO requests, sort them optimally, and respond to them
asychronously and asequentially. RAID subsystems further reduced the
effectiveness of this counter buy abstracting a large number of physical
devices into one logical device presented to the OS..
I use Physical disk | Disk Read Bytes/sec, Disk Write Bytes/sec, Disk
Reads/sec, Disk Writes/sec, and Disk Queue Length to determine if I am IO
bound. Of course, you need to know the maximum capabilities of your IO
subsystem to use these numbers effectively. But since you benchmarked the
IO system with IOMeter when you built the server that is an easy comparison.
:)
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:eKaIHNaqFHA.4044@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I have collected counters using perfmon from our Production SQL Server.
> I have noticed that %Disk Time counter averaging 111 with maximum value
> being 2249 for particular drive. I was wondering how I detect whether this
> value indicates a bottleneck. Drive in question is RAID 10. Is there
> conversion formula I should use?
> I know that value over 60 indicates a potential problem. Average Disk
> Queue lenght is 1 which means to me that disk is OK. I am confused by high
> %Disk Time value.
>
> Any advice is appreciated,
> Igor
>|||Jeffrey,
Thanks a lot!
Igor
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:22B02C35-4D78-47A1-B273-88ED2F5EDACA@.microsoft.com...
> Used 100 - %disk idle time to get your utilization.
> "imarchenko" wrote:
>> Hello!
>> I have collected counters using perfmon from our Production SQL
>> Server. I
>> have noticed that %Disk Time counter averaging 111 with maximum value
>> being
>> 2249 for particular drive. I was wondering how I detect whether this
>> value
>> indicates a bottleneck. Drive in question is RAID 10. Is there conversion
>> formula I should use?
>> I know that value over 60 indicates a potential problem. Average Disk
>> Queue
>> lenght is 1 which means to me that disk is OK. I am confused by high
>> %Disk
>> Time value.
>>
>> Any advice is appreciated,
>> Igor
>>|||Thanks, Geoff. I really appreciate your elaborate reply.
Igor
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:eSP65saqFHA.272@.TK2MSFTNGP15.phx.gbl...
> %Disk time is a useless counter on high-end IO systems. It was designed
> for sequential command IO systems, not current SCSI systems that support
> Command Tag Queuing or the new SATA Native Command Queuing options. These
> are the device-level commands that support scatter-gather IO. Short
> description is that they full-duplex command and response for IO. The
> device can queue a large number of IO requests, sort them optimally, and
> respond to them asychronously and asequentially. RAID subsystems further
> reduced the effectiveness of this counter buy abstracting a large number
> of physical devices into one logical device presented to the OS..
> I use Physical disk | Disk Read Bytes/sec, Disk Write Bytes/sec, Disk
> Reads/sec, Disk Writes/sec, and Disk Queue Length to determine if I am IO
> bound. Of course, you need to know the maximum capabilities of your IO
> subsystem to use these numbers effectively. But since you benchmarked the
> IO system with IOMeter when you built the server that is an easy
> comparison. :)
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:eKaIHNaqFHA.4044@.TK2MSFTNGP09.phx.gbl...
>> Hello!
>> I have collected counters using perfmon from our Production SQL Server.
>> I have noticed that %Disk Time counter averaging 111 with maximum value
>> being 2249 for particular drive. I was wondering how I detect whether
>> this value indicates a bottleneck. Drive in question is RAID 10. Is there
>> conversion formula I should use?
>> I know that value over 60 indicates a potential problem. Average Disk
>> Queue lenght is 1 which means to me that disk is OK. I am confused by
>> high %Disk Time value.
>>
>> Any advice is appreciated,
>> Igor
>>
>

% remainder in SQL

In t-sql you can do the following to check if a value is odd or even. If it returns a 0 then the number is even, if it returns a 1 it is an odd number.
select @.Value%2

I need to do something similar in a backgroundColor expression in RS 2005, any ideas? I dont see any built-in function for this.

You could do something like this and just use the boolean result(pseudocode only, not tested)

(@.Value/2 = CInt(@.Value/2))

|||

Thanks csi_hugh

This works great!

% on Pie Charts

Hello all,
I am currently showing the value (hours) for each slice of the pie chart but
would like to also include the percentage of that pie slice. How would I do
this?Hi Anthonysjo,
SUM( Fields!Hours.Value )/ Sum(Fields!Hours.Value, "DataSet1")
then Format Code: P.
Or
Format(SUM( Fields!Hours.Value )/ Sum(Fields!Hours.Value, "DataSet1"),
"Percent")
I hope this is what you are looking for.
Kerrie
anthonysjo wrote:
>Hello all,
>I am currently showing the value (hours) for each slice of the pie chart but
>would like to also include the percentage of that pie slice. How would I do
>this?
Message posted via http://www.sqlmonster.com|||Kerrie,
thanks for the help, but I may need a little more explaination....would I
put this formula in the Chart properties, on the data tab, values, edit, and
add the code to the Value field? Also is Dataset1 the name of the datasource
or the name of the output field in the example you gave below?
Thanks for the help!!
Anthony
"Kerrie S via SQLMonster.com" wrote:
> Hi Anthonysjo,
> SUM( Fields!Hours.Value )/ Sum(Fields!Hours.Value, "DataSet1")
> then Format Code: P.
> Or
> Format(SUM( Fields!Hours.Value )/ Sum(Fields!Hours.Value, "DataSet1"),
> "Percent")
> I hope this is what you are looking for.
> Kerrie
> anthonysjo wrote:
> >Hello all,
> >
> >I am currently showing the value (hours) for each slice of the pie chart but
> >would like to also include the percentage of that pie slice. How would I do
> >this?
>
> --
> Message posted via http://www.sqlmonster.com
>|||That expression would go under data tab -> edit values -> point labels
tab -> data label expression
"Dataset1" is an explicit scope for the Sum aggregation function and has to
be identical to the data set name the chart is based on.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
news:ABFE935A-7E47-4E31-BD69-578D29F1F29C@.microsoft.com...
> Kerrie,
> thanks for the help, but I may need a little more explaination....would I
> put this formula in the Chart properties, on the data tab, values, edit,
> and
> add the code to the Value field? Also is Dataset1 the name of the
> datasource
> or the name of the output field in the example you gave below?
> Thanks for the help!!
> Anthony
> "Kerrie S via SQLMonster.com" wrote:
>> Hi Anthonysjo,
>> SUM( Fields!Hours.Value )/ Sum(Fields!Hours.Value, "DataSet1")
>> then Format Code: P.
>> Or
>> Format(SUM( Fields!Hours.Value )/ Sum(Fields!Hours.Value, "DataSet1"),
>> "Percent")
>> I hope this is what you are looking for.
>> Kerrie
>> anthonysjo wrote:
>> >Hello all,
>> >
>> >I am currently showing the value (hours) for each slice of the pie chart
>> >but
>> >would like to also include the percentage of that pie slice. How would
>> >I do
>> >this?
>>
>> --
>> Message posted via http://www.sqlmonster.com|||I put in the following.....
=SUM( Fields!Reg_Hours.Value )/ Sum(Fields!Reg_Hours.Value, " YRT_Proj_Cat")
Then I ran the report and get the following error:
The expression for the chart â'chart1â' has a scope parameter that is not
valid for an aggregate function. The scope parameter must be set to a string
constant that is equal to either the name of a containing group, the name of
a containing data region, or the name of a data set.
Build complete
What am I missing...Reg_Hours is the field name and YRT_Proj_Cat is the
dataset?
Sorry if I am a little slow...I am new to SRS!
Thanks for the help thus far!!!
"Robert Bruckner [MSFT]" wrote:
> That expression would go under data tab -> edit values -> point labels
> tab -> data label expression
> "Dataset1" is an explicit scope for the Sum aggregation function and has to
> be identical to the data set name the chart is based on.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
> news:ABFE935A-7E47-4E31-BD69-578D29F1F29C@.microsoft.com...
> > Kerrie,
> >
> > thanks for the help, but I may need a little more explaination....would I
> > put this formula in the Chart properties, on the data tab, values, edit,
> > and
> > add the code to the Value field? Also is Dataset1 the name of the
> > datasource
> > or the name of the output field in the example you gave below?
> >
> > Thanks for the help!!
> >
> > Anthony
> > "Kerrie S via SQLMonster.com" wrote:
> >
> >> Hi Anthonysjo,
> >> SUM( Fields!Hours.Value )/ Sum(Fields!Hours.Value, "DataSet1")
> >> then Format Code: P.
> >> Or
> >> Format(SUM( Fields!Hours.Value )/ Sum(Fields!Hours.Value, "DataSet1"),
> >> "Percent")
> >> I hope this is what you are looking for.
> >> Kerrie
> >>
> >> anthonysjo wrote:
> >> >Hello all,
> >> >
> >> >I am currently showing the value (hours) for each slice of the pie chart
> >> >but
> >> >would like to also include the percentage of that pie slice. How would
> >> >I do
> >> >this?
> >>
> >>
> >> --
> >> Message posted via http://www.sqlmonster.com
> >>
>
>|||Scope names are case-sensitive and they must exactly match the data set
name. The scope name in the expressions starts with a space which is most
likely the reason for the error message. Try it without the leading space in
the scope name:
=SUM( Fields!Reg_Hours.Value )/ Sum(Fields!Reg_Hours.Value, "YRT_Proj_Cat")
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
news:AE6D597D-D4CF-4EEB-9EFB-A223A8734D27@.microsoft.com...
>I put in the following.....
> =SUM( Fields!Reg_Hours.Value )/ Sum(Fields!Reg_Hours.Value, "
> YRT_Proj_Cat")
> Then I ran the report and get the following error:
> The expression for the chart 'chart1' has a scope parameter that is not
> valid for an aggregate function. The scope parameter must be set to a
> string
> constant that is equal to either the name of a containing group, the name
> of
> a containing data region, or the name of a data set.
> Build complete
> What am I missing...Reg_Hours is the field name and YRT_Proj_Cat is the
> dataset?
> Sorry if I am a little slow...I am new to SRS!
> Thanks for the help thus far!!!
> "Robert Bruckner [MSFT]" wrote:
>> That expression would go under data tab -> edit values -> point labels
>> tab -> data label expression
>> "Dataset1" is an explicit scope for the Sum aggregation function and has
>> to
>> be identical to the data set name the chart is based on.
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
>> news:ABFE935A-7E47-4E31-BD69-578D29F1F29C@.microsoft.com...
>> > Kerrie,
>> >
>> > thanks for the help, but I may need a little more
>> > explaination....would I
>> > put this formula in the Chart properties, on the data tab, values,
>> > edit,
>> > and
>> > add the code to the Value field? Also is Dataset1 the name of the
>> > datasource
>> > or the name of the output field in the example you gave below?
>> >
>> > Thanks for the help!!
>> >
>> > Anthony
>> > "Kerrie S via SQLMonster.com" wrote:
>> >
>> >> Hi Anthonysjo,
>> >> SUM( Fields!Hours.Value )/ Sum(Fields!Hours.Value, "DataSet1")
>> >> then Format Code: P.
>> >> Or
>> >> Format(SUM( Fields!Hours.Value )/ Sum(Fields!Hours.Value, "DataSet1"),
>> >> "Percent")
>> >> I hope this is what you are looking for.
>> >> Kerrie
>> >>
>> >> anthonysjo wrote:
>> >> >Hello all,
>> >> >
>> >> >I am currently showing the value (hours) for each slice of the pie
>> >> >chart
>> >> >but
>> >> >would like to also include the percentage of that pie slice. How
>> >> >would
>> >> >I do
>> >> >this?
>> >>
>> >>
>> >> --
>> >> Message posted via http://www.sqlmonster.com
>> >>
>>|||Sweet!! That was it...the space was causing the problem. Thanks for the
help!!!
"Robert Bruckner [MSFT]" wrote:
> Scope names are case-sensitive and they must exactly match the data set
> name. The scope name in the expressions starts with a space which is most
> likely the reason for the error message. Try it without the leading space in
> the scope name:
> =SUM( Fields!Reg_Hours.Value )/ Sum(Fields!Reg_Hours.Value, "YRT_Proj_Cat")
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
> news:AE6D597D-D4CF-4EEB-9EFB-A223A8734D27@.microsoft.com...
> >I put in the following.....
> >
> > =SUM( Fields!Reg_Hours.Value )/ Sum(Fields!Reg_Hours.Value, "
> > YRT_Proj_Cat")
> >
> > Then I ran the report and get the following error:
> >
> > The expression for the chart 'chart1' has a scope parameter that is not
> > valid for an aggregate function. The scope parameter must be set to a
> > string
> > constant that is equal to either the name of a containing group, the name
> > of
> > a containing data region, or the name of a data set.
> > Build complete
> >
> > What am I missing...Reg_Hours is the field name and YRT_Proj_Cat is the
> > dataset?
> >
> > Sorry if I am a little slow...I am new to SRS!
> >
> > Thanks for the help thus far!!!
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> >> That expression would go under data tab -> edit values -> point labels
> >> tab -> data label expression
> >>
> >> "Dataset1" is an explicit scope for the Sum aggregation function and has
> >> to
> >> be identical to the data set name the chart is based on.
> >>
> >> -- Robert
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
> >> news:ABFE935A-7E47-4E31-BD69-578D29F1F29C@.microsoft.com...
> >> > Kerrie,
> >> >
> >> > thanks for the help, but I may need a little more
> >> > explaination....would I
> >> > put this formula in the Chart properties, on the data tab, values,
> >> > edit,
> >> > and
> >> > add the code to the Value field? Also is Dataset1 the name of the
> >> > datasource
> >> > or the name of the output field in the example you gave below?
> >> >
> >> > Thanks for the help!!
> >> >
> >> > Anthony
> >> > "Kerrie S via SQLMonster.com" wrote:
> >> >
> >> >> Hi Anthonysjo,
> >> >> SUM( Fields!Hours.Value )/ Sum(Fields!Hours.Value, "DataSet1")
> >> >> then Format Code: P.
> >> >> Or
> >> >> Format(SUM( Fields!Hours.Value )/ Sum(Fields!Hours.Value, "DataSet1"),
> >> >> "Percent")
> >> >> I hope this is what you are looking for.
> >> >> Kerrie
> >> >>
> >> >> anthonysjo wrote:
> >> >> >Hello all,
> >> >> >
> >> >> >I am currently showing the value (hours) for each slice of the pie
> >> >> >chart
> >> >> >but
> >> >> >would like to also include the percentage of that pie slice. How
> >> >> >would
> >> >> >I do
> >> >> >this?
> >> >>
> >> >>
> >> >> --
> >> >> Message posted via http://www.sqlmonster.com
> >> >>
> >>
> >>
> >>
>
>

% disktime

In NT4 this counter returned a value between 0 and 100. In 2000, the number
exceeeds 100. What is it acuatlly counting and why would a percentage stat
return more than 100?
I find that counter to be basically useless these days. I prefer the avg and
current disk queues for an indication of activity ove rthat.
Andrew J. Kelly SQL MVP
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:F7733806-7CB4-4D43-B20A-0F0E5AA6AAD2@.microsoft.com...
> In NT4 this counter returned a value between 0 and 100. In 2000, the
> number
> exceeeds 100. What is it acuatlly counting and why would a percentage
> stat
> return more than 100?
|||http://support.microsoft.com/default...310067&sd=tech
"Jeffrey K. Ericson" wrote:

> In NT4 this counter returned a value between 0 and 100. In 2000, the number
> exceeeds 100. What is it acuatlly counting and why would a percentage stat
> return more than 100?
|||click on the properties for that counter, your vertical scale is probably not
set to 100
"Jeffrey K. Ericson" wrote:

> In NT4 this counter returned a value between 0 and 100. In 2000, the number
> exceeeds 100. What is it acuatlly counting and why would a percentage stat
> return more than 100?

% disktime

In NT4 this counter returned a value between 0 and 100. In 2000, the number
exceeeds 100. What is it acuatlly counting and why would a percentage stat
return more than 100?I find that counter to be basically useless these days. I prefer the avg and
current disk queues for an indication of activity ove rthat.
Andrew J. Kelly SQL MVP
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:F7733806-7CB4-4D43-B20A-0F0E5AA6AAD2@.microsoft.com...
> In NT4 this counter returned a value between 0 and 100. In 2000, the
> number
> exceeeds 100. What is it acuatlly counting and why would a percentage
> stat
> return more than 100?|||http://support.microsoft.com/defaul...;310067&sd=tech
"Jeffrey K. Ericson" wrote:

> In NT4 this counter returned a value between 0 and 100. In 2000, the numb
er
> exceeeds 100. What is it acuatlly counting and why would a percentage sta
t
> return more than 100?|||click on the properties for that counter, your vertical scale is probably no
t
set to 100
"Jeffrey K. Ericson" wrote:

> In NT4 this counter returned a value between 0 and 100. In 2000, the numb
er
> exceeeds 100. What is it acuatlly counting and why would a percentage sta
t
> return more than 100?

% disktime

In NT4 this counter returned a value between 0 and 100. In 2000, the number
exceeeds 100. What is it acuatlly counting and why would a percentage stat
return more than 100?I find that counter to be basically useless these days. I prefer the avg and
current disk queues for an indication of activity ove rthat.
--
Andrew J. Kelly SQL MVP
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:F7733806-7CB4-4D43-B20A-0F0E5AA6AAD2@.microsoft.com...
> In NT4 this counter returned a value between 0 and 100. In 2000, the
> number
> exceeeds 100. What is it acuatlly counting and why would a percentage
> stat
> return more than 100?|||http://support.microsoft.com/default.aspx?scid=kb;en-us;310067&sd=tech
"Jeffrey K. Ericson" wrote:
> In NT4 this counter returned a value between 0 and 100. In 2000, the number
> exceeeds 100. What is it acuatlly counting and why would a percentage stat
> return more than 100?|||click on the properties for that counter, your vertical scale is probably not
set to 100
"Jeffrey K. Ericson" wrote:
> In NT4 this counter returned a value between 0 and 100. In 2000, the number
> exceeeds 100. What is it acuatlly counting and why would a percentage stat
> return more than 100?

Saturday, February 11, 2012

#value in Cube Calculation

Hello all

GIven the following calculation

([Measures].[12 Month Total Sales],[Sales History].[Sales YM].[Year].lag(1))

were 12 Month Total Sales is a measure of Sale History why do I get #value for this calculation ?

Thanks for your assistance

My guess is that [Sales History].[Sales YM].[Year] is a level, and lag() only works on a member. Does this do what you want?

([Measures].[12 Month Total Sales],[Sales History].[Sales YM].currentmember.lag(1))

Chris

|||thank you

Sunday, March 25, 2012

(nothing = 0) = true !

Thursday, March 22, 2012

((0)) set as Default Value or Binding for an int Data Type

Monday, March 19, 2012

"Static variable" in function

Sunday, March 11, 2012

"One or more DataSources is missing credentials"

Thursday, March 8, 2012

"Less Than or Equal To" MDX query

Saturday, February 25, 2012

"Cannot apply value null to property Login: Value cannot be null..".

"Cannot apply value null to property Login: Value cannot be null..".

Friday, February 24, 2012

"Cannot apply value null to property Login: Value cannot be null..".

"Cannot apply value null to property Login: Value cannot be null..".

Sunday, February 19, 2012

<Select all> disappear on server

Thursday, February 16, 2012

>= in Case Statement - Stuck

Monday, February 13, 2012

%Disk Time value over 100

%Disk Time value over 100

%Disk Time value over 100

% remainder in SQL

% on Pie Charts

% disktime

% disktime

% disktime

Saturday, February 11, 2012

#value in Cube Calculation

SQL,tables in a DB

Blog Archive

About Me