/build/static/layout/Breadcrumb_cap_w.png
03/28/2019 89 views

I am trying to figure out how to create a encompassing report via the Report Wizard that would let me generate a daily report (if any changes) when an Asset's status changes from Active to Repair/Disposed as well as (if Repair) when it returns to Active. Is that doable in the same report and if so what are some suggestions as far as setup.

0 Comments   [ + ] Show comments

Comments


All Answers

1

Sure, but you may have to build it with a SQL report rather than the Wizard.   When reports require a complex query I like to connect to the KACE database using FlySpeed ( https://www.activedbsoft.com/download-querytool.html)  because it creates the SQL in the native DB language for the DB you are connecting to.

Here is a quick query that will give you the changes for the past day.  You can alter "Interval 1 Day" to however many days you want.


Select

  ASSET_HISTORY.TIME,

  ASSET_HISTORY.NAME,

  ASSET_HISTORY.USER_ID,

  ASSET_HISTORY.USER_TEXT,

  ASSET_HISTORY.CHANGE_TYPE,

  ASSET_HISTORY.FIELD_NAME,

  ASSET_HISTORY.VALUE1,

  ASSET_HISTORY.VALUE2,

  ASSET_HISTORY.FRIENDLY_FIELD_NAME

From

  ASSET_HISTORY

Where

  ASSET_HISTORY.TIME > Date_Sub(Now(), Interval 1 Day) And

  ((ASSET_HISTORY.VALUE2 = 'Repair') Or

    (ASSET_HISTORY.VALUE2 = 'Disposed') Or

    (ASSET_HISTORY.VALUE2 = 'Active'))

Order By

  TIME

Answered 03/28/2019 by: JordanNolan
Sixth Degree Black Belt