Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

CustomRollupColumn and aggregates Expand / Collapse
Author
Message
Posted Sunday, September 28, 2008 2:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Sunday, September 28, 2008 7:10 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:16 AM
Points: 36,753, Visits: 31,209
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #577493
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse