MS SQL Server OLAP - Getting #ERR in my cell

Author

Getting #ERR in my cell

mzlamal

2005-12-19, 1:23 pm

Hello All,

I am relatively new to Analysis Services (using MS SQL, Analysis Services
2000).

I am playing with the "FoodMart 2000" database, cube is "HR". I have
created a simple MDX query, but the cell only shows #ERR, and the error
message is:

"Formula error- the aggregate function cannot operate on calculated measure
"test" - in the Aggregate function. An error occured during attempt to get a
cell value."

The MDX query is:

WITH MEMBER [Measures].test AS
'aggregate({[Measures]})'
SELECT
{test} ON COLUMNS
FROM
{HR}

The query itself probably does not show any meaningful results, but I cannot
seem to get *any* query to run when I use the keyword "aggregate" within it.
Is this aggregate function supported in sql 2000 (and Analysis services 2000)?

Thanks in advance.

--
Mark Zlamal

Darren Gosbell

2005-12-20, 7:23 am

Have you thought about getting a book on MDX like "MDX solutions" or
"Fasttrack to MDX"? The sample query you have provided does not really
make sense and I cannot figure out what you were trying to do.

I think what you have asked for is to add all the measures in the HR
cube together , so if your query did work you would get the sum of "Org
Salary" + "Count" + "Number of Employees" + "Employee Salary" + "Avg
Salary" + Test.

Note that because you have created Test as a measure it is trying to
recursively add itself.

Aggregate() does work, but normal measure aggregate without having to
create a calculated member. If you wanted to see all the measures for
each employee you would do something like the following

SELECT
Measures.AllMembers ON COLUMNS,
Employees.Members ON ROWS
FROM HR

I hope this helps

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <32139268-3B03-435F-BBE6- F8AD86EFDC55@microso
ft.com>,
mzlamal@discussions.microsoft.com says...
> Hello All,
>
> I am relatively new to Analysis Services (using MS SQL, Analysis Services
> 2000).
>
> I am playing with the "FoodMart 2000" database, cube is "HR". I have
> created a simple MDX query, but the cell only shows #ERR, and the error
> message is:
>
> "Formula error- the aggregate function cannot operate on calculated measure
> "test" - in the Aggregate function. An error occured during attempt to get a
> cell value."
>
> The MDX query is:
>
> WITH MEMBER [Measures].test AS
> 'aggregate({[Measures]})'
> SELECT
> {test} ON COLUMNS
> FROM
> {HR}
>
>
> The query itself probably does not show any meaningful results, but I cannot
> seem to get *any* query to run when I use the keyword "aggregate" within it.
> Is this aggregate function supported in sql 2000 (and Analysis services 2000)?
>
> Thanks in advance.
>
>

Sponsored Links