Live Report to show contents of a users Favorites

Jonathan_Thomson_(jonathanthomson_-_(deleted))

Is it possible to create a livereport that returns the results of a users Favorites volume?We are running SQL server on 9.7.Thanks,Jon

Find more posts tagged with

Comments

Bhupinder_Singh

Message from Bhupinder Singh <bsingh@opentext.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

This will be difficult, because favourites are stored in the KUAFPrefs table in an "assoc" datatype. Only the DataIDs of each favourite are stored in this assoc.

For example:

Before placing the last 3 favourites into a new "Specials" favourites tab, my KUAFPrefs table stored the Favourites tabs as follows:

PrefsID = 1000

PrefsKeyWord = PersonalFavoritesTabs

PrefsValue = A<1,?, 1=A<1,?,'ButtonBar'=false, 'Contents'=A<1,?,3202=1,3211=1,5375=1,29541=1,31623=1,32062=1>,'Dft'=true,'Label'='All','Select'=false,'System'=true>, 2=A<1,?,'ButtonBar'=false, 'Contents'=A<1,?>,'Dft'=false,'Label'='Specials','Select'=false,'System'=false>>

After placing the last 3 favourites into a new "Specials" favourites tab:

PrefsID = 1000

PrefsKeyWord = PersonalFavoritesTabs

PrefsValue = A<1,?, 1=A<1,?,'ButtonBar'=false, 'Contents'=A<1,?,3202=1,3211=1,5375=1,29541=1,31623=1,32062=1>,'Dft'=true,'Label'='All','Select'=false,'System'=true>, 2=A<1,?,'ButtonBar'=false, 'Contents'=A<1,?,29541=1,31623=1,32062=1>,'Dft'=false,'Label'='Specials','Select'=false,'System'=false>>

Notice in the PrefsValue the "Contents" component for both the "All" tab and the "Specials" tab:

"All" tab:

'Contents'=A<1,?,3202=1,3211=1,5375=1,29541=1,31623=1,32062=1>

"Specials" tab:

'Contents'=A<1,?,29541=1,31623=1,32062=1>

- Bhupinder

------------------------------------------------------
Bhupinder Singh, B.Math., B.Ed.
Senior Product Specialist, Customer Support
Open Text Corporation, Waterloo, Ontario, Canada
Customer support e-mail: support@opentext.com
Customer Support Telephone: 800-540-7292
------------------------------------------------------

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, April 23, 2007 9:46 AM
To: eLink Recipient
Subject: Live Report to show contents of a users Favorites

Live Report to show contents of a users Favorites

Posted by jonathanthomson (Thomson, Jonathan) on 04/23/2007 09:40 AM

Is it possible to create a livereport that returns the results of a users Favorites volume?

We are running SQL server on 9.7.

Thanks,

Jon

Jonathan_Thomson_(jonathanthomson_-_(deleted))

Hi Bhupinder,Thanks for the response.So if I was only interested in the "All" Tab, or perhaps only interested in a generic "Named" tag, is it possible to produce a SQL script to extract the relevent code from the assoc type?Where does each users preferences sit in relation to the data tables?Thanks,Jon

Lee_Butler

Message from Lee Butler via eLinkHi Jon,I think you've got WebReports - in which case you might want to look atthe ASSOC subtag. In addition there are subtags for indexing lists,records and recarrays (LIST, RECORD and RECARRAY respectively).Thanks,Lee.-----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: Dienstag, 24. April 2007 14:16To: eLink RecipientSubject: Fav Live ReportFav Live ReportPosted by Thomson, Jonathan on 04/24/2007 08:10 AMHi Bhupinder,Thanks for the response.So if I was only interested in the "All" Tab, or perhaps only interestedin a generic "Named" tag, is it possible to produce a SQL script toextract the relevent code from the assoc type?Where does each users preferences sit in relation to the data tables?Thanks,Jon[To reply to this thread, use your normal E-mail reply function.]============================================================Topic: Live Report to show contents of a users Favoriteshttps://knowledge.opentext.com/knowledge/llisapi.dll/open/12253768Discussion: 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.

Bhupinder_Singh

Message from Bhupinder Singh <bsingh@opentext.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

Each user's preferences are stored in the KUAFPrefs table in the Livelink database.

The problem is in parsing the assoc datatype. If you have WebReports, use the ASSOC subtag suggestion provided in this discussion thread by Lee Butler. For those who don't have WebReports, the following example (parsing an assoc datatype in DTree.ExtendData) was created by a former Open Text colleague, Alex Kowalenko:

The data in DTree.ExtendedData encapsulates the Assoc datatype used by Livelink. For example, here is what the DTree.ExtendedData column looks like for a LiveReport:

