I try to make a calculated cell in SSAS 2000, in the standard foodmart 2000 example.
I try a technique from the following page:
http://msdn2.microsoft.com/en-us/library/aa902637(SQL.80).aspx
For instance, I want to know how many married people there are in a salarygroup in the cube 'Sales'.
I define the following MDX instruction for the calculated member "Married customers":
Count(CrossJoin( {[Marital Status].[M]},
Descendants([Customers].CurrentMember, [Customers].[(All)])), excludeempty)
I get nothing but errors when browsing the cube. Every cell states #ERR
When I do not define 'excludeempty' everything's fine.
How come?
Thanks in advance
I have to guess here, because you don't specify the entire statement, but I am pretty sure that the #ERR that you are getting is "Infinite recursion" one. Which dimension did you define the "Married customers" calculated member ? If in measures - you will get this error. So either you move the calculated member to the Customers dimension, or you explicitly qualify your set with one of the cube measures for which you want to do math.|||Yes, I have defined it in measures.
As I, for instance, execute the following query in the MDX Sample Application (in the foodmart2000 example) I get these errors too:
WITH MEMBER [Measures].[allcustom] AS 'COUNT([Customers].[Name].MEMBERS, excludeEmpty)'
SELECT
[Gender].MEMBERS ON COLUMNS,
[Yearly Income].MEMBERS ON ROWS
FROM Sales
WHERE [Measures].[allcustom]
If I, for instance, make two calculated cells where I calculate the amount of male and female customers, like as follows:
COUNT(NonEmptyCrossjoin(
{[Customers].[Name].MEMBERS},
{ [Gender].[Gender].&[M] }
))
COUNT(NonEmptyCrossjoin(
{[Customers].[Name].MEMBERS},
{ [Gender].[Gender].&[F] }
))
And I execute the following query in the MDX sample application:
SELECT
[Yearly Income].MEMBERS ON COLUMNS,
{ [Measures].[Male customers], [Measures].[female customers]} ON ROWS
FROM Sales
With this, I get a similar result as what I wanted in first query in this post.
But I do not want to make calculated cells for every member of a dimension.
But how do I make one calculated cell for that?
Thanks,
Eyso
|||Thanks mister Pasumansky! It works great. :)
I didn't knew that if I changed the parent dimension of a calculated member, I could still get the counts as results rather than as metadata.
