Custom attributes in livereports v9

Ursula_Krische_(SIS-User_(Delete)_1424740)

I am having problems with my reports on custom attributes and categories in v9.0. In 8.1.5 I use the following easy code to get a user to his projects and current votings. However I do not really get the new schema. Any tips on getting the following report code to work in v9.0?---------------------select %2, B.se_doc_title_en, B.se_ballot_close_date from Dtree , Rostningsdokumenttbl B where Dtree.dataid=B.dataid and B.se_resp_expert=%1 and (Dtree.subtype=136) order by B.se_ballot_close_date------------------------In this code %2 is Reportfields and %1 is a user input.

Find more posts tagged with

Comments

eLink User

Message from Alex Kowalenko via eLinkOpen Text Support can answer your question. I already forwarded yourquestion to support@opentext.com-----Original Message-----From: knowledge@opentext.com [mailto:knowledge@opentext.com]On Behalf OfeLink Discussion: Livelink LiveReports DiscussionSent: Wednesday, November 28, 2001 10:41To: eLink RecipientSubject: Custom attributes in livereports v9Custom attributes in livereports v9Posted by SIS-User on 11/28/2001 10:36 AMI am having problems with my reports on custom attributes and categories inv9.0. In 8.1.5 I use the following easy code to get a user to his projectsand current votings. However I do not really get the new schema. Any tips ongetting the following report code to work in v9.0?---------------------select %2, B.se_doc_title_en, B.se_ballot_close_date from Dtree ,Rostningsdokumenttbl B where Dtree.dataid=B.dataid and B.se_resp_expert=%1and (Dtree.subtype=136) order by B.se_ballot_close_date------------------------In this code %2 is Reportfields and %1 is a user input.[To reply to this thread, use your normal e-mail reply function.]============================================================Discussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=viewLivelink Server:https://knowledge.opentext.com/knowledge/livelink.exe

eLink User

Message from Sean M Alderman via eLinkBear with me this is a long note.The 9.x attribute schema is fairly complex. Because I don't quite knowthe structure of your category, let me start with this idea -IMHO, the key to understanding the new structure at the db level isknowing that different attribute value types are stored in differentfields. You need to look at the version 9 schema and see that theLLAttrData table has several fields prefixed with "val", for examplevalstr, valdate, vallong.Each of these val fields are numberically related to theLLAttrData.AttrType field. In order to get that relationship we need toeither be intelligently looking at the data (meaning with our own eyes)or doing something nifty with the catregionmap, specifically theCatRegionMap.RegionName field.Ok... Lets say I have a category with a DTree.DataID of 1736743 (forsimplicity we'll just have two attribs in the category). I'm a livereport and I don't necessarily know what Attribs are in that category. So let's do some sql to figure that out -select * from catregionmap where catid = 1736743;CatID CatName SetName AttrName RegionName1736743 'Whatever' 'Attrib1' 'Attr_1736743_2'1736743 'Whatever' 'Attrib2' 'Attr_1736743_3'Ok so using the region name we can link the CatRegionMap.AttrName fieldto the LLAttrData.AttID field ensuring that we get the right data forthe right attribute in the same row. How might we do this?...I've usedsql concatenation, something like - ( ... from catregionmap c,llattrdata l where c.regionname = 'Attr_'||l.defid||'_'||l.attrid ...) So 'Attr_'||l.defid||'_'||l.attrid really ends up looking likeAttr_1736743_2 for instance. This is the only way I could figure outhow to link the name of an attribute to it's value.So now that my live report knows how to do that, how do I know whichfield the value is in? This is a tricky one too. I went and setup acategory with every type of attribute just to see how this works. Idon't have any nice logic from my reasearch, only these hints -Attrib Type LLAttrData.AttrType Value FieldCategory -18 NoneDate -7 ValDateDate Popup 13 ValDateFlag 5 ValIntInteger 2 ValIntInt Popup 12 ValIntText Single -1 ValStrText Multi 11 ValLongText Popup 10 ValStrUser 14 ValIntSo where do we go from here... Next in line is versions. You need tomake sure you're looking at the most current version's attributes. Thisis stored in LLAttrData.VerNum, and should probably be matched againstDTree.VersionNum.I have yet to figure out to make a live report that can, given an objectid, report on the category assigned, the attribute names and valuesassigned. The only way I can get values seems to be if I know whatvalue I'm looking for, as opposed to getting the sql to figure it outfor me.All that being said, in order to display the se_doc_title_en andse_ballot_close_date you'll have to deduce what attrID's they correspondto as I showed above. Since you've got two attribs to display you'regoing to have to link into the LLAttrData Table for each attrib (askingto display valstr and valdate from the same row in LLAttrData ispointless, only one will have a value). I'm going to assume that Titleis a text field and Date is a Date field. So you'll need to dosomething like this -select %2, a.valstr, b.valdate from dtree d, llattrdata a, llattrdata bwhere ..... you'll have to make sure somehow that a and b are linkedand the records match correctly.I imagine you'll have to do a subquery inside your query that matchesthe user input to the LLAttrData.id field for the docuements produced. Perhaps something like -select %2, a.valstr, b.valdate from dtree d, llattrdata a, llattrdata bwhere d.dataid in (select id from llattrdata where valstr = %1 ... andperhaps do something to make sure it's really the id you need, likecheck the category ID ...) and a.id = d.dataid and b.id = d.dataid andd.subtype = 136order by b.valdateDoes that help?eLink Discussion: Livelink LiveReports Discussion wrote:> > Custom attributes in livereports v9> Posted by SIS-User on 11/28/2001 10:36 AM> > I am having problems with my reports on custom attributes and categories in v9.0. In 8.1.5 I use the following easy code to get a user to his projects and current votings. However I do not really get the new schema. Any tips on getting the following report code to work in v9.0?> > ---------------------> select %2, B.se_doc_title_en, B.se_ballot_close_date from Dtree , Rostningsdokumenttbl B where Dtree.dataid=B.dataid and B.se_resp_expert=%1 and (Dtree.subtype=136) order by B.se_ballot_close_date> ------------------------> > In this code %2 is Reportfields and %1 is a user input.> > [To reply to this thread, use your normal e-mail reply function.]> > ============================================================> > Discussion: Livelink LiveReports Discussion> https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=view> > Livelink Server:> https://knowledge.opentext.com/knowledge/livelink.exe-- Sean M. AldermanITRACK Systems AnalystPACE/NCI - NASA Glenn Research Center(216) 433-2795