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



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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:53 AM
Points: 20,156, Visits: 13,694
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, 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 Expand / Collapse