Report on PersonWantsOrg: user see all requests

In report editor I set a datasouce with the following query:

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
order by 1,2

Then i create a band with each column of the item in the select.

From web portal I subscribed to that report and hit mail icon to generate immediatly a mail with the report attached...

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.

1. 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

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.

Thanks for your help Markus so I did a few tests

I look into documentation in Configuration Guide and then I try the view and Object query module:

View query module:

from the guide: You can use query module “View” to create data queries using predefined database views and in this way control user access rights.

So i thought with this module The results were filtered with user permission in web portal, but in the datasource properties I can't select the viewName like documentation said I doesn't have the viewName field. I tried to insert as paramater named viewName or in base table but I had always the errore viewName is null.

 Object query module:

from guide: Data queries with the query module “Object” are created using the object layer and therefore take user access permissions fully into account

so I thought as with view module because it uses object layer will be filtered by user permission in web portal

I selected the table in Data source and added the columns that I want to be shown, I prepared the report but when I test in the webportal from mysettings -> subsdcrobed report -> send mail with report I got a report with all the requests.

So I don't know what I'm doing wrong

Referring to the error you got with the query type View, you found a bug. But I believe, there is also a misunderstanding for this query type on your side. You can build the view in a way that it only returns the data you are allowed to see based on some parameter like @ReportScheduledBy.

I am going to take a look into your use case in regards to the query type Object but it will take while.

In the meantime, try to follow my instructions in regards to the parameter in your SQL query. That would definitely solve your issue.

Thanks Markus,

I could go with query method but I need to have all the requests an user can see as Product Owner Role not only the ones he owns has destinatary(UID_PersonOrdered) of the request.
Is there any table that connect Person with the Requests he can see connected with a role?

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
)