Scope + Multi Select Filter + Calculated member does not work

Question

Hi All,

    I have a scenario which is explained as below

1)  I have 2 dimensions namely Channel and Market and there are corresponding fact tables namely Channel Fact and Market Fact.

2) By default channel data should be made available in the reports  

3) Now when I select a region and when the channel dimension is at All level , the corresponding region data should be shown.

I have written scope statements for the above scenarios

/* Initializing the calculated measure to channel fact base measure since by default the channel value should be made available */

CREATE MEMBER CURRENTCUBE.[Measures].[SalesAmount]
 AS 
[Measures].[Ch Sales Amount], 
FORMAT_STRING = "0", 
VISIBLE = 1  ;

/* When the channel is at all Level and when a region is selected show region data */  - [All Channels] member present in the hierarcchy

SCOPE([In Market Channel].[InMarketChannelAttr].[All Channels],EXCEPT(DRILLDOWNLEVEL(EXISTING [In Market Region].[InMarketRegionAttr].[Region Code].members),
{[In Market Region].[InMarketRegionAttr].[All Regions]}));
[Measures].[SalesAmount]=[Measures].[Re Sales Amount];
END SCOPE;

/* When the channel is at all Level and when a region is selected show region data */ - [All channels] member present in the Level

SCOPE([In Market Channel].[Channel Code].[All Channels],EXCEPT(DRILLDOWNLEVEL(EXISTING [In Market Region].[InMarketRegionAttr].[Region Code].members),
{[In Market Region].[InMarketRegionAttr].[All Regions]}));
[Measures].[SalesAmount]=[Measures].[Re Sales Amount];
END SCOPE;

CREATE MEMBER CURRENTCUBE.[Measures].[TotalAmount]
 AS 
[Measures].[SalesAmount], 
FORMAT_STRING = "0", 
VISIBLE = 1  ;

The problem is that the calculated member works fine for multi select channels and single select Region. but when I select more than 2 regions then the scope statements are not evaluated and it shows only the channel values.

Single Select Working fine

As you can see in the above screenshot , if I select a Single Region then the values are matching with Re Sales amount which is region sales amount.

Now when I do a multiselect

As you can see in the above screenshot, when I select multiple regions the scope statements are not evaluated. It shows me the same value as channel sales amount. I need to get the region Sales Amount in the Total Amount Column.

Please let me know where am I going wrong. Please let me know if there are any workarounds.

Thanks,

Ram

---

Please vote as helpful or mark as answer, if it helps

Answer 1

Hi Ram,

what should happen if the user select the [All Regions] from the drop down?.

Scope is not able to exclude the [all regions] using except. So removing the except function from the scope can do the trick

SCOPE([In Market Channel].[Channel Code].[All Channels],

DRILLDOWNLEVEL(EXISTING [In Market Region].[InMarketRegionAttr].[Region Code].members))<o:p></o:p>

[Measures].[SalesAmount]=[Measures].[Re Sales Amount];

END SCOPE;

and if you
still want to do some thing with [All Regions] level then following approaches
can be taken..

a) if the
user want to see some other value at the [All Regions] level then you can write
seperate scope to override the value at [All Regions] Level.

b) if you
are showing the data to user not using ODC Client then you can always exclude
the [All Regions] from the query itself using except at the query

SELECT {[Product].[Category].[Category].members } ON ROWS, {[Measures].[Ch Sales Amount],[Measures].[Re Sales
Amount],[Measures].[Total Amount]} ON COLUMNS FROM [cube]

WHERE ([In Market Channel].[Channel Code].[All Channels], EXCEPT([In Market
Region].[InMarketRegionAttr].[Region Code].members,[In Market Region].[InMarketRegionAttr].[All Regions]))

Hope this helps.

Regards

Gokul

Answer 2

Hi Gokul,

 Thanks a lot for the reply. I did try your suggestion by excluding the All Level from the scope. But the issue is that, whenever I select a multi select of regions the scope is not getting evaluated.

Also to answer your question , the user should see the "All Channels" data when "all regions" is selected.

Any other suggestions?

Regards,

Ram

---

Please vote as helpful or mark as answer, if it helps

Answer 3

Is this blog post relevant?

http://cwebbbi.wordpress.com/2011/10/27/scoped-assignments-and-multiselect/

Chris

---

Blog: http://cwebbbi.wordpress.com/ Consultancy: http://www.crossjoin.co.uk/

Answer 4

Hi Ram,

I was  able to replicate the issue in adventureworks what you were facing .

Can you try using the scope with .Members like

SCOPE([In Market Channel].[Channel Code].[All Channels],

[In Market Region].[InMarketRegionAttr].[Region Code].members)

[Measures].[SalesAmount]=[Measures].[Re Sales Amount]

END SCOPE

Regards

 Gokul

Answer 5

Hi Chris and Gokul ,

   Thanks a lot for the posts.

@Chris, Thanks a lot. The link you provided me was perfect. I followed the link you mentioned and worked out very well. Here is the modified code.

CREATE MEMBER CURRENTCUBE.[Measures].[SalesAmount]
 AS 
null, 
FORMAT_STRING = "0", 
VISIBLE = 1  ;

/* When the channel is at all Level and when a region is selected show region data */
SCOPE([In Market Channel].[InMarketChannelAttr].[All Channels],[In Market Region].[InMarketRegionAttr].members);
[Measures].[SalesAmount]=[Measures].[Re Sales Amount];
END SCOPE;

/* When the channel is at all Level and when all region  show channel data */
SCOPE([In Market Channel].[InMarketChannelAttr].[All Channels],[In Market Region].[InMarketRegionAttr].[All Regions]);
[Measures].[SalesAmount]=[Measures].[Ch Sales Amount];
END SCOPE;



CREATE MEMBER CURRENTCUBE.[Measures].[TotalAmount]
 AS 
[Measures].[SalesAmount], 
FORMAT_STRING = "0", 
VISIBLE = 1  ;

However I do find one scenario , If I change the Scope statements i.e. If I add the second scope statement at the first place and when I select multiple regions, I am encountering the same issue. However If I first write the scope of region members and then add a scope for all region member then it works fine.

Please let me know why the behaviour. I have no clue why this is happening.

Thanks a lot Chris :)

Thanks a lot gokul . 

Regards,

Ram

  

---

Please vote as helpful or mark as answer, if it helps