Exclude a table from section access

[#1]

Hi all,

I have a report that i've created which uses section access to filter out a table of tasks to only those by the user. Onto this table i have linked a calendar table so that the user can select the month/week/quarter etc from a list box and see specific tasks for that timeframe.

A requirement of the report is that the list box should display all possible months, weeks etc and only grey out those that arent applicable. The problem is of course that since the calendar is linked in to the other table it is affected by the section access, so its values are reduced.

So my question is: how can i 'exclude' this calendar table from the section access on the tasks table, while still allowing the user to use it to filter out results? Removing all joins between the tables solves the problem but then means they cant be used for filtering, so i'm quite stuck at the moment.

Thanks for any help

[pover · Accepted solution]

You can't exclude a table from section access unless you create an island table, but you can create rows in your fact table that only have the user keys and all the possible dates without any information in the other columns. 

In the script, if you join the user table and calendar table with since they don't share a key, the resulting table will be the cross product of the two tables (ie. each user will have all the possible dates associated with him or her).  Then concatenate that table with the fact table.  All the other columns will be null in those rows, but the calendar will not be reduced since each user is related to all calendar dates.  It's like having rows that explicitly show that the user had no activity during those dates.

Karl

[#3]

Thats a great idea, i thought i'd have to do some really complex thing but that sounds quite simple.

Thanks for your help

[#4]

I would LOVE to see an example of this.  I'm getting a little confused between the join and concatenation.  I'm hoping this will resolve my problem of holes in my master calendar using section access.  It really causes problems when using rangesum and above to performing rolling calculations.