Filter permission with more complex querys

I got Livereport that works with categorys and attributes to create a report what the user is supposed to vote/answer on the upcoming month from system date.When running the query which involves alot of joins on category table. The Filter Permission parameter wont work.Now im looking for a way to get the similar code into the question instead. I know basically what Filter Permission does but cannot really recreate it.I only want the results that the user has see or higher permissions on in resultsets.

Find more posts tagged with

Comments

Lindsay_Davies

Message from Lindsay Davies via eLinkHi Urban,Filter Permissions (and Report Fields and maybe some other options)require that you have the DTree table in the queryAND that it is not aliased.If you test a filter permissions report as Admin, Livelink substitutesthe real where clause with 1=1 so it is always true.As an example, the following will not work because of the tablealias.... Select dt.name,dt.parentid,at.valstr from dtree dt, llattrdata atwhere %1 [filter permissions] and dt.dataid=at.defid....etcWhereas this would.... Select dtree.name,dtree.parentid,at.valstr from dtree,llattrdata at where %1 and dtree.dataid=at.defid....etcIf that does not help, post your SQL and I'll see if there is anything Ican suggest.RegardsLindsayOpen TextUK-Support-----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: 22 January 2007 09:30To: eLink RecipientSubject: Filter permission with more complex querysFilter permission with more complex querys Posted by Magnusson, Urban on01/22/2007 04:26 AMI got Livereport that works with categorys and attributes to create areport what the user is supposed to vote/answer on the upcoming monthfrom system date.When running the query which involves alot of joins on category table.The Filter Permission parameter wont work.Now im looking for a way to get the similar code into the questioninstead. I know basically what Filter Permission does but cannot reallyrecreate it.I only want the results that the user has see or higher permissions onin resultsets.[To reply to this thread, use your normal E-mail reply function.]============================================================Discussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/llisapi.dll/open/2249677Livelink Server:https://knowledge.opentext.com/knowledge/llisapi.dllTo Unsubscribe from this Discussion, send an e-mail tounsubscribe.livereportsdiscussion@elinkkc.opentext.com.

Urban_Magnusson

Thats the problem for sure. I dont really know how to work myself around it in this question though.Its the following question that needs rework. Its used to make a list of events that has a deadline the upcoming period of time. -----select doc.*, b.valstr as etitel, d.Valdate as distr, to_char(e.valdate, 'YYYY-MM-DD') as svsvar, to_char(f.valdate, 'YYYY-MM-DD') as isocensvar, doc.name as foldname from dtree doc, dtree fold, LLAttrdata b, LLAttrdata d, LLAttrdata e, LLAttrdata f where doc.parentid = fold.dataid and (doc.dataid=b.id and b.attrid=7 and b.defid=841147) and (doc.Dataid=d.id and d.attrid=8 and d.defid=841147) and (doc.Dataid=e.id and e.attrid=4 and e.defid=841147) and (doc.Dataid=f.id and f.attrid=9 and f.defid=841147) and (doc.subtype in (136)) and (e.valdate>SYSDATE - 14) and (e.valdate<(SYSDATE + 90)) and exists ( select dataid from dtreeacl acl where acl.dataid = doc.dataid and acl.rightid = %5 ) and not exists ( select dataid from dtree par,llattrdata att, llattrdata svar where par.dataid = doc.parentid and att.id=par.dataid and att.attrid=7 and att.valstr like b.valstr and att.defid=841147 and svar.id=par.dataid and svar.attrid=9 and svar.defid=841147 and svar.valdate > SYSDATE - 14 and svar.valdate < SYSDATE + 90 ) order by e.valdate------%5 = UserIDSorry for chaotic formatting I just cut and paste it atm.RegardsUrban

Lindsay_Davies

Message from Lindsay Davies <ldavies@opentext.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">RE: Thats the problem for sure. I dont really know how to work myself around it in...

Hi Urban,
Without delving too deeply into what you are doing, I assume the last but one line is where you try to manually do the filter for permissions.

and exists ( select dataid from dtreeacl acl where acl.dataid = doc.dataid and acl.rightid = %5 )
%5 will contain only the user's ID from KUAF, whereas using the "filter permissions" parameter will include any groups and project groups that the user is a member of.

