MS SQL Server OLAP - Calculated member / set depending on two dimensions

Author

Calculated member / set depending on two dimensions

Carlos

2005-04-22, 3:24 am

Hello,

I have what I think is a fairly common problem in AS.
I'd like to generate a member based on the value of a dimension.

For example: I have a dimension called [Status], with a level [status]
and a measure called [measures].[nr of hits]. Now I'd like to create a
calculated member which counts the number of hits where [status] =
"Online".

A similar example would be: I have a dimension, with a couple of
levels and in the top level there are members for NY, LA and SF. Now,
I'd like to create a named set (?) which only contains the tree (all
levels and members) for the member 'NY'. When I use the filter
statement in named sets I get the error message: 'cannot convert set
to member'..

Does anyone have any suggestions??

Thnx.

OLAPMonkey

2005-04-22, 11:23 am

You are mixing and matching concepts it sounds like to me...so let me
try your first issue first...the Online status issue. To execute this
you would create a member in the measures dimension that does what I
refer to as a cross-tuple lookup. It looks up the contents at a
different address (cell definition) in the cube and puts those contents
in a different cell.

A foodmart example might look like this...
with member [Measures]. [UnitSalesForHighSch
oolers] as '([Education
Level].[All Education Level].[High School Degree],[Measures].[Unit
Sales])'
select
{
[Measures].[Unit Sales], [Measures]. [UnitSalesForHighSch
oolers]
} on columns,
{
[Gender].[Gender].Members
} on rows
from Sales

So for you case it would be something like this...

with member [Measures].[OnlineHits] as
'([Status].[status].[Online],[Measures].[nr of hits])'

As for your second issue...I'm not quite following what you are saying
is the problem. The function you need to get the "tree" below New York
is the Descendants function and that function indeed will return a set.
That's the part where I'm missing what you are trying to accomplish.
>From you error message, it appears you are trying to use the set where
a membere is expected. If you wish to aggregate all the values in the
"tree" below New York, you could use a Sum or Average or Min or Max or
etc function against the set returned by the Descendants function to
morph the set data into an aggregated member. As I'm not sure what you
want to do, however, I don't know (and don't think) that is necessarily
what you are trying to accomplish.

Sponsored Links