Showing posts with label level. Show all posts
Showing posts with label level. Show all posts

Wednesday, March 28, 2012

Latest build version?

Hi,

What is the latest patch level for SQL server 2000?

I am currently at this build level:

Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

How can I tell when microsoft bring out a new SQL server hotfix or patch?Check out http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=37. I think that they keep the list updated, but I could not tell you how frequently.

Note that with some of the patches that come out, you have to patch the client tools as well as the server.

Regards,

hmscott

Hi,

What is the latest patch level for SQL server 2000?

I am currently at this build level:

Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

How can I tell when microsoft bring out a new SQL server hotfix or patch?

Monday, March 26, 2012

LastPeriods() Function producing incorrect results

I am using the LastPeriods Function to SUM the Last 12 periods at a month level:

MEMBER [Measures].[Last 12 Months] As
'SUM(LastPeriods(12, [Time].[Calendar Year].[Year].[2006].[Q4-06].&[December]), [Measures].[Total TEUS]) '

This should equal out to be [Time].[Calendar Year].[Year].[2006] but for some reason, the totals are different.

I thought it might be an issue with solve order so I took all other calculations out and it is still incorrect.
I am not sure what to look for now in fixing this problem. Is my syntax structure wrong here? Hopefully it is something small I am missing here.

Regards,

-Troy

What is the aggregation function for [Measures].[Total TEUS] - is it a "sum" measure?|||

Deepak,

Actually, this issue was a user error on my part. Turns out I was comparing it with a different Time Dimension.

The numbers are actually coming out correctly. Sorry for that. The problem I am seeing now is when using this member and a SET for the TopCount() function.

For Example:

WITH

MEMBER [Measures].[LastPeriod] AS
'SUM(LastPeriods(12, [Time].[Calendar Year].[Year].&[2006].&[Q4-06].&[December]), [Measures].[Total TEUS]) '

SET [TopShipline] AS
'NONEMPTY({TOPCOUNT([Ship Line].[Shiplines].[SHIPLINE].Members, 5, [Measures].CurrentMember)})'

SELECT {[Measures].[LastPeriod]} ON COLUMNS,
[TopShipline] ON ROWS
From [Account Level Aggregates]
WHERE ([Domestic Companies].[Domestic Company].[Company Name].[Some Company])

The results are as follows:

LastPeriod
ShiplineA 772.0
ShiplineB 7,738.5
ShiplineC 3,976.7
ShiplineD 2,158.1
ShiplineE 4,707.9

ShiplineA should not be in the results here. If I change to TopCount count to 6. The result is correct, minus ShiplineA.

LastPeriod
ShiplineA 772.0
ShiplineB 7,738.5
ShiplineC 3,976.7
ShiplineD 2,158.1
ShiplineE 4,707.9
ShiplineF 1,660.0

TopCount() for other dimensions that use this time dimension have similar results. This is only when using LastPeriods().

If I use say:

SET [TopPorts] AS
'NONEMPTY({TOPCOUNT([US Port].[US Port Name].Children, 5, [Measures].CurrentMember)})'

LastPeriod
PortA 2,690.7
PortB 5,036.4
PortC 6,124.4
PortD 5,542.2
PortE 513.7

In this case, PortE does not belong and the sort order of my measure is not as it should be. Again, changing the count to 6 from five brings the correct fifth Port, PortF

|||

Let me add one last piece to this and this is this apears to only be an issue when my [Domestic Company] dimension is used in the where clause:

WHERE ([Domestic Companies].[Domestic Company].[Company Name].[Some Company])

If I use a different dimension in the WHERE clause, the results are correct.

This is a somewhat large, flat dimension with about 600,000 members.

|||

Keeping in mind that the named set will be computed only in the context of the "where" slicer axis, which doesn't include a measure, you should explicitly specify [Total TEUS], like:

SET [TopShipline] AS
'NONEMPTY(TOPCOUNT([Ship Line].[Shiplines].[SHIPLINE].Members, 5, [Measures].[Total TEUS]))'

|||

Thanks for reply. I have resolved this. What I needed to do was use the Measure for my TopCount() function that I created as my calculated member.

MEMBER [Measures].[Last 12 Months] AS

'..........'

So, instead of:

SET [TopShipline] AS

'NONEMPTY( {TOPCOUNT( [Ship Line].[Shiplines].Children, 5, [Measures].[Total TEUS])})'

I needed to use:

SET [TopShipline] AS

'NONEMPTY( {TOPCOUNT( [Ship Line].[Shiplines].Children, 5, [Measures].[Last 12 Months])})'

Monday, March 19, 2012

Last Month-to-Last-Child

I am trying to write a calculation to give a Last Month reference, I use the following MDX and it works fine for the Day level

[Time].[Month Comparison].DefaultMember,
ParallelPeriod(
[Time].[Date with Month].[Month], 1,
[Time].[Date with Month].CurrentMember
)

However for Month level, I do not want it to reference to the last month value directly. What I want is, for example, today is "7th May 2007", I want the Last Month Reference for "May 2007 " to be the sum of "1st Apr 2007" to "7th Apr 2007" instead of the sum of the whole "Apr 2007".

Does anyone have any idea on how to archive this? Thanks in advance!

Assuming that all days in a month till the last day with data should be included, something like:

Aggregate(

CrossJoin({[Time].[Month Comparison].DefaultMember},
PeriodsToDate([Time].[Date with Month].CurrentMember.Level,

ParallelPeriod([Time].[Date with Month].[Month], 1,
Tail(NonEmpty(Descendants([Time].[Date with Month].CurrentMember,
[Time].[Date with Month].[Day])).Item(0).Item(0))))))

Monday, March 12, 2012

Last Function

Access has a last function available for when you are grouping on a summary level. What this does is return the last record for that particular grouping. Does sql have function of that nature?

Example:

Item_Code Time
123456 11:40
123456 11:41
123456 11:42

By grouping on item code and using the last function on the Time column, the third record would be returned.

Item_Code Time
123456 11:42

All, help is appreciated.Last is not as reliable as you may think, and SQL Server doesn't have it. Try Max instead.|||well, i really cannot use max because it will give me a max each cloumn. For example:

Item Date Time
12345 7/10/06 11:21
12345 7/10/06 11:45
12345 7/12/06 09:45

If i group on item and use max for date and time i would get

Item Date Time
12345 7/12/06 11:45

I basically need to find a way o get the last record of a set.

Maybe there is a better way to approach this.|||Last would not behave any differently in Access, so I'm not sure why you asked for the replacement for Last. One way is to join date and time together in one field and do your max on that.|||Last would not behave any differently in Access, so I'm not sure why you asked for the replacement for Last. One way is to join date and time together in one field and do your max on that.

I think that would probbaly be my best bet. Thanks for all your help.|||No problem. BTW, here's more info on why First/Last are not necessarily reliable anyway:

http://support.microsoft.com/kb/208190/en-us|||No problem. BTW, here's more info on why First/Last are not necessarily reliable anyway:

http://support.microsoft.com/kb/208190/en-us

Sweet, I really appreciate all the help and notes.