Re: MDX: Calculate the amount of open issues.

#1

There are 2 Date dimensions, but the measures you want only refer to a
single date member. So you can select one of the 2 dimensions (say,
[OpenDate]) for user input, with [ClosingDate] linked for MDX
computation. As Ohjoo already assumed, if you have a [CaseCount]
measure; and if each Date Dimension has an [All] level above [Year]:

1) Member [Mesures].[CasesOpened] as
'([CaseCount], [All ClosingDate])'

2) Member [Mesures].[CasesClosed] as
'([CaseCount], [All OpenDate],
LinkMember([OpenDate].CurrentMember, [ClosingDate]))'

3) Member [Mesures].[CasesStillOpen] as
'Sum(PeriodsToDate([OpenDate].[All]),
[Mesures].[CasesOpened] - [Mesures].[CasesClosed])'

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

#2

Hi Henrik,

[CasesOpened] would be a new calculated member, and no sum should be
necessary. I assumed that each of your Date dimensions has a top [All]
level above [Year], and that [All ClosingDate] is the member at that
level (just like in the Foodmart [Customers] dimension, there is an [All
Customers] member at the top).

But the error suggests that either the [All] member of [ClosingDate] has
another name, or there is no [All] level. Can you clarify the top levels
of [ClosingDate]?

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

#3

I understand there are one CaseCount regular measure, OpenDate dimension and
ClosingDate dimension.

This is another idea. How about having OpenCaseCount and CloseCaseCount
respectively as regular measures diretly from fact table. And, only one Time
dimension. I think this is simpler.

For this, create a view for OpenCaseCount and another view for
CloseCaseCount. Next create each cube for each measure and create a virtual
cube based on the two cubes.

Ohjoo Kwon
www.olapforum.com

"Henrik Hj?lund" <hjoellund (AT) mail (DOT) tele.dk> wrote

Quote:

Hi Peepak

You were right. In order to localise the Cube I had renamed the [All]
level.
When I used the localised name I got it to work, but only at the "Year"
level. The "Quarter","Month" and "Day" levels all return empty results for
"CasesClosed". I guess it is the LinkMember function that goes wrong. I
have
build the 2 time dimension based on the same Timetable (one on the table
itself and one on a view based on the table) so the 2 time dimensions
should
be equal.

Do you have an idea why it only works on the "Year" level?

Thanks for your help, I really appreciate it.

-Henrik.

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> skrev i en meddelelse
news:OubNGUwFFHA.3608 (AT) TK2MSFTNGP14 (DOT) phx.gbl...
Hi Henrik,

[CasesOpened] would be a new calculated member, and no sum should be
necessary. I assumed that each of your Date dimensions has a top [All]
level above [Year], and that [All ClosingDate] is the member at that
level (just like in the Foodmart [Customers] dimension, there is an [All
Customers] member at the top).

But the error suggests that either the [All] member of [ClosingDate] has
another name, or there is no [All] level. Can you clarify the top levels
of [ClosingDate]?

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

#4

Quote:

For this, create a view for OpenCaseCount and another view for
CloseCaseCount.

If you consider my idea, I think current fact table is still enough to
create the two cubes.

"Ohjoo Kwon" <ojkwon (AT) olap (DOT) co.kr> wrote

Quote:

I understand there are one CaseCount regular measure, OpenDate dimension
and
ClosingDate dimension.

This is another idea. How about having OpenCaseCount and CloseCaseCount
respectively as regular measures diretly from fact table. And, only one
Time
dimension. I think this is simpler.

For this, create a view for OpenCaseCount and another view for
CloseCaseCount. Next create each cube for each measure and create a
virtual
cube based on the two cubes.

Ohjoo Kwon
www.olapforum.com

"Henrik Hj?lund" <hjoellund (AT) mail (DOT) tele.dk> wrote in message
news:cv9mvg$d6e$1 (AT) news (DOT) cybercity.dk...
Hi Peepak

You were right. In order to localise the Cube I had renamed the [All]
level.
When I used the localised name I got it to work, but only at the "Year"
level. The "Quarter","Month" and "Day" levels all return empty results
for
"CasesClosed". I guess it is the LinkMember function that goes wrong. I
have
build the 2 time dimension based on the same Timetable (one on the table
itself and one on a view based on the table) so the 2 time dimensions
should
be equal.

Do you have an idea why it only works on the "Year" level?

Thanks for your help, I really appreciate it.

-Henrik.

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> skrev i en meddelelse
news:OubNGUwFFHA.3608 (AT) TK2MSFTNGP14 (DOT) phx.gbl...
Hi Henrik,

[CasesOpened] would be a new calculated member, and no sum should be
necessary. I assumed that each of your Date dimensions has a top [All]
level above [Year], and that [All ClosingDate] is the member at that
level (just like in the Foodmart [Customers] dimension, there is an
[All
Customers] member at the top).

But the error suggests that either the [All] member of [ClosingDate]
has
another name, or there is no [All] level. Can you clarify the top
levels
of [ClosingDate]?

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

#5

Hi Henrik,

Looks like a problem with LinkMember() - is the view filtering any rows
out of the Time Table? Here's an earlier thread that discussed a similar
problem:

