Home
Extended ECM
API, SDK, REST and Web Services
Livereport on Category data
Cynthia_Philpott
I'm still confused about how to produce a decent report using and displaying category data. I've created a report that selects based on a value in a category field and displays data from a single category joined with dtree. But how do you produce a report that joins dtree with category data and displays multiple attribute data. You know how we used to do easily in v8? Although I've read through the discussion items and I see the question asked a number of ways I've never seen it answered except for a description of the new tables. Has anyone at livelink developed a view or even a guide to users on how one might come close to replicating V8 category reports? What I want to do is be able to select on a status value (status is a category) and report out the following category attributesdrawing norev notitlelocation along with a link to the associated document in a livereport.I want one row for each document selected.Is this even possible anymore?
Find more posts tagged with
Comments
eLink User
Message from Sean M Alderman via eLinkI had a fancy query to do this once. I don't know that I've still gotit around, but the basic jist of it went like this -There's a relationship between the CatRegionMap.RegionName field and theLLAttrData.AttrID field. That relationship goes like this - CatRegionMap.RegionName = "Attr_" + LLAttrData.DefID + "_" +LLAttrData.AttrIDFor instance, run this query from a live report -Select a.* from catregionmap a, llattrdata b whereb.ID = and a.regionname = ( 'Attr_' || b.DefID || '_' || b.AttrID )You'll get the Category Definition, which is nifty, but useless.What you can do with this knowledge however is set yourself up tospecify an attribute by name...perhaps with out knowing what category itbelongs to (if it's a uniquely named attrib). This might help you alittle along the way, but it's not going to get you where you need togo.The part I struggle with in using reports on cat/attr stuff is that Inever really know what kind of attribute I'm dealing with. This is areal problem for livereports as I see it because there are 5 data typedvalue fields in LLAttrData. I can't realistically make a nice lookingreport that does this -select a.name, b.valstr, b.valint, b.valdate, b.vallong, b.valreal fromllattrdata b, dtree a where ...and expect to get a nice report like -Document Name | Attribute Value------------------------------------------...it's going to look nasty like -Document Name | String Attribute | Integer Attribute | ...etc.-------------------------------------------------------------------This is something I don't know how to get around in a livereport...Maybesomeone at opentext has a solution for this. I have been looking for itsince 9 came out. What I can tell you is that there is a correlationbetween LLAttrData.AttrType and which Val field to grab. For instancean AttrType of -18 represents a category name in ValStr, -1 representsa MultiLine Text Attrib in ValStr, -7 is a date value in ValDate, and soon. Like I said I don't really know how to use this in a livereport,but I can use it to make queries (like what you want) with a sqlcommandline, perl script (with DBD and DBI), Java, or OScript program. Someone recently submitted an Excel VBA thing which can directly run andimport LiveReports...I'm sure that could be used to make the propercondition testing happen to determine which Val to display.BTW, I don't have a complete list of the AttrType values, what they are,and how they correspond to Val fields in the same record. PerhapsOT could post that for us all?On Fri, 2002-05-17 at 15:00, eLink Discussion: Livelink LiveReportsDiscussion wrote:> Livereport on Category data> Posted by UCLLUser1 on 05/17/2002 02:59 PM> > I'm still confused about how to produce a decent report using and displaying category data. I've created a report that selects based on a value in a category field and displays data from a single category joined with dtree. But how do you produce a report that joins dtree with category data and displays multiple attribute data. You know how we used to do easily in v8? Although I've read through the discussion items and I see the question asked a number of ways I've never seen it answered except for a description of the new tables. Has anyone at livelink developed a view or even a guide to users on how one might come close to replicating V8 category reports? What I want to do is be able to select on a status value (status is a category) and report out the following category attributes> drawing no> rev no> title> location > along with a link to the associated document in a livereport.> I want one row for each document selected.> > Is this even possible anymore?> > > [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-2795Calling a windowed operating system "Windows" is like naming anautomobile "Wheels."
eLink User
Message from Alex Kowalenko via eLinkThere are three topics here:1. Select multiple attributes for a node----------------------------------------In the SQL FROM clause repeat LLAttrData and in the WHERE clause repeatLLAttrData conditions for every attribute to be selected: SELECT ... FROM DTree d, LLAttrData a1, LLAttrData a2, LLAttrData a3, ... WHERE d.DataID = a1.ID and d.VersionNum = a1.VerNum and a1.EntryNum = 1 and /* use only if first value wanted */ a1.DefID = and a1.AttrID = << repeat for a2, a3, ... >> ...2. Select attribute values by name----------------------------------Use Sean's CatRegionMap technique as a hint to replace the <... definitionID #>'s with names.3. Select the right attribute column------------------------------------To automatically select the right column from LLAttrData, use the AttrTypevalues.An Oracle clause for this would use Decode: DECODE( ai.AttrType, -7, ai.ValDate, /* Date field */ -4, ai.ValReal, /* Real field */ -1, ai.ValStr, /* String field */ 2, ai.ValInt, /* Integer field */ 5, ai.ValInt, /* Boolean field */ 10, ai.ValStr, /* String popup */ 11, ai.ValLong, /* String multiline */ 12, ai.ValInt, /* Integer popup */ 13, ai.ValDate, /* Data popup */ 14, ai.ValInt, /* User field */ 20, ai.ValReal, /* Real popup */ '???' )An MSSQL clause for this would use CASE: CASE ai.AttrType WHEN -7 THEN ai.ValDate /* Date field */ WHEN -4 THEN ai.ValReal /* Real field */ WHEN -1 THEN ai.ValStr /* String field */ WHEN 2 THEN ai.ValInt /* Integer field */ WHEN 5 THEN ai.ValInt /* Boolean field */ WHEN 10 THEN ai.ValStr /* String popup */ WHEN 11 THEN ai.ValLong /* String multiline */ WHEN 12 THEN ai.ValInt /* Integer popup */ WHEN 13 THEN ai.ValDate /* Data popup */ WHEN 14 THEN ai.ValInt /* User field */ WHEN 20 THEN ai.ValReal /* Real popup */ ELSE '???' ENDExercise--------A useful exercise to practise this knowledge is to write a LiveReport toselect attribute values by: a) category name, b) attribute name, and c) nodeid or name.--Alex KowalenkoSenior Consultant - Professional ServicesOpen Text Corporation+1 (613) 838-5220-----Original Message-----From: knowledge@opentext.com [mailto:knowledge@opentext.com]On Behalf OfeLink Discussion: Livelink LiveReports DiscussionSent: Friday, May 17, 2002 16:10To: eLink RecipientSubject: Re Livereport on Category dataRe Livereport on Category dataPosted by eLink on 05/17/2002 04:09 PMMessage from Sean M Alderman via eLinkI had a fancy query to do this once. I don't know that I've still gotit around, but the basic jist of it went like this -There's a relationship between the CatRegionMap.RegionName field and theLLAttrData.AttrID field. That relationship goes like this -CatRegionMap.RegionName = "Attr_" + LLAttrData.DefID + "_" +LLAttrData.AttrIDFor instance, run this query from a live report -Select a.* from catregionmap a, llattrdata b whereb.ID = anda.regionname = ( 'Attr_' || b.DefID || '_' || b.AttrID )You'll get the Category Definition, which is nifty, but useless.What you can do with this knowledge however is set yourself up tospecify an attribute by name...perhaps with out knowing what category itbelongs to (if it's a uniquely named attrib). This might help you alittle along the way, but it's not going to get you where you need togo.The part I struggle with in using reports on cat/attr stuff is that Inever really know what kind of attribute I'm dealing with. This is areal problem for livereports as I see it because there are 5 data typedvalue fields in LLAttrData. I can't realistically make a nice lookingreport that does this -select a.name, b.valstr, b.valint, b.valdate, b.vallong, b.valreal fromllattrdata b, dtree a where ...and expect to get a nice report like -Document Name | Attribute Value------------------------------------------...it's going to look nasty like -Document Name | String Attribute | Integer Attribute | ...etc.-------------------------------------------------------------------This is something I don't know how to get around in a livereport...Maybesomeone at opentext has a solution for this. I have been looking for itsince 9 came out. What I can tell you is that there is a correlationbetween LLAttrData.AttrType and which Val field to grab. For instancean AttrType of -18 represents a category name in ValStr, -1 representsa MultiLine Text Attrib in ValStr, -7 is a date value in ValDate, and soon. Like I said I don't really know how to use this in a livereport,but I can use it to make queries (like what you want) with a sqlcommandline, perl script (with DBD and DBI), Java, or OScript program.Someone recently submitted an Excel VBA thing which can directly run andimport LiveReports...I'm sure that could be used to make the propercondition testing happen to determine which Val to display.BTW, I don't have a complete list of the AttrType values, what they are,and how they correspond to Val fields in the same record. PerhapsOT could post that for us all?On Fri, 2002-05-17 at 15:00, eLink Discussion: Livelink LiveReportsDiscussion wrote:> Livereport on Category data> Posted by UCLLUser1 on 05/17/2002 02:59 PM>> I'm still confused about how to produce a decent report using anddisplaying category data. I've created a report that selects based on avalue in a category field and displays data from a single category joinedwith dtree. But how do you produce a report that joins dtree with categorydata and displays multiple attribute data. You know how we used to doeasily in v8? Although I've read through the discussion items and I see thequestion asked a number of ways I've never seen it answered except for adescription of the new tables. Has anyone at livelink developed a view oreven a guide to users on how one might come close to replicating V8 categoryreports? What I want to do is be able to select on a status value (statusis a category) and report out the following category attributes> drawing no> rev no> title> location> along with a link to the associated document in a livereport.> I want one row for each document selected.>> Is this even possible anymore?>>> [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-2795Calling a windowed operating system "Windows" is like naming anautomobile "Wheels."[To reply to this thread, use your normal e-mail reply function.]============================================================Topic: Livereport on Category data
https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2736023&objAction=viewDiscussion
: Livelink LiveReports Discussion
https://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 eLinkAlex, You are Awesome!!! Thanks for the little oracle decode statement! Ihadn't thought of trying to work that out like that. You should make atechtip for each of these cat/attr problem topics :)On selecting multiple attributes for a node, is it possible to do thiswith out knowing what category(s) a node has been assigned? Meaning todynamically report all the attributes with out knowing how many thereare?I know there's a lot of Oscript that deals with returning categories andattribs on a node, and I've found it to be fairly easy to work with(although it's got a high learning curve). Perhaps a future version ofthe LiveReport object will have the capability to use those OscriptObjects to get Cats/Attrs for a node like the Document Filter does todisplay items more like a folder does. Of course, that would makethings way too easy on us! :)On Fri, 2002-05-17 at 17:00, eLink Discussion: Livelink LiveReportsDiscussion wrote:> RE Re Livereport on Category data> Posted by eLink on 05/17/2002 05:00 PM> > Message from Alex Kowalenko via eLink> > There are three topics here:> > 1. Select multiple attributes for a node> ----------------------------------------> > In the SQL FROM clause repeat LLAttrData and in the WHERE clause repeat> LLAttrData conditions for every attribute to be selected:> > SELECT> ...> FROM> DTree d,> LLAttrData a1,> LLAttrData a2,> LLAttrData a3,> ...> WHERE> d.DataID = a1.ID and> d.VersionNum = a1.VerNum and> a1.EntryNum = 1 and /* use only if first value wanted */> a1.DefID = and> a1.AttrID = > << repeat for a2, a3, ... >>> ...> > 2. Select attribute values by name> ----------------------------------> > Use Sean's CatRegionMap technique as a hint to replace the <... definition> ID #>'s with names.> > 3. Select the right attribute column> ------------------------------------> > To automatically select the right column from LLAttrData, use the AttrType> values.> > An Oracle clause for this would use Decode:> > DECODE( ai.AttrType,> -7, ai.ValDate, /* Date field */> -4, ai.ValReal, /* Real field */> -1, ai.ValStr, /* String field */> 2, ai.ValInt, /* Integer field */> 5, ai.ValInt, /* Boolean field */> 10, ai.ValStr, /* String popup */> 11, ai.ValLong, /* String multiline */> 12, ai.ValInt, /* Integer popup */> 13, ai.ValDate, /* Data popup */> 14, ai.ValInt, /* User field */> 20, ai.ValReal, /* Real popup */> '???' )> > An MSSQL clause for this would use CASE:> > CASE ai.AttrType> WHEN -7 THEN ai.ValDate /* Date field */> WHEN -4 THEN ai.ValReal /* Real field */> WHEN -1 THEN ai.ValStr /* String field */> WHEN 2 THEN ai.ValInt /* Integer field */> WHEN 5 THEN ai.ValInt /* Boolean field */> WHEN 10 THEN ai.ValStr /* String popup */> WHEN 11 THEN ai.ValLong /* String multiline */> WHEN 12 THEN ai.ValInt /* Integer popup */> WHEN 13 THEN ai.ValDate /* Data popup */> WHEN 14 THEN ai.ValInt /* User field */> WHEN 20 THEN ai.ValReal /* Real popup */> ELSE '???'> END> > Exercise> --------> > A useful exercise to practise this knowledge is to write a LiveReport to> select attribute values by: a) category name, b) attribute name, and c) node> id or name.> > --> Alex Kowalenko> Senior Consultant - Professional Services> Open Text Corporation> +1 (613) 838-5220> > -----Original Message-----> From: knowledge@opentext.com [mailto:knowledge@opentext.com]On Behalf Of> eLink Discussion: Livelink LiveReports Discussion> Sent: Friday, May 17, 2002 16:10> To: eLink Recipient> Subject: Re Livereport on Category data> > > Re Livereport on Category data> Posted by eLink on 05/17/2002 04:09 PM> > Message from Sean M Alderman via eLink> > I had a fancy query to do this once. I don't know that I've still got> it around, but the basic jist of it went like this -> > There's a relationship between the CatRegionMap.RegionName field and the> LLAttrData.AttrID field. That relationship goes like this -> > CatRegionMap.RegionName = "Attr_" + LLAttrData.DefID + "_" +> LLAttrData.AttrID> > For instance, run this query from a live report -> > Select a.* from catregionmap a, llattrdata b where> b.ID = and> a.regionname = ( 'Attr_' || b.DefID || '_' || b.AttrID )> > You'll get the Category Definition, which is nifty, but useless.> > What you can do with this knowledge however is set yourself up to> specify an attribute by name...perhaps with out knowing what category it> belongs to (if it's a uniquely named attrib). This might help you a> little along the way, but it's not going to get you where you need to> go.> > The part I struggle with in using reports on cat/attr stuff is that I> never really know what kind of attribute I'm dealing with. This is a> real problem for livereports as I see it because there are 5 data typed> value fields in LLAttrData. I can't realistically make a nice looking> report that does this -> > select a.name, b.valstr, b.valint, b.valdate, b.vallong, b.valreal from> llattrdata b, dtree a where ...> > and expect to get a nice report like -> Document Name | Attribute Value> ------------------------------------------> ...> > it's going to look nasty like -> Document Name | String Attribute | Integer Attribute | ...etc.> -------------------------------------------------------------------> > This is something I don't know how to get around in a livereport...Maybe> someone at opentext has a solution for this. I have been looking for it> since 9 came out. What I can tell you is that there is a correlation> between LLAttrData.AttrType and which Val field to grab. For instance> an AttrType of -18 represents a category name in ValStr, -1 represents> a MultiLine Text Attrib in ValStr, -7 is a date value in ValDate, and so> on. Like I said I don't really know how to use this in a livereport,> but I can use it to make queries (like what you want) with a sql> commandline, perl script (with DBD and DBI), Java, or OScript program.> > Someone recently submitted an Excel VBA thing which can directly run and> import LiveReports...I'm sure that could be used to make the proper> condition testing happen to determine which Val to display.> > BTW, I don't have a complete list of the AttrType values, what they are,> and how they correspond to Val fields in the same record. Perhaps> OT could post that for us all?> > > > On Fri, 2002-05-17 at 15:00, eLink Discussion: Livelink LiveReports> Discussion wrote:> > Livereport on Category data> > Posted by UCLLUser1 on 05/17/2002 02:59 PM> >> > I'm still confused about how to produce a decent report using and> displaying category data. I've created a report that selects based on a> value in a category field and displays data from a single category joined> with dtree. But how do you produce a report that joins dtree with category> data and displays multiple attribute data. You know how we used to do> easily in v8? Although I've read through the discussion items and I see the> question asked a number of ways I've never seen it answered except for a> description of the new tables. Has anyone at livelink developed a view or> even a guide to users on how one might come close to replicating V8 category> reports? What I want to do is be able to select on a status value (status> is a category) and report out the following category attributes> > drawing no> > rev no> > title> > location> > along with a link to the associated document in a livereport.> > I want one row for each document selected.> >> > Is this even possible anymore?> >> >> > [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. Alderman> ITRACK Systems Analyst> PACE/NCI - NASA Glenn Research Center> (216) 433-2795> > Calling a windowed operating system "Windows" is like naming an> automobile "Wheels."> > [To reply to this thread, use your normal e-mail reply function.]> > ============================================================> > Topic: Livereport on Category data>
https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2736023&>
; objAction=view> > 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>
; > [To reply to this thread, use your normal e-mail reply function.]> > ============================================================> > Topic: Livereport on Category data>
https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2736023&objAction=view>
; > 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-2795Calling a windowed operating system "Windows" is like naming anautomobile "Wheels."
Atiya_Sultana
Hi,Can you help me know how I can create a LiveReport clicking on which it should show ask the user to pick up a particular attribute values and based on the input, all the objects and its location should be shown.I need it urgently. Could anyone help?Many Thanks,Atiya.
Lindsay_Davies
Message from Lindsay Davies <
ldavies@opentext.com
> via eLink
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink
Hi Atiya,
While you could probably do this with a LiveReport, it is going to be very messy and complicated.
You have not described the attribute/category and that is quite key.
So, I would say that this sort of thing is exactly that the advanced Search is for!
If you do want to proceed with a LiveReport, then search through this forum as there are plenty of discussions about this.
Regards
Lindsay
From:
eLink Discussion: Open Text Live Reports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent:
2009 December 23, Wed 11:20
To:
eLink Recipient
Subject:
Hi,
Hi,
Posted by
atiya.sultana@accenture.com
(Sultana, Atiya) on 2009/12/23 06:16
In reply to:
Re RE Re Livereport on Category data
Posted by eLink on 2002/05/20 09:49
Hi,
Can you help me know how I can create a LiveReport clicking on which it should show ask the user to pick up a particular attribute values and based on the input, all the objects and its location should be shown.
I need it urgently. Could anyone help?
Many Thanks,
Atiya.
Appu_Nair
Hi Atiya,The standard livereport interface allows you youu to pick a folder,container, etc. Are you saying that you want the user to select a category and drill down to the attribute and run your query?That is probably not possible out of box. You can with the help of web reports do this again with some effort.I would just create a Livelink search query because that is exactly what your requirement is. Does your organization or people not like Livelink search for some reason?
Atiya_Sultana
Hi Appu,Advanced search gives the results on the basis of an attribute value. That's correct. Actually, we are looking for data in the form ObjectName, Attribute Value, Location, DataID. And then export this data to excel.Do you see any way to achieve this?Many Thanks,Atiya.
Appu_Nair
if you persists on this course everything is possible.Livelink has an excellent indexing mechanism so that all of this is available to you.ObjectName standard search regionAttributeValue-You have to enable the search region in your search manager and it will show up in the search result.Location-standard search region that is what you see when you view source (2000|12345|45678) that kind the bread trail is the objectid.DataID-Dtree.Dataid or objectid that is definitely there.Now if you really are new to livelink search start with a small set and go into your search manager and look for regions by clicking the function button.All your attributes are available make all of the three buttons active.See the online help on what it does.Now the excel part.Excel export is not possible.However search can be shown in XML.By way of slight programming or tweaks you can then convert this xml outout to excel.My Humble opinion.I do not know how unless I do it.If you have web reports you can use the search query as a source and voila one of the options is export as CSV.
Craig_Stanley
We use Microsoft Reporting Services to run scheduled reports exporting direct to Excel. You can also do this using Wep Reports but this is an Expensive and strictly licensed product. Microsoft Reporting Services provide some great options especially using the free add on MSCHARTHere is a sample report looking at Document attributes. You will need to change the llattrdata references but this should give you a start. select dtree.name ObjectName,max (DECODE (llattrdata.attrid, 2, valstr, NULL)) Region, max (DECODE (llattrdata.attrid, 3, valstr, NULL)) Town_Tab, max (DECODE (llattrdata.attrid, 5, valstr, NULL)) House_No, max (DECODE (llattrdata.attrid, 6, valstr, NULL)) House_Name,max (DECODE (llattrdata.attrid, 7, valstr, NULL)) Road_Tab,max (DECODE (llattrdata.attrid, 8, valstr, NULL)) District_Village,max (DECODE (llattrdata.attrid, 9, valstr, NULL)) Town, max (DECODE (llattrdata.attrid, 10, valstr, NULL)) Road, max (DECODE (llattrdata.attrid, 11, valstr, NULL)) PRN,llattrdata.id,parentid Folder from dtree, llattrdata where dtree.dataid=llattrdata.id and valstr not like 'Y' group by dtree.name,parentid,llattrdata.idHere is a sample report looking at workflow AttributesSELECT WF_ID,TO_DATE(MAX( case when wf_attrid = 7 then wf_valstr end),'yyyy-mm-dd') As Start_Date,TO_DATE(MAX( case when wf_attrid = 8 then wf_valstr end),'yyyy-mm-dd') As End_date,MAX( case when wf_attrid = 6 then wf_valstr end) As Venue,MAX( case when wf_attrid = 5 then wf_valstr end) As Course_Title,MAX( case when wf_attrid = 3 then wf_valstr end) As Delegate,TO_NUMBER(MAX( case when wf_attrid = 53 then wf_valstr end)) As Question1,MAX( case when wf_attrid = 65 then TO_CHAR(wf_vallong) when wf_attrid = 47 then wf_valstr end) As Question1Comments,TO_NUMBER(MAX( case when wf_attrid = 54 then wf_valstr end)) As Question2,MAX( case when wf_attrid = 64 then TO_CHAR(wf_vallong) when wf_attrid = 48 then wf_valstr end) As Question2Comments,TO_NUMBER(MAX( case when wf_attrid = 55 then wf_valstr end)) As Question3,MAX( case when wf_attrid = 63 then TO_CHAR(wf_vallong) when wf_attrid = 49 then wf_valstr end) As Question3Comments,TO_NUMBER(MAX( case when wf_attrid = 56 then wf_valstr end)) As Question4,MAX( case when wf_attrid = 62 then TO_CHAR(wf_vallong) when wf_attrid = 50 then wf_valstr end) As Question4Comments,TO_NUMBER(MAX( case when wf_attrid = 57 then wf_valstr end)) As Question5,MAX( case when wf_attrid = 61 then TO_CHAR(wf_vallong) when wf_attrid = 51 then wf_valstr end) As Question5Comments,TO_NUMBER(MAX( case when wf_attrid = 58 then wf_valstr end)) As Question6,MAX( case when wf_attrid = 60 then TO_CHAR(wf_vallong) when wf_attrid = 52 then wf_valstr end) As Question6Comments,MAX( case when wf_attrid = 59 then TO_CHAR(wf_vallong) when wf_attrid = 46 then wf_valstr end) As Additional_CommentsFROM WFATTRDATAwhere WF_ID IN (select SUBWORK_WORKID from WSUBWORK where SUBWORK_MAPID IN ( SELECT MAP_MAPID FROM WMAP WHERE MAP_MAPOBJID = 44902651) )having (MAX( case when wf_attrid = 5 then wf_valstr end) = :TrainingCourseTitle OR 'All' = :TrainingCourseTitle)AND (TO_DATE(MAX( case when wf_attrid = 7 then wf_valstr end),'yyyy-mm-dd') = :StartDate OR :StartDate IS NULL)AND (TO_DATE(MAX( case when wf_attrid = 8 then wf_valstr end),'yyyy-mm-dd') = :EndDate OR :EndDate IS NULL)GROUP BY WF_IDORDER BY WF_ID
Appu_Nair
That I think is an excellent cost effective solution I would add these two comments.WR is expensive but it is more than a reporting tool.Many en masse changes that we used to do via lapi/oscript have smart tags now built with WR so changing things in livelink is pretty easy.Maybe a better business model for Resonate is to do a WR Lite just for reporting and give it free. Keep the manipulation tags as your IP stuff and brings you money.Lee listening ?Since I don't really have a RDBMS mind I like the way that OT has a KB article that allows you to flatten the llattrdata.I think you are doing the MAX functions to do exactly that.I always create a view of the llattrdata which makes the query much more understanadable and readable.BTW where is microsft reporting services in a server,Is it realted to sqlserver by any chance or should I be able to see it in my computer
Atiya_Sultana
Thanks for the reply.In which table the regions are stored? I want to pull these fields from the database.Thanks,Atiya.
Appu_Nair
First make a determination.If you are going the llattrdata way use a query to do it.Maybe something that Craig Stanley just posted will help you make it into excel.If you go the advanced search route like what I and Lindsay mentioned then you have to use livelink search in its entirety. No database tables are involved here as regions are part of the livelink search index and not in anyway stored in the livelink schema.
Appu_Nair
There also is an extremely easy way to get results in excel.MS EXcel has an option of running a web query.If you put the livereport URL as the source it will get into excel.You just have to do a littlemanipulation in formatting.Here's alink in tek tips that explains how to do it
http://www.tek-tips.com/viewthread.cfm?qid=1411407
Atiya_Sultana
Thanks for the reply Appu.Could you please suggest how to remove the empty and null rows in the AttributeValue column for the below query? Not sure why the results display the rows with corresponding attribute values as well the same row is repeated with blank attribute value.WITH recursive_tempDTREE (level, parentid, dataid, name, Subtype, Pathstr ) AS ((SELECT 1, parentid, dataid, Name, Subtype as ObjectType, CAST ('' as VARCHAR(MAX)) FROM dtree (nolock) WHERE parentid = XXXX) UNION ALL (SELECT level + 1, a.parentid, b.dataid, b.Name, b.subtype, Pathstr+ '\' + cast(a.name AS varchar(max)) as Location FROM recursive_tempDTREE as "a", dtree as "b" (nolock) WHERE b.parentid = a.dataid OR (b.parentid * -1) = a.dataid)) SELECT SPACE(level*10)+ c.Name as Name, c.dataid, dt.parentid, level, case c.subtype when 0 then 'Folder' when 144 then 'Document' Else 'Other Object' End As ObjectType, pathstr + '\' + cast(c.name AS varchar(max))as Location , llattrdata.ValStr as 'AttributeValue' FROM recursive_tempDTREE as "c", dtree as "dt" (nolock), llattrdata (nolock) WHERE dt.dataid = c.dataid AND c.dataid = llattrdata.id AND dt.dataid = llattrdata.id and llattrdata.attrid=3 UNION SELECT SPACE(level*10)+ c.Name as Name, c.dataid, dt.parentid, level, case c.subtype when 0 then 'Folder' when 144 then 'Document' Else 'Other Object' End As ObjectType,pathstr+ '\' + cast(c.name AS varchar(max)) as Location,' ' FROM recursive_tempDTREE as "c", dtree as "dt" (nolock) WHERE dt.dataid = c.dataid AND (c.subtype = 144 OR c.subtype=0) GROUP BY c.level, c.parentid, c.dataid, dt.parentid, c.name, c.subtype,c.pathstr order by pathstr+'\'+CAST (c.name as VARCHAR(MAX)), c.dataidMany Thanks,Atiya.
Lindsay_Davies
Message from Lindsay Davies <
ldavies@opentext.com
> via eLink
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink
Hi Atiya,
I would say that your issue is a direct result of your UNION.
Without seeing the data that is coming back and not being able to guess what you are trying to achieve,
it looks to me like your first use of the recursive common table expression (CTE) will pull back info for all subtypes in the hierarchy with the attribute value.
The second use of the CTE will pull back a subset of these rows (the folders and documents) with a blank string instead of the attribute.
So the Union adds the rows into the pot and then the order by sorts out the sequence.
I assume you did all that because some rows you wanted do not have the attribute applied.
That is what left outer joins are for. Select all rows on the left even if the rows on the right don't match.
So drop all the code from UNION onwards, then change the join condition.
For reference, check out this link....
http://www.techonthenet.com/sql/joins.php
Your use of LLAttrData.Valstr is equivalent to Order_date in the example.
I would also say that you are wasting effort recreating the pathstr when is is available already as c.Location.
Lindsay
From:
eLink Discussion: Open Text Live Reports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent:
2009 December 24, Thu 10:06
To:
eLink Recipient
Subject:
Thanks for the reply Appu.
Thanks for the reply Appu.
Posted by
atiya.sultana@accenture.com
(Sultana, Atiya) on 2009/12/24 05:05
In reply to:
First make a determination.If you are going the llattrdata way use a query to...
Posted by
anair@alitek.com
(Nair, Appu) on 2009/12/23 12:02
Thanks for the reply Appu.
Could you please suggest how to remove the empty and null rows in the AttributeValue column for the below query? Not sure why the results display the rows with corresponding attribute values as well the same row is repeated with blank attribute value.
WITH recursive_tempDTREE (level, parentid, dataid, name, Subtype, Pathstr ) AS ((SELECT 1, parentid, dataid, Name, Subtype as ObjectType, CAST ('' as VARCHAR(MAX)) FROM dtree (nolock) WHERE parentid = XXXX) UNION ALL (SELECT level + 1, a.parentid, b.dataid, b.Name, b.subtype, Pathstr+ '\' + cast(a.name AS varchar(max)) as Location FROM recursive_tempDTREE as "a", dtree as "b" (nolock) WHERE b.parentid = a.dataid OR (b.parentid * -1) = a.dataid)) SELECT SPACE(level*10)+ c.Name as Name, c.dataid, dt.parentid, level, case c.subtype when 0 then 'Folder' when 144 then 'Document' Else 'Other Object' End As ObjectType, pathstr + '\' + cast(c.name AS varchar(max))as Location , llattrdata.ValStr as 'AttributeValue' FROM recursive_tempDTREE as "c", dtree as "dt" (nolock), llattrdata (nolock) WHERE dt.dataid = c.dataid AND c.dataid = llattrdata.id AND dt.dataid = llattrdata.id and llattrdata.attrid=3 UNION SELECT SPACE(level*10)+ c.Name as Name, ! c.dataid, dt.parentid, level, case c.subtype when 0 then 'Folder' when 144 then 'Document' Else 'Other Object' End As ObjectType,pathstr+ '\' + cast(c.name AS varchar(max)) as Location,' ' FROM recursive_tempDTREE as "c", dtree as "dt" (nolock) WHERE dt.dataid = c.dataid AND (c.subtype = 144 OR c.subtype=0) GROUP BY c.level, c.parentid, c.dataid, dt.parentid, c.name, c.subtype,c.pathstr order by pathstr+'\'+CAST (c.name as VARCHAR(MAX)), c.dataid
Many Thanks,
Atiya.
