LiveReport for Custom Category

I have a custom category that has a User field (Select User). In reporting on that user field, how do I convert it from the number ID to its Livelink user ID? See attached document with screenshots from the Category, the edit screen of the Livereport and the Livereport results.

Thank you in advance for any help with this.

Mary Slane Corona

Category and LiveReport Information.docx

Find more posts tagged with

Comments

Hans Stoop

HI,

You need to join it with KUAF.ID and retrieve KUAF.Name.

Hans

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Mittwoch, 8. August 2012 02:25
To: eLink Recipient
Subject: LiveReport for Custom Category

[Attachments exceeded pre-set size limits. Use the links provided to access]

LiveReport for Custom Category

Posted bymary.corona@ejgallo.com (Slane Corona, Mary) On 08-07-2012 20:23

Thank you in advance for any help with this.

Mary Slane Corona

Attachment Links:

Category and LiveReport Information.docx (182 KB)

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Mary_Slane_Corona

Hans, thank you.

I'm not real good on coding, would anyone be willing to share the SQL code for Han's suggestion.

We are using Oracle 11g.

Thank you.

Hans Stoop

When SQL was first designed it was meant to be used by managersJ

After the select add: KUAF.Name,

After from add KUAF,

In the where clause add … = KUAF.ID ( I don’t remember exactly what your table was, something like g.ValInt (was was before DocOwnerID)

Hans

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Mittwoch, 8. August 2012 20:09
To: eLink Recipient
Subject: LiveReport for Custom Category

LiveReport for Custom Category

Posted bymary.corona@ejgallo.com (Slane Corona, Mary) On 08-08-2012 14:05

Hans, thank you.

I'm not real good on coding, would anyone be willing to share the SQL code for Han's suggestion.

We are using Oracle 11g.

Thank you.

[To post a comment, use the normal reply function]
Topic:	LiveReport for Custom Category
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Mary_Slane_Corona

Hans

Thank you for your help.

That code still isn't bringing back what I need. To be clear--the "Document Owner" attribute in the Category is not necessary the same person as the owner of the document in Livelink (or the person that added it to Livelink). When the Category is filled out, a user (person responsible for reviewing the documentation) is selected for the "Document Owner" attribute by the Document Admin. When I run the LiveReport with the following code, it comes up with the numeric user code. How do I convert that numeric code (g.valint as "Docowner") to the actual user ID? (Say the numeric code for that user was 123456 but the actual user ID was jsmith--I want the result to show jsmith)

Code is following.

SELECT x.name, a.reservedby, g.valint as "Docowner", a.name, b.valdate as "Docreview", decode(c.valstr,null,' ',c.valstr) as "Docnum", e.valstr as "Facility", f.valstr as "Dept"

FROM kuaf x, dtree a, llattrdata b, llattrdata c, llattrdata e, llattrdata f, llattrdata g

WHERE x.id = g.valint and a.dataid = b.id and a.versionnum = b.vernum and b.defid = 862314 and b.attrid=10 and a.dataid = c.id and a.versionnum = c.vernum and c.defid = 862314 and c.attrid=12 and a.dataid = e.id and a.versionnum = e.vernum and e.defid = 862314 and e.attrid=6 and a.dataid = f.id and a.versionnum = f.vernum and f.defid = 862314 and f.attrid=5 and a.dataid = g.id and a.versionnum = g.vernum and g.defid = 862314 and g.attrid=22 and a.subtype = 144 and e.valstr IN ('Coastal Operations','GVI & Coastal','Martini','Premium Wine Team','Sonoma','William Hill') and b.valdate >= %1 and b.valdate <= %2

ORDER BY "Facility", "Dept"

Hans Stoop

Looks correct to me except that you have x.name and g.name which will result in the same column name and that may explain the wrong result.

Hans

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Freitag, 10. August 2012 00:29
To: eLink Recipient
Subject: RE LiveReport for Custom Category

RE LiveReport for Custom Category

Posted bymary.corona@ejgallo.com (Slane Corona, Mary) On 08-09-2012 18:26

Hans

Thank you for your help.

Code is following.

SELECT x.name, a.reservedby, g.valint as "Docowner", a.name, b.valdate as "Docreview", decode(c.valstr,null,' ',c.valstr) as "Docnum", e.valstr as "Facility", f.valstr as "Dept"

FROM kuaf x, dtree a, llattrdata b, llattrdata c, llattrdata e, llattrdata f, llattrdata g

ORDER BY "Facility", "Dept"

[To post a comment, use the normal reply function]
Topic:	LiveReport for Custom Category
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Kelly_Charis

Code is following.

SELECT x.name, a.reservedby, g.valint as "Docowner", a.name, b.valdate as "Docreview", decode(c.valstr,null,' ',c.valstr) as "Docnum", e.valstr as "Facility", f.valstr as "Dept"

FROM kuaf x, dtree a, llattrdata b, llattrdata c, llattrdata e, llattrdata f, llattrdata g

ORDER BY "Facility", "Dept"

Try this: select x.name as "Docowner"
and x.id = g.valint
remove the g.valint as "Docowner" from your select clause

Mary_Slane_Corona

ckelly

That did it--it works just as I needed. Thank you so much.

Hans

Thank you for your help as well.

You both are wonderful.

Mary