Clarity

I am fairly new to Clarity and I need to create a portlet that pulls certain data from the rate matrix...I am not sure which tables store this data...any help is appreciated! Also how to include the proper security parameters in the NSQL so users don't have access to rates can't access them through the portlet?

Thanks

"need to create a portlet that pulls certain data from the rate matrix...I am not sure which tables store this data"

ppa_matrix
ppa_matrixvalues ---> value2 holds the unique_name from srm_resources

"how to include the proper security parameters in the NSQL so users don't have access to rates can't access them through the portlet?"

didn't get this point ... since u will create the query to build the portlet, the users will not be able to edit the values. They will only be able to view.

NJ

The way security works is in the GUI.
Like you have Project, Resource and other objects and you give rights to view, edit create which work in navigation, the list and properties views.
When you access the database (directly) there is no Clarity security involved. Therefore that is a high level security decision who can have direct database access.

Then there is database access through Clarity. Users who can create lookups and queries have access to all data in the database.
If you don't want the users to see confidential project, resource, rate data and other don't give that right.

Then there is end user access to data in portlets. With portlets that have @WHERE:SECURITY: type clause the data is limited based on the existing user rights.
Without that all data is displayed and is a high level security decision who can create portlets.

When all data is displayed in portlets then you have to use instance level
Portlet - View right and again that is a high level security decision who can view a portlet with confidential data.
To view a matrix in the GUI the user would need Financial Maintenance - Define Matrix right, but to view the matrix data in a portlet the user does not need that right.

I could not come up with a @WHERE:SECURITY: epxression that would allow only users Financial Maintenance - Define Matrix right to view the matrix portlet data.
Unless you can come up with that then everybody who can view the portlet can view the matrix data.

Martti K.

We have a rates portlet and all our super users can see all resources and portlets, so the way we got round it was by checking the logged in user's group membership: if they're not in our admin group then they get nothing back in the portlet. I've attached the whole NSQL if anybody wants it (MS-SQL) - if your matrix columns are different then obviously you will need to tweak this.

SELECT   @SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:X.UID:UID@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:X.MATRIX:MATRIX@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:X.FROMDATE:FROMDATE@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:X.TODATE:TODATE@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:X.RESOURCE_CODE:RESOURCE_ID@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:X.RESOURCE_INT_ID:RESOURCE_INT_ID@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:X.RESOURCE:RESOURCE@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:X.EMPLOYMENT:EMPLOYMENT@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:X.TRANSCLASS:TRANSCLASS@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:X.PROJECT_CODE:PROJECT_CODE@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:X.LAST_UPDATED_DATE:LUD@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:X.LAST_UPDATED_BY:LUB@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:X.ROW_URL:MATRIX_URL@,

         @SELECT:METRIC:USER_DEF:IMPLIED:X.RATE:RATE@,
         @SELECT:METRIC:USER_DEF:IMPLIED:X.STANDARD_COST:STANDARD_COST@,
         @SELECT:METRIC:USER_DEF:IMPLIED:X.ACTUAL_COST:ACTUAL_COST@
