Data Access Control in Brio

    I'd like to call it Brio rather than Interactive Reporting. I have some kind of thinkging that Oracle, this company, sometimes likes to buy a company and then destroy it.That's why I'd like to use the word Brio. Before going to the point, I want to say a little about my predecessor, and it's him who has brought me to this field-BI. His boss made Brio, the whole Hyperion solution, come into China and be used for OLAP analysis. My predecessor Mr. Liu said that version 6.x was best in use, but after Oracle had takenover the company, many bugs occured...
    We can do data access control in Brio. It is our usual practice to type the URL in IE, than we press Enter or click go as to open a BQY. So first of all, we have to get some parameters we need in this URL. Then according to the parameters we will know what role is logining in and finally we are able to show the user what he/she can see or what he/she can do to this BQY in the system.
    So how to get parameters in the URL. We use Sesssion. For example, there is an URL like this,

http://10.200.48.4:19000/workspace/browse/get/BQY/cube/%e5%ae%a2%e6%b5%81%e9%87%8f%e5%88%86%e6%9e%90.bqy?user=user&pass=pass&roletype=A&module_id=000002&JScript=enable&REQUEST_TYPE=GET_DOCUMENT."http://10.200.48.4:19000/workspace/browse/get/BQY/cube/%e5%ae%a2%e6%b5%81%e9%87%8f%e5%88%86%e6%9e%90.bqy" is the BQY address in workspace. We have a portal system(Java EE). There is a link in the portal, and when we click the link, it is actually we have clicked the above URL.Naturally parameters have routed. We can use Session.URL["parameter name"] to get a certain parameter. For example, to get role type we can use a variable to get it, var role_type = Session.URL["roletype"]. The session concept appears in Java EE too.
    Now suppose a user(some role) can only see and query Shanghai's sales(只允许看见并查询上海的销售额), what would we set to Brio? In the place section(有一个地区的区), we can add a filter, this filter uses CustomSQL to slice the place. Codes are as follows,
var l_role_type =Session.URL["roletype"] var l_role_id = Session.URL["user"] var l_module_id = Session.URL["module_id"] if(l_role_type=="L"){ //roletype=1（第一类用户），不进行任何权限限制 //代码为空 }else{//roletype=A（第二类用户）和roletype=S（第三类用户），需要对地址进行限制 //首先在地区区（q_city)新增筛选器，对显示的内容进行控制 //新增筛选器 var l_q_city = ActiveDocument.Sections["q_city"] var l_eis_section = ActiveDocument.Sections["首页"]//EIS section var sql1 = "Tad_City_Info.City_Code in (select dim_value from tar_role_data where role_id= '"+l_role_id+"' and module_id= '"+l_module_id+"' and dim_id='C')" var newLimit = l_q_city.Limits.CreateLimit("Tad_City_Info.City_Code") newLimit.Operator = bqLimitOperatorCustomSQL l_q_city.Limits.Add(newLimit) newLimit.CustomSQL = sql1 l_q_city.Process()
Then we can fill in the controls in EIS section, so that user can only see the place Shanghai's sales.
Here we have to notice how to use variables in a CustomSQL string. It's quite simple. We only have to use the plus sign(+) to form an SQL string. Then it's OK!