/build/static/layout/Breadcrumb_cap_w.png
04/02/2019 70 views

Hi,

I am wanting a device report but also need an added topic to show ship date and warranty but the added topic is on line breaks when I do the report. Is there anyway I can add these columns to the same line as the device columns?

Thanks

3 Comments   [ + ] Show comments

Comments

  • Hi,

    Could you post the SQL query for the report?
  • Hi,
    I created the report through the wizard not through SQL. If there is a way I can access the SQL could you let me know please?
    • Hi,

      Even if you made it in the Wizard - open up the report and at the bottom of the Report Wizard there is a button that says 'Edit SQL'.

      Click on that and copy the SQL code over! I can take a look and help you add this to the same line.
  • Hi,
    Sorry, realised that the "edit SQL" is removed once you add another topic. So not sure if this helps but the main part of the report is all the device / user information which is in teh SQL query below. I just need to add the warranty start and stop date on the same row if possible

    SELECT MACHINE.NAME AS SYSTEM_NAME, CSP_ID_NUMBER, USER_FULLNAME, ASSET_OWNER.USER_NAME AS ASSIGNEE_LOGIN, ASSET_OWNER.EMAIL AS ASSIGNEE_EMAIL, ASSET_DATA_5.FIELD_10004, ASSET_DATA_5.FIELD_10005, OS_NAME, CS_MANUFACTURER, CS_MODEL, PROCESSORS, RAM_TOTAL, round(SUM(MACHINE_DISKS.DISK_SIZE),2) AS MACHINE_DISKS_DISK_SIZE FROM MACHINE LEFT JOIN ASSET ON ASSET.MAPPED_ID = MACHINE.ID AND ASSET.ASSET_TYPE_ID=5 LEFT JOIN USER ASSET_OWNER ON ASSET_OWNER.ID = ASSET.OWNER_ID LEFT JOIN ASSET_DATA_5 ON ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID LEFT JOIN MACHINE_DISKS ON (MACHINE_DISKS.ID = MACHINE.ID) WHERE ((ASSET_DATA_5.FIELD_10004 = 'Fendercare')) GROUP BY MACHINE.ID ORDER BY FIELD_10004

All Answers

1
SELECT MACHINE.NAME AS SYSTEM_NAME	,CSP_ID_NUMBER	,USER_FULLNAME	,ASSET_OWNER.USER_NAME AS ASSIGNEE_LOGIN	,ASSET_OWNER.EMAIL AS ASSIGNEE_EMAIL	,ASSET_DATA_5.FIELD_10004	,ASSET_DATA_5.FIELD_10005	,OS_NAME	,CS_MANUFACTURER	,CS_MODEL	,PROCESSORS	,RAM_TOTAL	,round(SUM(MACHINE_DISKS.DISK_SIZE), 2) AS MACHINE_DISKS_DISK_SIZE        ,START_DATE        ,END_DATEFROM MACHINELEFT JOIN ASSET	ON ASSET.MAPPED_ID = MACHINE.ID		AND ASSET.ASSET_TYPE_ID = 5LEFT JOIN USER ASSET_OWNER	ON ASSET_OWNER.ID = ASSET.OWNER_IDLEFT JOIN ASSET_DATA_5	ON ASSET_DATA_5.ID = ASSET.ASSET_DATA_IDLEFT JOIN MACHINE_DISKS	ON (MACHINE_DISKS.ID = MACHINE.ID)LEFT JOIN DELL_WARRANTY	ON MACHINE.BIOS_SERIAL_NUMBER = DELL_WARRANTY.SERVICE_TAG    WHERE ((ASSET_DATA_5.FIELD_10004 = 'Fendercare'))GROUP BY MACHINE.ID ORDER BY FIELD_10004


Does this work? I joined DELL_WARRANTY on the MACHINE and then added the Warranty Start and End Date columns. 

Answered 04/08/2019 by: feeldamped
Senior Purple Belt