How can I report on all topics on the same line?
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
Answers (1)
Please log in to answer
Posted by:
feeldamped
5 years ago
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.
Could you post the SQL query for the report? - feeldamped 5 years ago
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? - Karllap 5 years ago
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. - feeldamped 5 years ago
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 - Karllap 5 years ago