FROM     
(select 
val.matrixrowkey as uid
,m.description as matrix
,val.fromdate
,val.todate
,isnull(r.unique_name, '*') resource_code
,r.id as resource_int_id
,isnull(r.full_name,'*') resource
,(select name from cmn_lookups_v where language_code ='en' and lookup_type = 'srm_resource_type' and id = r.person_type) as employment
,isnull(val.value4, pac.transclass) as transclass
,isnull(val.value3, '*') as project_code
,numval1 rate
,numval2 standard_cost
,numval3 actual_cost
,niku.cmn_trunc_date_fct(val.last_updated_date) as last_updated_date
,(select user_name from cmn_sec_users u where u.id = val.last_updated_by) last_updated_by

from
ppa_matrix m inner join ppa_matrixvalues val on m.matrixkey = val.matrixkey
inner join srm_resources r on r.unique_name= val.value2
inner join pac_mnt_resources pac on r.id = pac.id
inner join prj_resources pr on r.id = pr.prid


where
(r.is_active = 1 or year(date_of_termination) = year(getdate()) )

and ((pr.prisopen = @WHERE:PARAM:USER_DEF:INTEGER:OPEN_TE@) or (@WHERE:PARAM:USER_DEF:INTEGER:OPEN_TE@ is null))
and ((r.id = @WHERE:PARAM:USER_DEF:INTEGER:RESOURCE_ID@) or (@WHERE:PARAM:USER_DEF:INTEGER:RESOURCE_ID@ is null))
and ((m.matrixkey = @WHERE:PARAM:USER_DEF:INTEGER:MATRIXKEY@) or (@WHERE:PARAM:USER_DEF:INTEGER:MATRIXKEY@ is null))
and ((isnull(val.value4, pac.transclass) = @WHERE:PARAM:USER_DEF:STRING:TRANSCLASS@) or (@WHERE:PARAM:USER_DEF:STRING:TRANSCLASS@ is null))
and ((r.person_type = @WHERE:PARAM:USER_DEF:INTEGER:EMPLOYMENT@) or (@WHERE:PARAM:USER_DEF:INTEGER:EMPLOYMENT@ is null))

/* Only show most recent matrix row values */
and getdate() between fromdate and todate

/* If current user not in Admin then return nothing */
and case when exists
(select ug.user_id 
from cmn_sec_user_groups ug, 
cmn_sec_groups g 
where g.id = ug.group_id 
and g.group_code = 'admin_group'
and ug.user_id = @WHERE:PARAM:USER_ID@)
then 1 else 0 end = 1

/*OBS Filtering */
and exists (select 1 FROM 
prj_obs_associations assoc 
LEFT OUTER JOIN prj_obs_units_flat flat ON assoc.unit_id = flat.unit_id 
AND assoc.table_name = 'SRM_RESOURCES' 
WHERE ((r.id = assoc.record_id) AND flat.branch_unit_id = isnull(@where:param:user_def:integer:obs_id@, flat.branch_unit_id) )) 

) as X

WHERE    
@FILTER@

Owen

Does that cover the case of users having rights to create queries and portlets being able to create and view a new portlet without those restrictions for the rates - to name a sensitive, but not that risky area.

Martti K.

No, you're right that doesn't cover it if the group membership check is not there. But we don't give our users personal dashboarding rights so it's not an issue we've encountered!

The way I see it if you are picky about sensitive data (and security) you can be compromised in two ways:
- you give access to database content either directly or through Clarity portlets, queries and lookups
and if you don't
- there are users who have rights to modify their own rights to get the above

Martti K.

Thanks for all the responses, I see now that most of the values from the matrix are stored in the PPA_Matrixvalues, but I"m still having trouble finding where the rest are stored and how I connect them...

I need these attributes for my portlet


Role Name
Role ID
Resource Name
Resource ID
From Date
To Date
Billable Rate
All-In Rate

All of the values in the matrix are stored in PPA_MATRIXVALUES. The columns that you define in the matrix are recorded in PPA_MATRIXCOLDEF - one record per column and matrix. The order you define the columns in the matrix determines which of the columns in COLDEF hold the data. So for example we have a matrix with a column definition like this (ascending numbers map to columns left to right in matrix)

1
chargecode
2
resource_code
3
project_code
4
transclass
5
resource_class
6
projclass

We don't have Role as a column in that matrix but if we did then it would appear here, in the same order it is shown in Clarity's matrix page. You will have to join to other tables (SRM_RESOURCES probably) to get resource/role names as the matrix tables only deal with IDs (and note they are the GUI visible IDs not the 500000 internal IDs often used elsewhere)
.
From/To date are the same fields in MATRIXVALUES. The rate fields will be NUMVAL1, NUMVAL2 or NUMVAL3 in that table.

The lower level SQL I pasted earlier should be a starting point but you should also look at the technical reference guide which shows the relationships pictorially.

Hi noland,

Did the responses provided help answer your questions? If so please mark the appropriate post as Accepted Solution.

Thanks!
Chris