Showing posts with label ending. Show all posts
Showing posts with label ending. Show all posts

Tuesday, March 20, 2012

Balance Forwarding

Hi,

Just want to seek advice on how to forward the ending balance of Balance Sheet Accounts of previous year to the current year.

Marvs

You can use a couple of MDX function depending on the level you are standed.

If we have the following time heriarchie:

[DimTime].[Year-Month-Day-H] = [Year], [Month], [Day]

And assuming that the [Balance] measure is additive

1) if you drill down to the day level you can obtain the ending balance of the previous year thorugh the following MDX sentence:

(ParallelPeriod([Year],1,[Dim Time].Currentmember).Parent.Lastsibling.Lastchild, measure.[Balance])

2) If you drill down to the month level you can obtain the ending balance of the previous year like this:

(ParallelPeriod([Year],1,[Dim Time].Currentmember).Lastsibling.Lastchild, measure.[Balance])

3) If you are on the year level you can obtain the ending balance of the previous year like this:

([Dim Time].currentmember.Lag(1).Lastchild.Lastchild, measure.[Balance])

And finally you can put the three sentences in two nested iif function, like this:

Iif([DimTime].currentmember.Level.Name = "Day", <Sentence 1>,

Iif([DimTime].currentmember.Level.Name = "Month", <Sentence 2>,

<Sentence 3>))

Other ways using custom rollup formulas can be used.

Hope that helps you

Leandro

|||

What I think you are looking for is the new semi-additive measures in AS2K5, specifically the "last non-empty child" aggregation function. It is designed for balances, quantities on-hand, and a whole class of semi-additive situatons. While this is technically possible on AS2K, it involves a considerable amount of calculations and MDX to evaluate it at runtime. In AS2K5, it is a native aggregation function and performs at the same speed as sum, count, etc.

_-_-_ Dave

|||

That' right.

So the following more simple sentence could be used:

(parallelperiod([Year],1,[DimTime].currentmember), measures.[Balance])

Thanks

bad scripts within EM Taskpad

For some of my databases, when I want to use the Taskpad
view within EM I get an Internet Explorer script error on
the URL ending sqlmmc.rll/Tabs.html
I'm using SQL2000 on the client workstation. The Server
is 7.0. Some of the databases on that server dispaly
fine, some have this problem.
I have re-installed SQL to no avail. Thining about
reinstalling Explorer. Any other ideas? Thanks Mikein EM:
View>>large Icons
then,
View>>Taskpad
Known bug in EM
--
Kevin3NF
Sick of all that junk filling up your mailbox?
http://spamarrest.com/affl?2967001
"Mike Hoyt" <mhoyt@.affiliatedhealth.org> wrote in message
news:07d401c3c8c5$c173f360$a001280a@.phx.gbl...
> For some of my databases, when I want to use the Taskpad
> view within EM I get an Internet Explorer script error on
> the URL ending sqlmmc.rll/Tabs.html
> I'm using SQL2000 on the client workstation. The Server
> is 7.0. Some of the databases on that server dispaly
> fine, some have this problem.
> I have re-installed SQL to no avail. Thining about
> reinstalling Explorer. Any other ideas? Thanks Mike|||Kevin, Thank you so much! works like a charm.
>--Original Message--
>in EM:
>View>>large Icons
>then,
>View>>Taskpad
>Known bug in EM
>--
>Kevin3NF
>|||Change the view to something other than "Task" then select
the "Task" view again to view the Task Pane.
>--Original Message--
>For some of my databases, when I want to use the Taskpad
>view within EM I get an Internet Explorer script error on
>the URL ending sqlmmc.rll/Tabs.html
>I'm using SQL2000 on the client workstation. The Server
>is 7.0. Some of the databases on that server dispaly
>fine, some have this problem.
>I have re-installed SQL to no avail. Thining about
>reinstalling Explorer. Any other ideas? Thanks Mike
>.
>|||Thanks. That's been driving me insane since we started
testing SQL 2000. Has anyone heard if Microsoft is going
to correct this? -Phil-
>--Original Message--
>Change the view to something other than "Task" then
select
>the "Task" view again to view the Task Pane.
>>--Original Message--
>>For some of my databases, when I want to use the Taskpad
>>view within EM I get an Internet Explorer script error
on
>>the URL ending sqlmmc.rll/Tabs.html
>>I'm using SQL2000 on the client workstation. The Server
>>is 7.0. Some of the databases on that server dispaly
>>fine, some have this problem.
>>I have re-installed SQL to no avail. Thining about
>>reinstalling Explorer. Any other ideas? Thanks Mike
>>.
>.
>