SQL Server Analysis Services 2008 and ByAccount Aggregations

Question

Hi

I am having problems getting the 'ByAccount' aggregation to work in SQL Server Analysis Services (SSAS) 2008.

I am trying to create a financial reporting solution, with Balance Sheet and Profit and Loss elements.

I have a fact table with figures at a month level of granularity. There is one measure I am interested in on this table currently, "Reporting Amount" and the AggregateFunction property is set to "ByAccount".

I have a time dimension consisting of Year and Period, with Year set to "ReportingYear" type and Period set to "ReportingMonthOfYear".

I have an "Account" dimension, consisting of the following attributes: AccountId (set to type AccountNumber), AccountType (set to AccountType), ParentAccountId (set to type Account an usage Parent).

If I go to "Edit Database", I can see that there are appropriate Account Type mappings set up and the Balance sheet types are set to "LastNonEmpty".

However, the ByAccount simply isn't taking effect, I am not getting anything aggregating with "LastNonEmpty". What am I missing?

Thanks in advance.

Answer 1

I eventually discovered that the problem seemed to be the fact that my AccountType column was an integer Id. Changing this to a string containing actual account type description such as "Asset", "Liability" etc. solved the problem.