Creating a Custom K1000 Report
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.
Answers (3)
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
Comments:
-
Thanks a bunch. I'll give it a shot. - ckubaska 12 years ago
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
*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