A<1,?,'conf_names'='WIDTHOPTION,WRAPOPTION,ROW1COLOR,ROW2COLOR','conf_values'=',NOWRAP,ffffff,ffcc99','displaytype'='report','fields'={'Discussion'},'headers'={'Discussion'},'inputs'={},'oldDataId'=45039,'params'={'#ReportFields','#FilterObject:130',?,?,?,?,?,?,?,?},'recordlimit'=100,'sql'='select %1, d.name "Discussion" from dtree dtree, dtree d, dtree r where %2 and dtree.dataid *= r.parentid and dtree.ownerid = d.dataid group by %1, d.name having count(r.dataid) < 1 order by count(r.dataid) desc','subreport_id'=?,'subreport_parms'={},'subreport_path'='','title'=''>

This Oracle-specific function definition extracts a specified Assoc value from DTree.ExtendedData. It searches for '<valuekey>'='<value>' and returns <value>.

create or replace function ed_value(selid in number, valuekey in varchar2)return varchar2is edc varchar2(32000);keytag varchar2(255);vstart number(10);vend number(10);retvalue varchar2(32000);beginselect extendeddata into edc from dtree where dataid = selid;keytag := '''' || valuekey || '''=';vstart := Instr(edc,keytag);If vstart = 0 Then retvalue := null;Elsevstart := vstart + Length(keytag) + 1;vend := Instr(edc,''',''',vstart);If vend = 0 Then vend := Instr(edc,'''>',vstart); End If;If vend >= vstart Then retvalue := replace(substr(edc,vstart,vend-vstart),'\''','''');Else retvalue := null; End If;End If;return(retvalue);end;

To use this function, you can call it from within a SELECT statement, passing the parameters it wants. This example call returns all of the SQL query definitions that are embedded inside a LiveReport definition in DTree.ExtendedData.

select name, ed_value( dataid, 'sql' ) "SQL Query" from dtree where dataid in (select dataid from dtree start with dataid=26343 connect by prior dataid = parentid) and %1 (Filter LiveReport)order by name

- Bhupinder

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Tuesday, April 24, 2007 8:16 AM
To: eLink Recipient
Subject: Fav Live Report

Fav Live Report		Posted by jonathanthomson (Thomson, Jonathan) on 04/24/2007 08:10 AM
In reply to: RE Live Report to show contents of a users Favorites		Posted by bsingh (Singh, Bhupinder) on 04/23/2007 10:11 AM

Hi Bhupinder,

Thanks for the response.

So if I was only interested in the "All" Tab, or perhaps only interested in a generic "Named" tag, is it possible to produce a SQL script to extract the relevent code from the assoc type?

Where does each users preferences sit in relation to the data tables?

Thanks,

Jon

Jonathan_Thomson_(jonathanthomson_-_(deleted))

Hi Lee,This sounds interesting...My personal PrefsValue is listed below. Obviously Data ID's are added to the list as and when I add a new favorite.How can I pull out all data id's between 'Contents'=A<1,?,.....>A<1,?,1=A<1,?,'ButtonBar'=false,'Contents'=A<1,?,2004=1,64893=1,4332757=1,4943158=1,4943612=1,5435594=1,5528380=1,5860820=1,6084163=1,6147940=1,6148495=1,14009838=1,14317576=1,15641137=1,15990959=1,20406242=1,22276242=1,22285543=1>,'Dft'=false,'Label'='All','Select'=false,'System'=true>,2=A<1,?,'ButtonBar'=false,'Contents'=A<1,?,64893=1,4332757=1,4943158=1,5435594=1,5860820=1,6084163=1,6147940=1,6148495=1,14009838=1,14317576=1,15641137=1,15990959=1,20406242=1,22276242=1,22285543=1>,'Dft'=true,'Label'='Point Solutions','Select'=false,'System'=false>,3=A<1,?,'ButtonBar'=false,'Contents'=A<1,?,2004=1,4943612=1>,'Dft'=false,'Label'='ECM General','Select'=false,'System'=false>> My webroeport is something like this at present...Row section... [LL_REPTAG_1 RECARRAY:<4>: /]Any ideas?Jon

Lee_Butler

Message from Lee Butler via eLinkHi Jon,You have a nested assoc so you need something like:[LL_REPTAG_1 ASSOC:1 ASSOC:Contents /]This will return everything in the contents assoc.The key to the first assoc is actually called 1 which is not that easyto read. You then want the Contents key within assoc 1. This alsohappens to be an assoc.Hope this helps.Thanks,Lee.-----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: Mittwoch, 25. April 2007 10:12To: eLink RecipientSubject: Please can you elaboratePlease can you elaboratePosted by Thomson, Jonathan on 04/25/2007 04:05 AMHi Lee,This sounds interesting...My personal PrefsValue is listed below. Obviously Data ID's are added tothe list as and when I add a new favorite.How can I pull out all data id's between 'Contents'=A<1,?,.....>A<1,?,1=A<1,?,'ButtonBar'=false,'Contents'=A<1,?,2004=1,64893=1,4332757=1,4943158=1,4943612=1,5435594=1,5528380=1,5860820=1,6084163=1,6147940=1,6148495=1,14009838=1,14317576=1,15641137=1,15990959=1,20406242=1,22276242=1,22285543=1>,'Dft'=false,'Label'='All','Select'=false,'System'=true>,2=A<1,?,'ButtonBar'=false,'Contents'=A<1,?,64893=1,4332757=1,4943158=1,5435594=1,5860820=1,6084163=1,6147940=1,6148495=1,14009838=1,14317576=1,15641137=1,15990959=1,20406242=1,22276242=1,22285543=1>,'Dft'=true,'Label'='PointSolutions','Select'=false,'System'=false>,3=A<1,?,'ButtonBar'=false,'Contents'=A<1,?,2004=1,4943612=1>,'Dft'=false,'Label'='ECMGeneral','Select'=false,'System'=false>> My webroeport is something like this at present...Row section... [LL_REPTAG_1 RECARRAY:<4>: /]Any ideas?Jon[To reply to this thread, use your normal E-mail reply function.]============================================================Topic: Live Report to show contents of a users Favoriteshttps://knowledge.opentext.com/knowledge/llisapi.dll/open/12253768Discussion: 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.

Jonathan_Thomson_(jonathanthomson_-_(deleted))

Thanks Lee,Got that far - but what about extracting the actual Data ID's?I looked at using a slice, but ofcourse the dat ID's could be 5 numeric characters or anything up to infinity, so a slice will not extract the data ID's in a list?RECCARRAY seems to make zero difference, RECORD causes an error message and LIST says the data is not of type list.Where do I go from here?Javascript?Jon

Lee_Butler

Message from Lee Butler via eLinkHi Jon,I think I need to step back and see the big picture. What is it thatyou want to so with the dataids? What is the report trying to achieve?WebReports supports pattern matching so you could use the PATCHANGEsubtag to extract the dataids from the assoc.I think I should explain that RECARRAY and RECORD need to be used ontheir respective data types - they would never work on an assoc directly(although they might work on an item in an assoc, or an assoc may existin one of them).Thanks,Lee.-----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: Mittwoch, 25. April 2007 11:49To: eLink RecipientSubject: but....but....Posted by Thomson, Jonathan on 04/25/2007 05:44 AMThanks Lee,Got that far - but what about extracting the actual Data ID's?I looked at using a slice, but ofcourse the dat ID's could be 5 numericcharacters or anything up to infinity, so a slice will not extract thedata ID's in a list?RECCARRAY seems to make zero difference, RECORD causes an error messageand LIST says the data is not of type list.Where do I go from here?Javascript?Jon[To reply to this thread, use your normal E-mail reply function.]============================================================Topic: Live Report to show contents of a users Favoriteshttps://knowledge.opentext.com/knowledge/llisapi.dll/open/12253768Discussion: 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.

Jonathan_Thomson_(jonathanthomson_-_(deleted))

Hi Lee,Would you be able to give me a call on 02073208743 to discuss?I am trying to produce a live-report and web-report to display the contents of a users favorites folder.Cheers,Jon

Lee_Butler

