Change Schema Name

[mrcool]

Hi All,

I am using sql object in my report studio report and I would like to change the schema name based on logged user group.
With CSVIdentityNameList I am able to fetch the usergroup list but not getting any idea to change the schema name in sql.
Appreciate your help.

Thanks,
Mc

[charon]

HI,


im not sure if you can change the Db schema in report studio, but you can do that in Framework manager.
In the "project viewer" on the left side, click on the data source of your choice, in the "porperties pane" you will find the DB and the schema.

May i ask what the reason is for changing the schema? If you want to add/ deny access to data you should use the security functionality for Cognos ..
cheerz :)

[mrcool]

Thanks for your reply charon..there are few reports which are not being sourced from FM instead we are embedding sql in the report.
Our users can logon to cognos with 2 different profiles based on the selected profile during the login the schema name has to be changed.Reports which are coming from FM has this functionality but thinking how to implement in SQL based reports.

[mrcool]

Finally reached more than halfway in achieving my req but I am facing the problem with the single quotes around my schema name because of the macro output. Any idea how to get the output without quotes?

Thanks,
MC

[MFGF]

Hmmmm. I guess it would help if we knew how you have coded your macro...

[mrcool]

Thanks MFGF
Here is the macro I am using to fetch the schema name..I am using 'grep' to search the particular group I am looking for and substituting with the schema name.
I checked this by adding in select part of the query and I found that it is generating the schema name as 'ABUK'/'ABUS' .
#
csv(
Substitute('xyz \(UK\)','ABUK',substitute('xyz \(US\)','ABUS',grep('xyz',
substitute('''', '',
substitute('''', '',
substitute(' ', '',
array(
split(',',
CSVIdentityNameList()
))))))

)))#

Cheers,
Mc

[mrcool]

Any suggestions please?

[CognosPaul]

CSV will automatically wrap array elements in single quotes. Use join('') instead.

Also, I think CSVIdentityNameList also returns an array of strings. Try using CAMIDList or CAMIDListForType instead.

[mrcool]

Hi paul,
Thanks for your reply but even CAMID functions are working with 'sq' or 'csv' only.
How to use join here?

Thanks,
Mc

[CognosPaul]

CAMIDList and CAMIDListForType return arrays.

#
join('',
grep('System Admin',CAMIDListForType('role'))
)
#

This will return CAMID("::System Administrators") without quotes.

One trick I've used before is to create a parameter map that contains the various possible groups. If each user will only ever have one matching role, then you can use CSVIdentityNameList to get the result.

A big warning about using CSVIdentityName and CSVIdentityNameList. These functions will not return any values that are inaccessible to the user. Users without traverse rights in the security folders will not see them. Normally everyone has traverse rights in the root folder, but if you arrange things logically in folders, each with their own custom security, you may get unexpected results.

But before you do anything really crazy, try simply switching out your CSV function for join('',...)

[mrcool]

CAMIDList and CAMIDListForType return arrays.

#
join('',
grep('System Admin',CAMIDListForType('role'))
)
#

This will return CAMID("::System Administrators") without quotes.

One trick I've used before is to create a parameter map that contains the various possible groups. If each user will only ever have one matching role, then you can use CSVIdentityNameList to get the result.

A big warning about using CSVIdentityName and CSVIdentityNameList. These functions will not return any values that are inaccessible to the user. Users without traverse rights in the security folders will not see them. Normally everyone has traverse rights in the root folder, but if you arrange things logically in folders, each with their own custom security, you may get unexpected results.

But before you do anything really crazy, try simply switching out your CSV function for join('',...)

Thank You so much for your time Paul. Join statement worked but went with one of the custom session paramter(Gives company name) which was already added by admin instead of CSVIdentityName. With simple substitute function I replaced the output from session parameter.

Once again a big thanks.

MC