How to refer to the whole cube while using the filter of the cube browser?

Hello!

I've got an accounting cube with balances as measures and amongst others a time dimension. There's also a calculated member which should show the balance of the previous year. This calculated member uses the PARALLELPERIOD-function.

So far everything works fine! But while using the filter of the cube browser and selecting a certain year, I can't refer to other years than the selected year.

So how can I get the balance of the previous year?

Thanks for any help!

From the information you provided I infer that the calculated member is defined inside your Time hierarchy. The solution to your problem could be one of the following:

1. Define your calculated member in a separate dimension, you can call it [Time Series] for example.

2. Alternatively, do not put Time hierarchy into the Filter of the cube browser, put it on Rows or Columns instead, and then filter only the current year and the calculated member.

3. Instead of calculated member use a calculated measure.

|||

Hello Tigran!

Thanks for you advice! But I defined the calculated member inside the Measure Group. So I think that it's called calculated measure. Am I right? So do you know any solution in this case?

I have to use the filter of the cube browser, because I have to use this kind of filter also in the KPI's! There's no alternative.

|||

Yes, it is a calculated measure in that case.

I think the complete answer to your question would depend on what client tools you will use to display all this data (or in case of reporting website - the application suite used to create the reports). Every application can have its own method of querying for the data, including the Cube browser inside BI Development Studio.

In your particular case (using Cube Browser) try to drag your Time hierarchy in a small spot that says 'Drop Filter Fields here'. It may give you a different behavior.

If that turns out to fix the problem, I would say that the original problem is likely to be caused by Cube Browser's use of Subcubes to restrict the data to a particular slice of the cube. Alternative query method using WHERE clause of MDX may give you different results.

|||

Yes, you're right. If I drag the Time hierarchy into 'Drop Filter Fields here', the calculated measure works properly.

But as I don't know so far which client tool I'll use, I want to fix the problem caused by the filter of the cube browser.

My intention is to refer to the whole cube (and not to any slice) within the MDX statement.

Now the statement looks like that:

CREATE MEMBER CURRENTCUBE.[MEASURES].LastYearBalance

AS

(

[Measures].[Balance],

PARALLELPERIOD

(

[Time].[Hierarchy].Levels(1),

1,

[Time].[Hierarchy].CurrentMember

)

),

VISIBLE = 1;

So is there any possibility to add a statement which makes a reference to the whole cube?

I think about creating something like a Session Cube and then refering to this Session Cube within my MDX statement!

But neither I don't know how to do that within the MDX skript nor if it's possible.

|||

I wish there was such statement indeed!

Unfortunately, I don't think there is anything you can do to make your calculation statement more 'correct'. The only bulletproof solution is having a regular measure (not a calculated one), that carries last year's data. For that, you will need to modify the underlying relational database.

But I would recommend studying what client tools will be used first. Because you may find that this is not even an issue with a lot of existing applications out there (as most of them use WHERE clause for stuff you put in Filter area). For example, OWC should work just fine.

|||

Ok, there seems to be no solution which makes my calculated measure working properly.

Right now I'm testing a beta version of Excel 2007 as BI frontend tool to see how it deals with this problem.

Thank you very much for help!