I'm looking to create a report that will show the asset name, system description, Gl number and location. this is pretty easy but I'm looking for a way to also show from history "who" changed a gl or location and "when" if possible.

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

1

Try this out - you may need to use the report wizard to create a dummy report and go into the SQL version of the dummy report to get the RD columns and LEFT JOIN statements to work for your setup, but otherwise it should give you want you need.

Hope that helps!

John

___________________

SELECT A.NAME, AH.DESCRIPTION, A.MODIFIED, U.FULL_NAME,
RD3.FIELD_9 AS PO_NUMBER, RD3.FIELD_6 AS UNIT_COST

FROM ASSET_DATA_5 

LEFT JOIN ASSET A on (ASSET_DATA_5.ID = A.ASSET_DATA_ID AND A.ASSET_TYPE_ID=5)

LEFT JOIN ASSET_ASSOCIATION JR3 on (JR3.ASSOCIATED_ASSET_ID = A.ID AND JR3.ASSET_FIELD_ID=3)

LEFT JOIN ASSET R3 on (R3.ID = JR3.ASSET_ID)

LEFT JOIN ASSET_DATA_7 RD3 on (RD3.ID=R3.ASSET_DATA_ID)

JOIN ASSET_HISTORY AH on (AH.ASSET_ID = A.ID)

JOIN USER U on (U.ID = AH.USER_ID)

WHERE A.ID = AH.ASSET_ID

Answered 06/30/2012 by: jverbosk
Red Belt

  • Thanks a bunch. I'll give it a shot.
Please log in to comment

Answers

0

Maybe take a look at this and see if you can determine what table that information is in.  Then it would be pretty easy to include it in your report.

http://www.itninja.com/blog/view/primer-for-writing-select-statement-queries-on-the-k1000-w-custom-sql-report-example

John

 

Answered 06/25/2012 by: jverbosk
Red Belt

Please log in to comment
0

*Edit - the ITNinja bug of not catching line breaks struck again...

Specifically, look at the ASSET_HISTORY table, it should have what you want.  Try this to get started:

SELECT * FROM ASSET A, ASSET_HISTORY AH
WHERE A.ID = AH.ASSET_ID

John

Answered 06/28/2012 by: jverbosk
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity