Hi, I've looked around the forum and documentation and found a examples but nothing that quite answers my question in a simple enough way for me to understand!
What I need to do. We have a DB2 database containing our data. In certain 'header' tables that are the first tables generally mentioned in join statements (such as an 'order header' which then joins to multiple 'items') we have a 'branch' data item. We need to turn off access to certain branches' data to all of our dashboard reporting and to some reportcaster reports - as we are selling those branches and for a while they will continue to have data in the database but no one in the remaining company can be allowed to access this data (but some scheduled reports will be sent to them).
Question - is there an easy way to force a filter on the tables - even when they are joined up to others - so that they by default will have for example WHERE BRANCH NE 'X' OR 'Y' ;
If this is something that can done at synonym level I can always create some new synonyms for the new owners scheduled reports to allow them to access their data (ie in the copy in effect switch the 'ne' into and 'eq' - WHERE BRANCH EQ 'X' OR 'Y' ; )
Many thanks in advance for any and all help and suggestions

This message has been edited. Last edited by: Tamra,

There may be an easier way than this, but I think you could create a DEFINE in the synonym to test for those two branches:

DEFINE BRANCH_BLOCK/A1=IF ((BRANCH EQ 'X') OR (BRANCH 'EQ' 'Y')) THEN 'Y' ELSE 'N';

Then add an additional WHERE clause to your reports to exclude those branch records:

WHERE BRANCH_BLOCK NE 'Y';

You could create a new synonym for the new owners and set BRANCH_BLOCK to 'N':

DEFINE BRANCH_BLOCK/A1='N';

Hi,
Yes - can see what you're saying but (very) small team, a couple of thousand reports and very short timescales, so a very simple fix in one place would be awesome!

Actually, I just found out there ARE filters in master files... I've just never used them.

I'll create a copy of the sample CAR master file and see if I can get one to work.

Bad news... although you can create a filter in a master file, it does not appear to automatically apply it. In App Studio, the filter shows up, but I must right-click on it and select "Add filter to report".

I came across this:
http://webfocusinfocenter.info...f5-8F1A-D2058F6C697F

Have you considered using DBA Security?

quote:

Originally posted by Darryl_uk:
I came across this:
http://webfocusinfocenter.info...f5-8F1A-D2058F6C697F

Interesting. I think you probably need to use the RESTRICT option.

Maybe something like:

RESTRICT=NOPRINT, VALUE=((BRANCH NE 'X') AND (BRANCH NE 'Y'));, $

Squatch,

it would be restrict=value_where. The NOPRINT option is to hide the field.

 RESTRICT=VALUE_WHERE, NAME= [SEGMENT], VALUE=BRANCH NOT IN ('X','Y');,$ 

the thing I have been warning people about is that if you use Clustered Master Joins and DBA security in v8.1.05 (probably 8.0.09 and up) you need gen 913 or above. there was a bug in sql generation somewhere after V8.0.08. basically it would always join all tables in the query all the time. It wouldn't recognize when a table wasn't used and exclude it from the query.

Daryl,

How about using FILTER FILE in EDASPROF?
See: Filter File