To get filter permissions to work, replace the entire line with
and %5 where that is filter permissions
And also make the following changes.....

DO NOT alias DTree as doc when you are using it for the primary record AND want to user report fields or filter permissions.

Doc.name as foldname was a mistake - you want fold.name as foldname (fold is the row where its dataid matched the parentid from main dtree row).

I would suggest the following....
select dtree.*,
        b.valstr as etitel,
        d.Valdate as distr,
        to_char(e.valdate, 'YYYY-MM-DD') as svsvar,
        to_char(f.valdate, 'YYYY-MM-DD') as isocensvar,
        fold.name as foldname
from dtree,
        dtree fold,
        LLAttrdata b,
        LLAttrdata d,
        LLAttrdata e,
        LLAttrdata f
where dtree.parentid = fold.dataid and (dtree.dataid=b.id and b.attrid=7 and b.defid=841147)
        and (dtree.Dataid=d.id and d.attrid=8 and d.defid=841147)
        and (dtree.Dataid=e.id and e.attrid=4 and e.defid=841147)
        and (dtree.Dataid=f.id and f.attrid=9 and f.defid=841147)
        and (dtree.subtype in (136))
        and (e.valdate>SYSDATE - 14)
        and (e.valdate<(SYSDATE + 90))
        and %5
        and not exists ( select dataid from dtree par,llattrdata att, llattrdata svar where par.dataid = doc.parentid and att.id=par.dataid and att.attrid=7 and att.valstr like b.valstr and att.defid=841147 and svar.id=par.dataid and svar.attrid=9 and svar.defid=841147 and svar.valdate > SYSDATE - 14 and svar.valdate < SYSDATE + 90 ) order by e.valdate

%5 = filter permissions

Good luck.
Lindsay
-----Original Message-----
From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 24 January 2007 09:56
To: eLink Recipient
Subject: Thats the problem for sure. I dont really know how to work myself around it in...

Thats the problem for sure. I dont really know how to work myself around it in...
Posted by Magnusson, Urban on 01/24/2007 04:54 AM

Thats the problem for sure. I dont really know how to work myself around it in this question though.

Its the following question that needs rework. Its used to make a list of events that has a deadline the upcoming period of time.

-----
select doc.*,
        b.valstr as etitel,
        d.Valdate as distr,
        to_char(e.valdate, 'YYYY-MM-DD') as svsvar,
        to_char(f.valdate, 'YYYY-MM-DD') as isocensvar,
        doc.name as foldname
from    dtree doc,
        dtree fold,
        LLAttrdata b,
        LLAttrdata d,
        LLAttrdata e,
        LLAttrdata f
where doc.parentid = fold.dataid and (doc.dataid=b.id and b.attrid=7 and b.defid=841147)
        and (doc.Dataid=d.id and d.attrid=8 and d.defid=841147)
        and (doc.Dataid=e.id and e.attrid=4 and e.defid=841147)
        and (doc.Dataid=f.id and f.attrid=9 and f.defid=841147)
        and (doc.subtype in (136))
        and (e.valdate>SYSDATE - 14)
        and (e.valdate<(SYSDATE + 90))
        and exists ( select dataid from dtreeacl acl where acl.dataid = doc.dataid and acl.rightid = %5 )
        and not exists ( select dataid from dtree par,llattrdata att, llattrdata svar where par.dataid = doc.parentid and att.id=par.dataid and att.attrid=7 and att.valstr like b.valstr and att.defid=841147 and svar.id=par.dataid and svar.attrid=9 and svar.defid=841147 and svar.valdate > SYSDATE - 14 and svar.valdate < SYSDATE + 90 ) order by e.valdate

------

%5 = UserID

Sorry for chaotic formatting I just cut and paste it atm.

Regards
Urban

[To reply to this thread, use your normal E-mail reply function.]

============================================================

Topic: Filter permission with more complex querys
https://knowledge.opentext.com/knowledge/llisapi.dll/open/10375540

Discussion: Livelink LiveReports Discussion
https://knowledge.opentext.com/knowledge/llisapi.dll/open/2249677

Livelink Server:
https://knowledge.opentext.com/knowledge/llisapi.dll

To Unsubscribe from this Discussion, send an e-mail to unsubscribe.livereportsdiscussion@elinkkc.opentext.com.