Hi there everyone

I have a table in SQL that holds an opening balance. nd then holds the
movements/posting for a time period. It does not give a closing balance. How
would I go about doing this in Analysis Services. I don't want the balance as
at the end of today but also for historical months/days.

Any ides please as I'm desperate.

Do you have a cube with a time dimension ?

then you shouldnt have to have any aggregations in the SQL table - you can
just make Analysis Services make the aggregations for you in the cube so
that
the user will see the balance per choice of date in the time dimension.

in order to do this you may need to create some calculated measures in the
cube such as

sum(ytd(),([Measures].[Beløb]))

....you should change "Beløb" to the measure name in your cube.

There's a lot of these date functions and you can make a periodicity
dimension as well - a bit more complicated though....

"Granville" <Granville (AT) discussions (DOT) microsoft.com> wrote

Hi there

Thank you for the response. Yes, I have a time dimension. However, the YTD
feature will not solve my problem.

I am trying to build a Balance Sheet and for some stupid reason we have
Sales and Credit Notes posted in the system for FUTURE dates.
Meaning, that if I do a Balance Sheet for Feb 2005 and I use the YTD or MTD
feature that it will pick up those future periods and I don't want to that to
happen. It must only pick up anything dates going back up untill current date.

Any ideas would be very welcome PLEASE as I am desperate,


"Granville" wrote:

I don't quiet get it - you are aware that the MDX YTD function will give you
the balance
sheet per choice of date in the time dimension ?

That means if the user selects feb 2005 data will only be aggregated per
this data - despite the
fact there there are transactions in march 2005.

Doesnt see how that could conflict with the description below.

If its because you want to change which periode certain transactions are
placed in I think you
should consider other types of time dimension hierachies or changes in the
way you're pulling
data out of the host system.


"Granville" <Granville (AT) discussions (DOT) microsoft.com> wrote

If you need to compute the current date, the VBA!Now() function can be
used, with suitable formatting like:

'Sum(YTD(StrToMember("[Time].["
+ CStr(Year(Now())) + "].[Q"
+ CStr(CInt((Month(Now())-1)/3)+1) + "].["
+ CStr(Month(Now())) + "]")),
[Measures].[Balance])'

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi Gran,

If the data is not chaning often, it would be better to create three
measures in fact table:
opening_balance
MTD
YTD

Using sprocs, calculate YTD properly (op_bal+sum(Fiscal year start MTD
till current month MTD), and load it in YTD column.

It has benefit of calculating YTD once and just reading in cube instead
of calculating it dynamically every time. Also, the performance of the
report would be boosted.

Ajit Singh
www.hostanalytics.com

Then you're granularity of the fact cannot be transactions...?

You can have transactions in the fact and still get this information using
either time dimension or mdx data functions as deepak described.

In this way you can add drill through to detail transactions if desired.

"Ajit Singh" <ajit555 (AT) yahoo (DOT) com> wrote