Need help reporting task attributes..

David_Beliveau_(sanmin013_-_(deleted))

I am using the following SQL (ms sql 7.0) to obtain status information for a specific task list:select DTree.*, KUAF.Name as kuafname, (CASE DTree.status When 0 Then 'Pending' When 1 Then 'In Process' When 2 Then 'Issue' When 3 Then 'On Hold' When -1 Then 'Completed' When -2 Then 'Cancelled' Else 'Unknown' End) as mystatus from DTree, KUAF where SubType=206 and KUAF.ID=DTree.Assignedto and %1 and((DTree.Status=0) or (DTree.Status=1) or (DTree.Status=2) or (DTree.Status=3) or (Dtree.Status=-1) or (Dtree.Status=-2)) and (DTree.OwnerID=88079) order by DTree.Priority DESC, DTree.DateDue*the %1 variable is set to filter permissions.The owner of the task list has now created a category with a couple of attribute values and assigns an attribute to each task. Does anyone know how I could add this attribute value for each task as a column in the livereport?Any help would be appreciated. Thank you,Dave Beliveau

eLink User

Message from Marie Lindsay <<A HREF="mailto:marie@opentext.com">marie@opentext.com> via eLink

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

As you probably know, the attribute data is stored in LLAttrData.  LLAttrData.ID is a foreign key to DTree.DataID, identifying the task.  LLAttrData.DefID is a foreign key to DTree.DataID, identifying the category. LLAttrData.AttrID is a number representing an attribute within the category. The value for an attribute is stored in one of LLAttrData.ValStr, ValDate, ValInt, etc., depending on the data type of the attribute.

 

I'm assuming that it's always the same attributes within the same category that are assigned to the tasks. With that in mind, here's how I'd approach this:

 

- Get the category's DataID. (One method: Visit the category and look in the URL for "objID=".)

- Get the attrIDs of the attributes you want to query. (One method: Visit the category and hold your cursor over one of the attributes. In the status bar at the bottom of the page, there will be a JavaScript call that passes a numerical parameter, for example, javablahblah('3'). The "3" is the attrID for the attribute.)

- Consider the datatypes of the attributes you want to query, and decide whether you'll need to select LLAttrData.ValStr, LLAttrData.ValInt, or one of the other "Val" columns.

- Now that I have all the building blocks. For the sake of example, imagine that I learned that the category's DataID is 1234, the attribute I want to query has an attrID of 3, and the attribute is a string. I can add the string I want to select to the SELECT, for example:

 

    Select DTree.*, ...., LLAttrData.ValStr

 

And I can add the join and other criteria to the WHERE, for example:

 

   where .... and DTree.Dataid = LLAttrData.ID

                 and LLAttrData.DefID = 1234

                 and LLAttrData.AttrID = 3

 

...And that ought to do it. Caution: I didn't test any of this, so good luck.