How to Filter on more than one Value in FM, i.e. IN operator not working?

[longhorndkr]

Hello,

In my FM model, I have a Fact table that contains Student Data.  I need to filter that data down for Campus Admins so that the admin user logging in and running a report only sees the Campuses (and the teacher and student results in those campuses) to which they belong.

I have Campus, Teacher and Student dimensions all joined to my Fact via keys.  In my report, I have prompts allowing the user to filter on these dimensions.  Because I do not want to have the user running my report see the entire list of campuses that exist in their prompt but just the ones in which they work, I want to filter the results to just their own campuses, let's say 3 out of 100.  I would think this would also speed things up by pre-filtering my Fact data.  Consequently, because I have access to the security tables, I was able to create a datasource query subject (called Dim_AdminCampus) using sql and the macro #sq($account.defaultName)# that lets me filter my list of campuses to just those in which the admin works. The two tables (Fact and newly created dimension) are related in my Foundation Layer.

HOWEVER, when I attempt to implement just such a filter on my Fact so that it will be filtered to just the campuses the admin has access to, I am getting (parsing) errors.

Effectively, I am trying to do a filter in my Business view Fact table that says:

[Fact].[CampusID] IN [Dim_AdminCampus].[CampusID]

Thus, I am trying to limit my Fact to just the Campuses that exist in my already filtered list of Campuses (in [Dim_AdminCampus]).  How do I do this?  I would think the 'IN' operator would work but it apparently does not.  I am using a SQL Server db on Windows Server with all function sets.

Surely, someone has run into this before.  How did you filter a large Fact dataset down to a smaller dataset that isn't simply one-to-one?  By that I mean, I cannot use = here because I have multiple values in my resultset.  in sql it should be something more akin to: select * from fact where campusid in (campus1,campus2,campus3)... why does this not work? 

FYI, instead of creating the new datasource query subject [Dim_AdminCampus], I first tried just implementing a Filter directly on the Fact but got even more bizarre parsing errors (parsing error at position 6, etc referring to my 'IN' operator, etc) , thus I created the new dimension as a workaround thinking that dragging a table member over would be more acceptable but alas, it simply does not seem to want to let me filter on more than one value.  How do I do this?  So frustrating. . .

THANK YOU ALL IN ADVANCE!
Dave

[MFGF]

Hi,

If you implement the filter on your Campus dimension (which you seem to have done) and this is joined to the Fact using a mandatory relationship (ie 1..1 <--> 1..n) then you shouldn't need a filter on your Fact. The act of bringing in a campus item into any report will automatically limit the facts to just those that join to the filtered Campus rows?

If I'm not understanding properly, can you explain further?

Cheers!

MF.

[longhorndkr]

Hi MF,

No, you are right.  I actually had to cut the old join to my old Dim_Campus table and then it worked.  I guess my question still revolves around filtering a set down.  The syntax for what you can put in a Filter seems very lacking to me. 

As an example, I solved that particular problem by recreating the dimension table (re-building it and filtering it down via sql).  But instead of doing that, I should have been able to just add a filter to my existing dimension so that it goes from 1000 items to just 3, right?  The syntax for that filter is what I am after so that I do not have to recreate the wheel via my dimension every time I run into this. 

What s the syntax for a filter when you need to do more than just 1:1 (or =)?  I tried 'in' unsuccessfully, and 'CONTAINS' threw errors as well.  Any suggestions would be great.

Thank you by the way!
Dave

[longhorndkr]

Ay yay yay!

FYI, this worked for me - putting the dang right part in parentheses!!!  I was certain I had tried every combination of this but alas. . .

[Fact].[CampusID] IN ([Dim_AdminCampus].[CampusID] )

Omg, I wish they had better (*scratch that*), SOME documentation on this!

Thanks guys!