en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Chris Campbell  
#1 Posted : Thursday, March 29, 2012 4:43:30 PM(UTC)
Chris Campbell
Rank: Advanced Member

Groups: extranet\Customer
Joined: 3/28/2011(UTC)
Posts: 21

Hi

I was wondering if anyone knew if you can have a forced criteria range for a specific role in the management studio?

I would like our Sales cube to only return data relating to a range of Items / Sales Entities.

For Example Items 10000-99999 - any other items that are out of this range i would want to exclude.

I could do this manually by ticking each item in the forced criteria box, however we add items frequently and the list would have to be updated on an almost daily basis therefore if there was a way of including this as a range that would be great.

Thanks

Chris
Chris Campbell
Gemma International - Andover UK
Jeff Alexander  
#2 Posted : Monday, April 2, 2012 10:20:18 PM(UTC)
Jeff Alexander
Rank: Newbie

Groups: extranet\Customer
Joined: 3/2/2012(UTC)
Posts: 3

After much consideration, I managed to come up with this as the simplest way I can think of for doing this:

I would add a calculated column to the primary fact table that is either true or false if the item ID is in the range you want.

In the cube data source view, convert the table to a named query (Right click, Table - Replace with named query) and edit it to tack something like this on the end:
CASE WHEN itemid between 10000 and 99999 then 1 else 0 end as ShowSales

Create a decomposed dimension with the contents of ShowSales

Then add a criteria in your targit role that shows only items where showsales=1

Jeff

Edited by user Monday, April 2, 2012 10:49:08 PM(UTC)  | Reason: Not specified

Jeff Alexander  
#3 Posted : Monday, April 2, 2012 11:10:22 PM(UTC)
Jeff Alexander
Rank: Newbie

Groups: extranet\Customer
Joined: 3/2/2012(UTC)
Posts: 3

Sorry about the multiple edits, I came up with a potential solution and then immediately thought of a better way of doing it.

Hope that helps,
Jeff
Users browsing this topic
Anonymous
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Notification

Icon
Error