http://groups-beta.google.com/group/...rver.olap/msg/
37180e37fcb58d63

Quote:

Chris Webb [MS] Dec 3 2002, 12:06 am show options

Newsgroups: microsoft.public.sqlserver.olap
From: "Chris Webb [MS]" <chw... (AT) online (DOT) microsoft.com>

Date: Tue, 3 Dec 2002 09:01:48 +0100

Subject: Re: Linkmember usage problem

Hi David,

Are you absolutely sure your two Time dimensions are exactly the same?
Have
a look at the keys of Quarter and Day levels and see whether they are
the
same for equivalent members - if they aren't, then that would explain
why
LINKMEMBER isn't working.

Regards,

Chris

--

Chris Webb
Consultant, Microsoft Services Switzerland

"David Fateman" <dfate... (AT) yahoo (DOT) com> wrote

Quote:

I'm trying to use Linkmember() to return the same dimension value in a
different hierarchy. For example:
(linkmember([Calendar Date].currentmember,[Fiscal
Time]).lead(1),[Measures].[Dollars]) would return Dollars for Fiscal
Time

1

Quote:

period forward from the Calendar Time. currentmember. Both Time
dimensions
are exactly the same (levels, members..)

For reasons unknown it only works for the year level (levels are All,
Year,
Quarter, Month, Day). I need it to work for any level
choosen(currentmember). Am I missing something obvious?

Quote:

Any help is GREATLY Appreciated!

Quote:

-Thanks in advance, Dave

Quote:

David Fateman
dfate... (AT) memberworks (DOT) com

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

#6

LinkMember() is for the multihierarchy, so it is not this case I guess.

Deepak add more informaton on LinkMember below.

Ohjoo

"Henrik Hj?lund" <hjoellund (AT) mail (DOT) tele.dk> wrote

Quote:

Hi

I have implemented the "2 cubes, one time dimension" solution and it works
really fine. I have even used the same "user" dimension to distinquish
between the person who started the case and the person who closed it. I
build the "closing" cube on a view based on the fact table from the "open"
cube adding a where clause saying "closetime is not null".

I think the solution with the LinkMember would have turned out fine as
well,
but there was no way I could get it to work below the "Year" level...
Still
wonder....

Thanks for your help
-Henrik.

"Ohjoo Kwon" <ojkwon (AT) olap (DOT) co.kr> skrev i en meddelelse
news:OLI7b80FFHA.3312 (AT) TK2MSFTNGP15 (DOT) phx.gbl...
For this, create a view for OpenCaseCount and another view for
CloseCaseCount.

If you consider my idea, I think current fact table is still enough to
create the two cubes.

"Ohjoo Kwon" <ojkwon (AT) olap (DOT) co.kr> wrote in message
news:uOvPZK0FFHA.1392 (AT) tk2msftngp13 (DOT) phx.gbl...
I understand there are one CaseCount regular measure, OpenDate
dimension
and
ClosingDate dimension.

This is another idea. How about having OpenCaseCount and
CloseCaseCount
respectively as regular measures diretly from fact table. And, only
one
Time
dimension. I think this is simpler.

For this, create a view for OpenCaseCount and another view for
CloseCaseCount. Next create each cube for each measure and create a
virtual
cube based on the two cubes.

Ohjoo Kwon
www.olapforum.com

"Henrik Hj?lund" <hjoellund (AT) mail (DOT) tele.dk> wrote in message
news:cv9mvg$d6e$1 (AT) news (DOT) cybercity.dk...
Hi Peepak

You were right. In order to localise the Cube I had renamed the
[All]
level.
When I used the localised name I got it to work, but only at the
"Year"
level. The "Quarter","Month" and "Day" levels all return empty
results
for
"CasesClosed". I guess it is the LinkMember function that goes
wrong.
I
have
build the 2 time dimension based on the same Timetable (one on the
table
itself and one on a view based on the table) so the 2 time
dimensions
should
be equal.

Do you have an idea why it only works on the "Year" level?

Thanks for your help, I really appreciate it.

-Henrik.

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> skrev i en meddelelse
news:OubNGUwFFHA.3608 (AT) TK2MSFTNGP14 (DOT) phx.gbl...
Hi Henrik,

[CasesOpened] would be a new calculated member, and no sum should
be
necessary. I assumed that each of your Date dimensions has a top
[All]
level above [Year], and that [All ClosingDate] is the member at
that
level (just like in the Foodmart [Customers] dimension, there is
an
[All
Customers] member at the top).

But the error suggests that either the [All] member of
[ClosingDate]
has
another name, or there is no [All] level. Can you clarify the top
levels
of [ClosingDate]?

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: MDX: Calculate the amount of open issues.

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap

Re: MDX: Calculate the amount of open issues. - 02-18-2005 , 06:28 PM

Re: MDX: Calculate the amount of open issues. - 02-19-2005 , 10:31 PM

Re: MDX: Calculate the amount of open issues. - 02-20-2005 , 05:55 AM

Re: MDX: Calculate the amount of open issues. - 02-20-2005 , 07:24 AM

Re: MDX: Calculate the amount of open issues. - 02-21-2005 , 03:00 PM

Re: MDX: Calculate the amount of open issues. - 02-23-2005 , 06:06 PM