CustomRollupColumn and aggregates

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Vincent Rainardi » CustomRollupColumn and aggregates

CustomRollupColumn and aggregates

Rate Topic

Display Mode

Topic Options

Author

Message

kaimu_boy

Posted Sunday, September 28, 2008 2:18 AM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, September 28, 2008 2:50 AM
Points: 1, Visits: 4

How to Sets CustomRollupColumn's Express to get data as follow:
DimItem
Year month week ItemName
2008 1 1 100.10
2008 1 2 100.20
2008 1 3 100.40
2008 1 4 100.90
2008 1 sum 100.10
2008 2 1 100.50
2008 2 2 100.20
2008 2 3 100.40
2008 2 4 100.90
2008 2 sum 100.50
2008 sum 100.10

I use the express as follow, but the cell show null

case
when [Date].[CalendarDate].CurrentMember.Level.Ordinal = 0
then
null
when [Date].[CalendarDate].CurrentMember.Level.Ordinal = 1
then
([Measures].[Amount],[AmountItem].[FinanceSubjectItem].&[1],[Date].[CalendarDate].CurrentMember.FirstChild.FirstChild.FirstChild)
when [Date].[CalendarDate].CurrentMember.Level.Ordinal = 2
then
([Measures].[Amount],[AmountItem].[FinanceSubjectItem].&[1],[Date].[CalendarDate].CurrentMember.FirstChild.FirstChild)
when [Date].[CalendarDate].CurrentMember.Level.Ordinal = 3
then
([Measures].[Amount],[AmountItem].[FinanceSubjectItem].&[1],[Date].[CalendarDate].CurrentMember.FirstChild)
else
([Measures].[Amount],[AmountItem].[FinanceSubjectItem].&[1],[Date].[CalendarDate].CurrentMember)
end

Post #577376

Jeff Moden

Posted Sunday, September 28, 2008 7:10 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:13 AM
Points: 32,906, Visits: 26,793

You'd get better help quicker if you'd follow the methods in the article in the link in my signature below... ;)

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #577493

« Prev Topic | Next Topic »

Permissions