I am hoping someone can help or point me in the right direction.

I am using a virtual cube to run reports in Excel Pivot table against.

The data that I have consolidated in the cube view is Revenue and Sales;
however, I am also tracking credit amounts. The problem is there are two time
stamps. The revenue has a order time that I am using in the time dimension
and I am using another time stamp for the credits.

In the Virtual cube when I use one time stamp, I only get the revenue amount
and if I use the credits time stamp, I only get the credits information. I
need to use one time stamp to pull the data back regardless the time stamp.
Or combine the time stamps to pull the correct data.

Any ideas or suggestions?

Assuming that there are 2 time dimensions in the virtual cube, credit
time and order time, applicable to Sales and Revenue data respectivley,
you could use LinkMember() to navigate from 1 time dimension to the same
point in the other (assuming that both have similar structures).

There are numerous threads in this newsgroup discussing the use of
LinkMember(), such as:

http://groups.google.com/group/micro...olap/msg/17655
312c0d0ddc8

From: Chris Webb [MS]
Date: Fri, Feb 14 2003 8:34 am
Groups: microsoft.public.sqlserver.olap

Hi Andrew,

First, the short answer: if you have a copy of 'MDX Solutions' by George
Spofford, this problem is dealt with in detail in Chapter 11.

...and if you don't (it's a worthwhile purchase though), here's what to
do:
simply hide one out of the pair of dimensions, and use calculated
measures
to match the currentmember on the visible dimension to the equivalent
member
on the hidden dimension. So, in your virtual cube if you hid your Budget
Calendar dimension and did nothing else you'd find that you could browse
the
Sales measures quite easily, but your Budget measures wouldn't work
properly; to get them working, you hide each Budget measure and for each
one
add a calculated measure with a formula something like:
VALIDMEASURE(
(MEASURES.MYBUDGETMEASURE, LINKMEMBER([SALES CALENDAR].CURRENTMEMBER,
[BUDGET CALENDAR]))
)

Since the keys on both the [Sales Calendar] and [Budget Calendar]
dimension
are the same, you can use the LINKMEMBER function to match a member on
one
to the equivalent member on the other. The resulting measure then gives
you
the same result as if you were browsing with both Time dimensions
visible
and had selected the same member on each.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hello thanks.

I am trying to follow what you stated in your comments.

My vitural includes two time dimensions - Time and Time Order Credits. I
would like to go by the one Time dimension for all data. However, I am still
new to MDX and not sure I understand the syntax correctly.

The measures based on the Time Order credits are
the total amount and number of credits. Based on the example, I should hide
these measures in the virtual cube and create calculated member such as the
one you indicated? What is ValidMeasure? Is this a function?
The time structures are the same for both dimensions.

VALIDMEASURE(
(MEASURES.MYBUDGETMEASURE, LINKMEMBER([SALES CALENDAR].CURRENTMEMBER,
[BUDGET CALENDAR]))
)



"Deepak Puri" wrote:

I'm getting a syntax error:

VALIDMEASURE(
({[Measures].[Total Amount Credited]}, LINKMEMBER({[Time].CURRENTMEMBER},
{[Time of Order Credts]}))
)


"SAM" wrote:

Superfluous {} can cause problems, so:

VALIDMEASURE(
([Measures].[Total Amount Credited], LINKMEMBER([Time].CURRENTMEMBER,
[Time of Order Credts]))
)


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Ok, thanks. I am no longer getting the syntax error but I am getting #ERR as
the value.

I'm a little confused. Since Time is one of my dimensions, I'm not sure how
to use this as a dimension for both Time and Credits Time dimensions.


"Deepak Puri" wrote: