Restrict Columns and Values

[nimishabrahamc]

Hi,

I am new to Cognos. I wanted to know

1. In report studio is there a way to restrict certain columns for particular roles/users and allow the other roles/users to view it?

2. In report studio is there is a way to restrict a particular value in a column for particular roles/users and allow the other roles/users to view it?

If yes, how do I do that?

Please advice me on these.

Thanks,
acn

[ww55d]

These wouldn't be controlled within report studio.

These are settings that can be applied in the framework.
Framework dictates what's available within the object list, as well as any mandatory data filters.

These are controlled within Framework Manager under Actions > Specify Object Security or Actions > Specify Data Security

[nimishabrahamc]

HI,

Thanks.  Yes found that the Column fields permission can be controlled from the framework under

Actions > Specify Object Security or Actions > Specify Data Security

But what about the values in the columns?

In report studio / framework manager is there is a way to restrict a particular value in a column for particular roles/users and allow the other roles/users to view it?

Please advice me on these.

Thanks,
acn

[BigChris]

Yep, its in the same place. You need to specify the group that the user exists in, then specify the filter that will restrict the data for that user.

[nimishabrahamc]

HI,

Thanks. I understood how to to specify the group that the user exists in, but how do I specify  the filter that will restrict the data for that user? PLease give me an example.

Thanks,
nac

[cognostechie]

Hi

As BigChris and ww55d mentioned, the option of Data security is the one that allows you to set filters to control the data for certain users/user groups.
Click on the query subject, from the 'Action' menu, select 'Specify data Security'. You can add groups there and then add a filter with the condition you need.

You can also do this in Report Studio by using Cognos macro account.defaultname but that is not a preferred approach and if the user has access to open the report
in Report Studio then they will be able to get rid of it

[bdbits]

nac, the filter is the last column in the list of groups. You can choose the dropdown by clicking on that column. Then pick Create/Edit Embedded if you want the filter defined with the query subject, and create an expression that evalutes to a boolean. You can also choose to create the filters as objects in the model and reference those, which is useful if you will be reusing them on multiple query subjects.

Example: Let's say you have an organizational dimension [Org] which is a dimension on a fact table you want to filter. Assume the first level in [Org] is [Department] with a member "HR". You want certain people in a predefined group to see the data linked to the "HR" department. Go to the [Org] dimension and choose Actions > Specify Data Security. Add the group and choose Create/Edit Embedded in the Filter dropdown. Your expression might look like: [Business View].[Org].[Department] = 'HR'
Now when someone in the HR department group accesses the data, Cognos will know what group they are in. When they want to retrieve data from the fact table, Cognos will see there is a link to the [Org] dimension which has data security applied; that they are a member of the group with the filter; and so it will allow facts linked to the 'HR' department to be included in the result set.

Hope that makes sense.

[nimishabrahamc]

Thanks a lot  bdbits and cognostechie. That really helped.

Although can anyone please tell me why I get an error when I right click on the dimension and select Specify data security? It says "This action is not supported for query subject based on Stored Procedures" Most of my reports are from Stored Procedures in Framework Manager.

How do I Specify Data security in Stored Procedures?

Please tell me a solution for this.

Thanks
nac

[BigChris]

I didn't know you could pull data from a stored procedure...

[nimishabrahamc]

Well yes, The reason I am using the Stored Procedures is because it is more faster than Views in the running the report.

But the only issue now is that error. It works with Views but not with Stored Procedures.

Can anyone please tell me why I get an error when I right click on the dimension and select Specify data security? It says "This action is not supported for query subject based on Stored Procedures" Most of my reports are from Stored Procedures in Framework Manager.

How do I Specify Data security in Stored Procedures?

Please tell me a solution for this.

Thanks
nac

[MFGF]

Well yes, The reason I am using the Stored Procedures is because it is more faster than Views in the running the report.

But the only issue now is that error. It works with Views but not with Stored Procedures.

Can anyone please tell me why I get an error when I right click on the dimension and select Specify data security? It says "This action is not supported for query subject based on Stored Procedures" Most of my reports are from Stored Procedures in Framework Manager.

How do I Specify Data security in Stored Procedures?

Please tell me a solution for this.

Thanks
nac

Data security works by adding a predicate (WHERE clause) to the generated SQL at runtime, dependent upon the user running the query. This is fine if Cognos is generating the SQL SELECT statement and sending it to the database to be executed. Where you are calling a stored procedure, you can't add a Predicate - instead you'd need to pass the user/group into the SP as a parameter and deal with it in your Stored Procedure code. You can't have it both ways, sadly. If you want Cognos to generate the SQL then it can also handle the data security for you. If you must use Stored Procedures, then it's up to you and your coding skills to dictate what gets returned to Cognos from the SP.

Cheers!

MF.