Reports / Database data

Hi,

 

We've had a report generated, where would that data live within the database? I would like to query that data for an external process. If that is not possible, would you know how I would be able to tie an attribute and attribute value to a ticket?

I can get the tickets I need to query based on their app id and form id, but I don't see how I can tie the attribute to the ticket.

 

This query will get me the tickets where a user has submitted a request, but I don't know how to then tie to the Attribute tables to get the values from there.

 

    SELECT *
    FROM [TDX].[usd-prd].[dbo].[Users]
    INNER JOIN [TDX].[usd-prd].[dbo].[Tickets]
    ON [Tck_CreatedByUID] = GUID
    INNER JOIN [TDX].[usd-prd].[dbo].[Attributes]
    ON BE_ID = Att_BEID and att_id = 2058
    WHERE [Tck_AppID] = 38 and Tck_FormID = 226

 

AV_ATTID 2058 = Request

AV_ATTID 2064 = Print Date

 

I need to pull data for another process from the db, on when the request was made and when the Card was printed, I just don't see what values I would use to connect the 2. I didn't see anything in Forms to connect them either.

 

I would assume tickets > forms > attributes > attributes values, but again didn't see a good way to connect them. 

 

 

Thanks,

Tags ticket attribute attribute-value connection
Asked by Alex Haberer on Mon 5/3/21 9:24 AM
Sign In to leave feedback or contribute an answer

Answer (1)

This answer has been marked as the accepted answer
Mark Sayers Mon 5/3/21 11:24 AM

Hello Alex,

You can't just "run" a report from the database side that you have configured in report builder. It isn't stored anywhere. The app saves metadata about the report and generates the query on the fly, from application code.

The tables involved are:
- Attributes (where Att_BEID is not null). This is the table of custom attributes created by the customer. IDs match the custom attribute IDs in Admin. It is split up by a component ID column, which joins to the Components table, to see what area of the system the attribute is for.
- AttributeChoices. This describes all choice IDs and names for choice-based attributes. IDs align to what you see in Admin.
- AttributeValues. This describes the *saved* custom attribute values on a project/ticket/item/etc. You have to make use of the ComponentID and ItemID columns to join to tickets properly otherwise you can get duplicate ItemIDs between different types of items. If it is a choice-based attribute, the ID of the saved choice will be in the Value column, otherwise saved values will be in ValueText, ValueInt, ValueDecimal, ValueDatetime, etc. Attributes which support multiple choices will have a row per selected choice if a selection with multiple choices is saved for an item.
Ex: Select Top 10 * From dbo.AttributeValues Where AV_ItemComponentID = 9 And AV_ItemID = [ticket ID] And AV_ATTID = [attribute ID]
9 is the component for tickets. If you were doing projects, you'd use 1 and AV_ItemID would be a project ID.

Let me know if that helps or if you have any additional questions.
 

No feedback