Desktop and Enterprise Software, Solutions and Services for Chemists and Biologists.

Desktop Support » FAQ » Details

Question:

Customizing the SQL View for Inventory EHS reports

- Adding variables to the existing View in the CHEMINVDB2 database

Answer:

Install the Microsoft SQL Server Management Studio:
- Make sure to right-click the installer >> Run as Administrator
- Once the installation is completed login with a Windows Administrator account or with the following SQL credentials:
Instance: CSSQL05
User: sa
Password: CambridgeSoft_SA
- Expand the "Databases" folder
- Expand "Views"
- Right-click dbo.EHS_CONTAINERS_V and select "Design"
- In the graphic tables pane right-click the frame and select the option "Add Table"
- Select the desired table and click Add then Close
- Within the graphic representation of the SQL Table choose the desired variable to import into the Inventory EHS Reports
- Save the SQL View
- Launch Inventory
- Click on SQL Menu >> Select "Edit EHS Report SQL"
- Click the New button
- Give a name in the Query Name field and Query Description fields(test)
- Enter the following in the SQL field


SELECT *
FROM EHS_CONTAINERS_V
WHERE LOCATION_ID_FK IS NOT NULL AddToWhereClause
ORDER BY Substance_Name



- Click the "Create" button then the "Close" button.
- Highlight an Inventory location from the left pane
- Click on the Report Menu >> EHS >> Report by Location
- From the dropdown list select the query that you created previously(test)
- Click the Design button and point to the following template for custom EHS reports
http://media.cambridgesoft.com/support/Custom_EHS_Report_template.lst
- Now the Design form will appear, from here you can modify the reports to your own convenience.
- In the right pane you will see the List of Variables available from the SQL View, please expand the "Fields" folder
- Select the desired varibles for the report and drag them to the Layout pane into the table.
- Save your report and then you can print it from the EHS Report editor.


Created on: 7/19/2013