Report on PersonWantsOrg: user see all requests

Question

Hi, 
 
 I'm trying to implement a report fot the productOwner that show only the requests an owner can see. 
 A product owner can be owner of more than a product so in object browser an user that is owner of 2 products will see only request for those products but in a report he can see all the requests. 
 I thought the permission on what an user can see will be applied to the report too. 
 Any advice? 
 
 (7.1)

Markus Weiss-Ehlers · Answer

There are two differences between the list of request in Object Browser and your report query. 
 
 First, the list in Object Browser is using the object layer and therefore the permissions of the logged on user while loading the list. 
 Second, you are using a native SQL query which can not use the permissions of the user that generates the request. And you are not limiting the result of your query to one specific person. 
 
 In your report you need to do the two following things. 
 
 Add a parameter to your query that limits the query to the person which is requesting the report. I'll call it @eportScheduledBy. The query looks like this than. select r.DisplayName, g.cn as Role, p.LastName + ' ' + p.FirstName as Person, s.ident_PwoState as Status, pwo.CCC_RequestCode as RequestCode, pwo.OrderDate as OrderDate 
from PersonWantsOrg pwo
join UNSGroupB g on pwo.ObjectKeyOrdered = g.xobjectkey
join Person p on pwo.UID_PersonOrdered = p.UID_Person
join PWOState s on s.UID_PWOState = pwo.UID_PWOState
join UNSRootB r on r.UID_UNSRootB = g.UID_UNSRootB
where pwo.UID_PersonOrdered = @ReportScheduledBy
order by 1,2 
 You add a parameter to your report called ReportScheduledBy and give the parameter a valuation script like the following. If Provider.Contains("UID_Person") Then 
	Value = $UID_Person$
Else
	Value = Connection.User.Uid
End If 
 
 HINT: 
 Take a look at the OOTB reports, for example VI_Reporting_Template_Portrait_Params. It contains the same parameter and you could see how the parameter has to be defined to set it automatically to the logged on / report subscription user.

Markus Weiss-Ehlers · Answer

Let's assume you stick with a query. 
 I took the following where clauses from the role based permission groups. They should limit the results accordingly if you use the parameter @ReportScheduledBy I have described earlier. The parameter needs to contain the UID of a person. 
 Select * from PersonWantsOrg 
where

-- My own requests
-- Config Parm QER\ITShop\ShowClosedAssignmentOrders influences the result of the function QER_FTPWOVisibleForPerson
UID_Personwantsorg in (select UID_Personwantsorg from dbo.QER_FTPWOVisibleForPerson(@ReportRequestedBy, 0))
-- Requests i am able to see because I am a manager
or
UID_Personwantsorg in (select UID_Personwantsorg from dbo.QER_FTPWOVisibleForPerson(@ReportRequestedBy, 1))
-- Requests i am able to see as Product Owner
OR
UID_Org in
(select UID_ITShopOrg from ITShopOrg ito 
 join AccProduct ac
 on ito.UID_AccProduct = ac.UID_AccProduct
 join AERole ae
 on ac.UID_OrgRuler = ae.UID_AERole
 join PersonInAERole pia
 on ae.UID_AERole = pia.UID_AERole
 where pia.UID_Person = @ReportRequestedBy
)