Creating a report to show categories of specific documents in a folder

Hi everyone,I have a reference library in Livelink Enterprise. Within the library, we have different areas and types of documents.We have set up categories for each document based on document type and tax type (income, sales, etc). I am trying to create a sql query that will show me within a specific folder a list of the documents along with their tax type.Using the parent id of the documents allows me to select the folder I want and the documents I am looking for. My problem is that, every document shows a row for each tax type that is offered, not the tax type that is actually selected.I can't seem to find the table or column that pertains to which tax type was actually selected.Anyone have any ideas?Thank you in advance.

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

What's the SQL query you are using so far?

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Tuesday, February 24, 2009 10:22 AM
To: eLink Recipient
Subject: Creating a report to show categories of specific documents in a folder

Creating a report to show categories of specific documents in a folder

Posted by nystax01user3 (Young, Lori C) on 2009/02/24 10:20

Hi everyone,
I have a reference library in Livelink Enterprise. Within the library, we have different areas and types of documents.

We have set up categories for each document based on document type and tax type (income, sales, etc).

I am trying to create a sql query that will show me within a specific folder a list of the documents along with their tax type.

Using the parent id of the documents allows me to select the folder I want and the documents I am looking for. My problem is that, every document shows a row for each tax type that is offered, not the tax type that is actually selected.

I can't seem to find the table or column that pertains to which tax type was actually selected.

Anyone have any ideas?
Thank you in advance.

Lori_Young

while I was waiting your response, I found this in the knowledge base, so I changed it for my specifications and got the SQL query to work, but I cannot get the livereport to work. Here is what I have:I ran this one first:select catid, catname, attrname, regionname from catregionmap where catname='NYS Global'Got the catid and attrid, then ran this:create view typecategories (name, valstr, id) asselect t.name, a.valstr, a.id from LLAttrData a, DTree t where (t.DataID=a.ID and a.DefID=6272 and a.AttrID=4 and t.VersionNum=a.VerNum)that gave me the results I was looking for. I then went into livereports and put this in the sql statementselect * from typecategories, dtree where (dtree.parentid like %1||'%%')put an input box for parentid in param %1I run the report and get the input box, put in the id and update. I receive an error message: Invalid object name 'typecategories'. - select * from typecategories, dtree where (dtree.parentid like :A1||'%')] Not sure why.I am receiving the following message:

Appu_Nair

i have not looked at the query but since livelink is complaining about typecategories,are you sure you created that within the livelinkschema.If you open the view for your query tool there is data.You could try prefixing it with schemaname.owner.For eg qall.lluser.typecategoriesalso the query will return dtree parentid object but there is not anything that ties it to your view I would think you would need to tieselect * from ..typecategories, dtree where (dtree.parentid like %1||'%%') and dtree.id=typecategories.id

Lori_Young

I did put in the later part of connecting the parentid. thank you for that info. The typecategories is a view, so I am not really sure if that is causing the problem.

Appu_Nair

a view or table does not make any difference.The user for livelink should have create View,did you create the view as the livelink database user or super user.In the latter I would suggest you just look at security on the view.I create views like this all the time for live reports and have not had any problems,the article talks about creating views for use in a livelink livereport so views should work.Test the query in a sql tool like TOAD,SQLplus or SQLserver management studio if it works and livelink LR does not then you have chanced on something.The LR area where you type your query is not extremely friendly with CR/LF so make sure there are spaces etc between the sql in those .Just a thought

Greg_Griffiths_(ggriffiths_-_(deleted))

Causeway (http://www.causeway.com) do a module called Category Browse which shows selected Attribute / Category information at the Browse View level and should do what you want at the GUI level so you won't need the report.

Bhupinder_Singh

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

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

See also the CategoryView customization, referenced here:

https://knowledge.opentext.com/go/5169829

- Bhupinder----------------------------------------------
Bhupinder Singh, B.Math, B.Ed.
Senior Systems Analyst, Information Technology
Open Text, Waterloo, Ontario, Canada
----------------------------------------------

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, March 02, 2009 3:39 PM
To: eLink Recipient
Subject: some thoughts

some thoughts		Posted by ggriffiths (Griffiths, Greg) on 2009/03/02 15:34
In reply to: Creating a report to show categories of specific documents in a folder		Posted by nystax01user3 (Young, Lori C) on 2009/02/24 10:20

Evert_Kuiken_(ekuiken_-_(deleted))

MorningStar has a similar module called PresentationPlus, which we have running:http://www.morningstarsystems.nl/index.asp?parentid=46&itemID=166&page=Add on modules voor Livelink ECM#And BluBaker has a system that runs outside of Livelink (not as a module), that enables you to extract the Category information to Excel, modify it using Excel functionality and updating the Category again with the modified values. We have this running as well.But there will me several other options as well.