crossjoin problem?

i need to calculated the number of participants during a specific period for a give club. the member table contains the following info: member id, club id, starting date, ending date, etc. i have created four named calculations: starting year, starting month, ending year, ending month. starting year and starting month are part of the hierarchy starting period while ending year and ending month belong to the hierarchy ending period.

i defined a basic measure: count and a calculated measure: numberofparticipants. the calculated measure is defined as:

sum(crossjoin([starting period].currentmember.parent.firstsibling.firstchild:[starting period].currentmember, [ending period].currentmember:[ending period].currentmember.parent.lastsibling.lastchild, [club id].currentmember), [measures].[count]))

i could use nonempty crossjoin but since the hierarchies are from the same dimension, it doesn't make a difference.

the MDX query i have looks something like:

select [starting period].[2005].[x] on axis(0), [ending period].[2005].[x] on axis(1) from clubstats where ([measures].[numberofparticipants], [club id].[23])

i am hoping to get the number of participants in club 23 during the month x of 2005. the problem is that for some months, the query returns correct result and for other months. the months that do have a problem are those where there is no existing record in the table with an ending date in month x even if there are records with ending date in month x+1, x+2, etc. it seems that crossjoin or the query "bailed out" after it determines that tuple (x, x) does not exist.

if i use different months for starting and ending period, e.g.

select [starting period].[2005].[3] on axis(0), [ending period].[2005].[4] on axis(1) from clubstats where ([measures].[numberofparticipants], [club id].[23])

again the result is empty if no participant with starting month of 2005 3 and ending month of 2005 4 even if there is participant starting in 2005 3 and ending in 2005 6, which ought to be part of the result.

does anybody know why this is happening? any help will be greatly appreciated!

> i need to calculated the number of

participants during a specific period for a give club

I think the definition of task needs to be clarified a bit before diving into

programming...

Suppose your start and end dates for any given member have "day"

granularity, meaning that for your system it does not make sense to record

the time of joining the club and time of quitting the club by the member. It

gives you capability to calculate the number of members in the club at certain

day.

How would you define this measure at other levels

like week, month and year? Is it the average number of members of the club

during the period?

|||

You mentioned above that: "..but since the hierarchies are from the same dimension..". If you're using AS 2005, and the [starting period] and [ending period] hierarchies are indeed in the same dimension (though I don't understand how/why it would be modelled that way), then the "Autoexists" feature will eliminate combinations of members from the 2 hierarchies which don't exist in the dimension data:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/imdxsmss05.asp

>>

Introduction to MDX Scripting in Microsoft SQL Server 2005

...

Autoexists

The true space of the cube is more restricted than the product of its attribute hierarchies. There are cells that do not exist, because Autoexists Attribute members from the same dimension, which do not exist with one another, do not exist in this cube space. For example, (Beijing, Canada) does not exist. The concept of Autoexists runs throughout this document.

Note This has nothing to do with data in the fact table. It is a dimensional concept only-Autoexists only pertains to the attributes in the same dimension.

Queries can request cells that do not exist in the cube space. For example, the statement select customer.gender.members on 0, {Customer.Name.Fred, Customer.Name.Jane} on 1 from sales includes cells that do not exist in this space. These cells always return empty-they cannot contain calculations and they cannot be written to.

...

>>

|||

You are right at pointing out the cause of the problem. To get around this, I need to create two server generated time dimension and use starting date and ending date to setup regular relationship between the measure and these dimensions.

I am still interested in hearing recommendations from experts on this forum because there maybe more elegant/efficient ways to set up the cube/dimensions.