Editing SME and Excel - Extracting Data in Meaningful ways
Jump to navigation
Jump to search
Warning: You are not logged in. Your IP address will be publicly visible if you make any edits. If you log in or create an account, your edits will be attributed to your username, along with other benefits.
The edit can be undone. Please check the comparison below to verify that this is what you want to do, and then save the changes below to finish undoing the edit.
Latest revision | Your text | ||
Line 17: | Line 17: | ||
# Print out to excel | # Print out to excel | ||
+ | |||
+ | |||
<h3>Connect Excel directly to SQL Server</h3> | <h3>Connect Excel directly to SQL Server</h3> | ||
Line 22: | Line 24: | ||
This is the most powerful and flexible way to utilize SME with excel, however takes a bit more effort to setup. | This is the most powerful and flexible way to utilize SME with excel, however takes a bit more effort to setup. | ||
− | * Open Excel | + | # Excel 2007 onward now has capability to connect directly to SQL Server. Follow these steps |
− | * | + | * Open Excel 2007 or 2010 |
− | * | + | * Select Data tab |
− | + | * Select From Other Sources and select "From SQL Server" | |
− | + | * Enter your SQL Server instance name ( see step 5 onward below) | |
− | * | + | * Select your SQL database |
− | + | * Then you will be provided with a list of SME tables to select | |
− | * | + | ** For Excel 2003: Select +New SQL Server Connection.odc |
− | * | + | ** [[File:ExcelConnect1.png]] |
− | + | * Enter the Server Name and select Next | |
− | * | + | ** [[File:ExcelConnect2.png]] |
− | + | * Select Database and table and select next | |
− | * | + | ** [[File:ExcelConnect3.png]] |
− | + | * Provide a file name, description and search terms and select Finish | |
− | * | + | ** [[File:ExcelConnect4.png]] |
− | + | * Select a location for the data | |
− | * | + | * You can also edit the query for the data |
− | + | ** [[File:ExcelConnect5.png]] | |
− | * | + | * Note: Without editing the query, it will bring in the entire table. Most likely you will only want to bring in specific data |
− | + | *Example: Pull in Customer name, city, state, Contact and email: | |
− | * | + | #[[File:0300000A.png]] |
− | + | #This requires knowledge of SQL. | |
− | + | <br>[[File:0300000B.png]] | |
− | * | + | # More complex SQL queries can be performed, please contact your local sql expert. |
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | <br>[[File: | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | <h3>Outputting search grids to excel</h3> | + | <h3>Print Report to Excel by Outputting search grids to excel</h3> |
The final method is to print a report to Excel. | The final method is to print a report to Excel. | ||
Line 69: | Line 57: | ||
# First create the search grid or search view you want. There are many ways to get the grid to show information you want. Follow these steps and options to get the search grid to show what you want | # First create the search grid or search view you want. There are many ways to get the grid to show information you want. Follow these steps and options to get the search grid to show what you want | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | < | + | * Go to the module you want for information. For example, customers, service orders, invoices, etc. |
+ | * Select Search | ||
+ | * Select Options>Add Fields to Grid to select the information you want in the grid and then in excel. | ||
+ | * Arrange the columns how you want by dragging the columns to the appropriate position. | ||
+ | * If you want to group item, select the column and drag into the grey area above the columns. | ||
+ | * If you want to filter certain data, select the green funnel icon towards the bottom left. This pulls up a filter too to filter on available data. | ||
+ | |||
+ | <br>2. Next select Options>Export to Excel and select the path and filename for export. | ||
+ | <br>3. Finally, open the Excel file you created and manipulate data as needed. | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
All reports can be output as many different types of formats, for example graphic formats, pdf, and Excel. | All reports can be output as many different types of formats, for example graphic formats, pdf, and Excel. | ||