Ad Hoc Reports
Build:
Run, Export, And Sort In Excel:
Provides the ability to query data from the database without the expense of a custom report.
Custom forms can more more complex data needs, but Ad Hoc queries are suitable in simple to medium complex cases with fixed parameters.
The code must be in SQL Server’s TSQL language and would require basic knowledge of TSQL programing.
The History button is used to keep a list of all queries that have been run within the past 2 years. After the 2 year time frame, the query will be removed from the History.
Do not use an Order By clause. Perform any sorting on the resulting exported file.
Table names must be prefixed with 'dbo.' .
Tables can be linked using joins and subqueries are allowed.
There is no charge for writing your own Ad Hoc queries. Detailed queries can be requested from ARTSCO for a fee.
An example of a query phrase: 'Select TOP 100 PERCENT FirstName, LastName, EMail, Street1, Street2, City, State, Zip FROM dbo.patient' would produce a spreadsheet of all patients from the user's database. The information included in the spreadsheet would be the patient's first and last name, email. street, city, state and zip code.
Do not use semicolon at the end of the SQL statement.
Comments must appear after a double dash on each line and before the SQL statement.
Options
- Table: A drop down list of tables in the database which can appear in the query - Tables can be linked using joins and subqueries are allowed. All tables in the query must begin with 'dbo.' - Select the drop down table, click in the SQL statement where the table should appear, then click the associated Insert button
- Field: A drop down list of fields for the selected table in the database which can appear in the query - Select the drop down table, then select the drop down field, click in the SQL statement where the field should appear, then click the associated Insert button - Separate fields with a comma - Use a * to specify all fields in the table
- Include Header: When checked on, includes a header row with column names in the spreadsheet file
- Query: SQL query to run - The results of the query can be downloaded as a .csv file which can be opened in any spread sheet program including Excel - The Table and Field drop down selections show the tables and fields available for the SQL statement - Use the history button to view and copy queries run in the last 2 years - Do not use an Order By clause - Perform any sorting on the resulting exported file after opening into a spread sheet program - Table names must be prefixed with 'dbo.' - Tables can be linked using joins and subqueries are allowed - Do not use semicolon at the end of the SQL statement - Comments must appear after a double dash on each line and before the SQL statement
Actions
- Build: Build the SQL query in the Query field - The Table and Field drop down selections show the tables and fields available for the SQL statement - Use the history button to view and copy queries run in the last 2 years - Do not use an Order By clause - Perform any sorting on the resulting exported .csv file opened in any spread sheet program including Excel - Table names must be prefixed with 'dbo.' - Tables can be linked using joins and subqueries are allowed - Do not use semicolon at the end of the SQL statement - Comments must appear after a double dash on each line and before the SQL statement
- Run: Click the Run button at the bottom of the window to run the SQL query in the Query window - Update the query based on any error messages shown. If no error messages are shown, the query ran successfully
- Export: Click the Export button at the bottom of the screen - Optionally set a name for the .csv file - The file is saved on the computers local drive and can be opened in any spread sheet program including Excel
- Sort: Open the exported file and sort as appropriate and resave the file - If using Excel follow the Run, 'Export, And Sort In Excel' video above
