How to reflect the equivalent of the relational IN operator in MDX

I need to filter a set, based in another attribute hierarchy.

So I am looking for something like

filter(Time.Months.children , Time.Months.currentmember.label in ({Timen.MonthName.Children})

The list is generated dinamycally, so the OR approach wouldn't work (unless I write 12 expressions.

Any ideas ?

Thank you

Alejandro Leguizamo

SQL Server MVP

Could you give an example with data, to help clarify? If this is a case where dimensions [Time] and [Timen] have identical structure (eg: role-playing dimensions like Date and ShipDate in Adventure Works), and you want the set in [Time].[Months] hierarchy, equivalent to some set in [Timen].[Months], then LinkMember() may work, like:

Generate([TimenSet], {LinkMember([Timen].[Month].CurrentMember, [Time].[Month])})

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!

How to query data by various amount of filter value ??

Generally, on any screen, we design filter screen by allowing user to identify range or one value to search.

But sometime in some screen, It will be more convenient for user if user can identify No matter how value to search.

For example

On screen which have information of people in any province.

So user would like to search it by identify no matter how value to search.

There are check box of any province on filter part which enable users choose it.

Hence, if sometime user choose (by clicking on checkbox) 3 provinces : LA, Michigan, WachingtonDC to see description only 3 chosen province.

and sometime user choose (by clicking on checkbox) 2 provinces : LA, Michigan to see description only 2 chosen provinces.

Please give me any idea for create Stored Procedure or any tecnique to complete my idea...

Help me Pleaseeeee

If you want to use a stored procedure, the problem is a little more complicated, because you must use dynamic SQL. If you are using SQL 2005, see sp_executesql. If you just want to build an SQL string in your ASP page, you can set the checkboxes to postback, and handle the CheckChanged event. Build up a Comma seperated list of the values that are checked, and add a where clause to the SQL statement that looks like "WHERE province IN (" + ListOfProvinces + ")". Of course, if there is nothing checked, perhaps you want to show all the records, so you should drop the where clause, or maybe you don't want to show any, so you could just set the ListOfProvinces to an empty string.