Message from Lee Butler via eLinkHi all,I wanted to write this up so that others can make use of the sametechnique. The requirement was for a report that can present a usersfavorites in a series of hyperlinks. By doing this and then calling thereport from a Customview we remove the need for the user to navigate tothe favorites page - which was important for this project. The first element is a LiveReport that returns the raw data from thetable kuafprefs: select prefsvalue from kuafprefs where prefskeyword='PersonalFavoritesTabs' and prefsid=%1%1 is defined as a USERID parameter type in the LiveReport.The LiveReport is now returning a single column called prefsvalue. Allthe values in the column relate to the user running the report. Thecolumn might look like:A<1,?,1=A<1,?,'ButtonBar'=false,'Contents'=A<1,?,2004=1,64893=1,4332757=1,4943158=1,4943612=1,5435594=1,5528380=1,5860820=1,6084163=1,6147940=1,6148495=1,14009838=1,14317576=1,15641137=1,15990959=1,20406242=1,22276242=1,22285543=1>,'Dft'=false,'Label'='All','Select'=false,'System'=true>,2=A<1,?,'ButtonBar'=false,'Contents'=A<1,?,64893=1,4332757=1,4943158=1,5435594=1,5860820=1,6084163=1,6147940=1,6148495=1,14009838=1,14317576=1,15641137=1,15990959=1,20406242=1,22276242=1,22285543=1>,'Dft'=true,'Label'='PointSolutions','Select'=false,'System'=false>,3=A<1,?,'ButtonBar'=false,'Contents'=A<1,?,2004=1,4943612=1>,'Dft'=false,'Label'='ECMGeneral','Select'=false,'System'=false>>Next we need to access the value of the Contents field. This fieldexists inside 2 assoc data structures which have keys of "1" and"Contents" respectively. To do this we need a WebReport - so add a newWebReport that users the LiveReport above as it's data source. Edit theWebReport so that it contains nothing other than the following line inthe row section:[LL_REPTAG_1 ASSOC:1 ASSOC:Contents /]If you run the report at this point you will see results of:A<1,?,2004=1,64893=1,4332757=1,4943158=1,4943612=1,5435594=1,5528380=1,5860820=1,6084163=1,6147940=1,6148495=1,14009838=1,14317576=1,15641137=1,15990959=1,20406242=1,22276242=1,22285543=1>What we want to do now is turn this into a list of comma separateddataids. To do this use the REPLACE subtag to strip both ends of thestring then we use PATCHANGE to extract the dataids. This could looklike:[LL_REPTAG_1 ASSOC:1 ASSOC:Contents REPLACE:"A<1,?,":"" REPLACE:">":""PATCHANGE:<[0-9]+>@=[1-3]:#1 /]This looks a little complex so I'll break down what the individualcomponents are doing.REPLACE:"A<1,?,":"" strips out the <1,?, and replaces it with nothingmeaning you now have:2004=1,64893=1,4332757=1,4943158=1,4943612=1,5435594=1,5528380=1,5860820=1,6084163=1,6147940=1,6148495=1,14009838=1,14317576=1,15641137=1,15990959=1,20406242=1,22276242=1,22285543=1>REPLACE:">":"" strips out the > and replaces it with nothing whichleaves:2004=1,64893=1,4332757=1,4943158=1,4943612=1,5435594=1,5528380=1,5860820=1,6084163=1,6147940=1,6148495=1,14009838=1,14317576=1,15641137=1,15990959=1,20406242=1,22276242=1,22285543=1PATCHANGE:<[0-9]+>@=[1-3]:#1[0-9] looks for a digit between 0 and 9[0-9]+ adding the plus makes it look for multiple occurrences of 0 to 9<[0-9]+> the angular brackets makes the number available for referencewith the # symbol@= is simply an escaped equals sign[1-3] finds a single digit between 1 and 3: specifies that the replacement string comes next#1 is the replacement string which takes everything from within theangular brackets <>In short it first finds 2004=1 then replaces it with 2004 then it finds64893=1 and replace it with 64893. The commas are left in tact from theoriginal as we're not looking at or replacing them.This leaves us with a comma separated list of dataids:2004,64893,4332757,4943158,4943612,5435594,5528380,5860820,6084163,6147940,6148495,14009838,14317576,15641137,15990959,20406242,22276242,22285543The next part of this is to take these dataids and turn them into a listof hyperlinks. To do this requires a bit of a workaround as we need aSub WebReport which accepts this list of dataids and displays thehyperlinks.Create a second LiveReport: select dataid from dtree where dataid in (%1)%1 is a user input parameter of type insertString.If you run this LiveReport and pass in the comma separated list ofdataids you will see that we now have each dataid on it's own row -which allows us to work on them individually in the Sub WebReport.Create another WebReport (this is going to be our Sub WebReport) and usethe LiveReport we just created as a data source. This is what the useris going to see in their output so it needs to be formatted accordingly.It might look like:[LL_WEBREPORT_STARTROW /][LL_WEBREPORT_ENDROW /]

Favorite	Function Menu
[LL_REPTAG=DATAID NODEINFO:NAME /]	[LL_REPTAG=DATAID LLURL:FUNCTIONMENU /]

As you can see, it's going to be a visual extravaganza :-)This is a simple report with one favorite on each row with a hyperlinkpointing to the item itself. It also has a second column that containsthe function menu for the particular favorite.The final step is to call the Sub WebReport from the main report. Editthe main report so that each row (in reality we will only ever have onerow) now calls the Sub WebReport. This might look like:[LL_WEBREPORT_SUBWEBREPORT NODEID:**** PARM:inputLabel1:'[LL_REPTAG_1ASSOC:1 ASSOC:Contents REPLACE:"A<1,?,":"" REPLACE:">":""PATCHANGE:<[0-9]+>@=[1-3]:#1 /]' /]**** is the dataid of the Sub WebReport (it would be better to use aconstant here rather than hard code it).The above might look complex but the reality is that it was achieved ina couple of hours work once the requirement was understood.Hope others find this useful.Thanks,Lee.-----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: Mittwoch, 25. April 2007 12:22To: eLink RecipientSubject: CallCallPosted by Thomson, Jonathan on 04/25/2007 06:13 AMHi Lee,Would you be able to give me a call on 02073208743 to discuss?I am trying to produce a live-report and web-report to display thecontents of a users favorites folder.Cheers,Jon