Difference between revisions of "SME and Excel - Extracting Data in Meaningful ways"
Jump to navigation
Jump to search
| Line 24: | 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 | |
| − | + | * Go to the ‘Data’ tab | |
| − | + | * ‘From Other Sources’ | |
| − | + | * From Microsoft Query | |
| − | + | <br>[[File:Excel1.png]]<br> | |
| − | + | * Choose your Data Source (Contact High5 Software if you need assistance setting up your ODBC connection). | |
| − | + | <br>[[File:Excel2.png]]<br> | |
| − | + | * Click on the data source and click ok | |
| − | + | * You will be asked to log in. Put in your SQL password or contact High5 to set one up. | |
| − | + | <br>[[File:Excel3.png]]<br> | |
| − | + | * Set the options, uncheck all except Tables, set the Owner to ‘dbo’ as it makes finding the tables easier and then choose your columns. | |
| − | + | <br>[[File:Excel4.png]]<br> | |
| − | + | * I recommend locating the table you are looking for and adding only one column, then editing the query in MS Query and adding the remainder. | |
| − | + | <br>[[File:Excel5.png]]<br> | |
| − | + | * Click ‘Next’ and pick a sort field. It’s better to wait until you edit the query. | |
| − | + | <br>[[File:Excel6.png]]<br> | |
| − | + | * Filter Data - It’s better to wait until you edit the query. | |
| − | + | <br>[[File:Excel7.png]]<br> | |
| − | + | * Click ‘View data or edit query in Microsoft Query | |
| − | + | <br>[[File:Excel8.png]]<br> | |
| − | + | * The Query Editor opens | |
| − | + | * First step – turn off Auto Query or the query will attempt on every change. | |
| − | <br>[[File: | + | <br>[[File:Excel9.png]]<br> |
| − | + | * Show hide Criteria – will be where you limit your query | |
| + | <br>[[File:Excel10.png]]<br> | ||
| + | * Set your filter criteria: | ||
| + | <br>[[File:Excel11.png]]<br> | ||
| + | * Add fields | ||
| + | <br>[[File:Excel12.png]]<br> | ||
| + | * Sorting | ||
| + | <br>[[File:Excel13.png]]<br> | ||
| + | * Return the data to Excel | ||
| + | <br>[[File:Excel14.png]]<br> | ||
| + | * Pick the row/column for your data. | ||
| + | <br>[[File:Excel15.png]]<br> | ||
| + | * Returned Data | ||
| + | <br>[[File:Excel16.png]]<br> | ||
| + | * To edit the query: | ||
| + | * Right click in the query and select Table> Edit Query from the menu. | ||
<h3>Outputting search grids to excel</h3> | <h3>Outputting search grids to excel</h3> | ||
Revision as of 22:05, 18 February 2020
SME and Excel: Extracting Data in Meaningful ways
Summary:
SME: Service Management Enterprise provides a complete service management solution with complete reporting capabilities. As you know, Excel is a powerful program for data manipulation and reporting. Combining the capabilities of SME and Excel give a powerful way of working with business data.
SME has multiple ways to work with Excel. This document describes the various ways to utilize excel with SME.
Ways of Using Excel with SME:
- Connect Excel direct to SQL Server (preferred)
- Output search grids to excel
- Print out to excel
Connect Excel directly to SQL Server
This is the most powerful and flexible way to utilize SME with excel, however takes a bit more effort to setup.
- Open Excel
- Go to the ‘Data’ tab
- ‘From Other Sources’
- From Microsoft Query
- Choose your Data Source (Contact High5 Software if you need assistance setting up your ODBC connection).
- Click on the data source and click ok
- You will be asked to log in. Put in your SQL password or contact High5 to set one up.
- Set the options, uncheck all except Tables, set the Owner to ‘dbo’ as it makes finding the tables easier and then choose your columns.
- I recommend locating the table you are looking for and adding only one column, then editing the query in MS Query and adding the remainder.
- Click ‘Next’ and pick a sort field. It’s better to wait until you edit the query.
- Filter Data - It’s better to wait until you edit the query.
- Click ‘View data or edit query in Microsoft Query
- The Query Editor opens
- First step – turn off Auto Query or the query will attempt on every change.
- Show hide Criteria – will be where you limit your query
- Set your filter criteria:
- Add fields
- Sorting
- Return the data to Excel
- Pick the row/column for your data.
- Returned Data
- To edit the query:
- Right click in the query and select Table> Edit Query from the menu.
Outputting search grids to excel
The final method is to print a report to Excel.
- Output search grids to excel:
This is the simplest and quickest way to get data to excel. Any data you can show in an SME search grid can be output to excel. Here is how:
- 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.
- Next select Options>Export to Excel and select the path and filename for export.
- Finally, open the Excel file you created and manipulate data as needed.
Print Report to Excel
All reports can be output as many different types of formats, for example graphic formats, pdf, and Excel.
- Select the report you want to output to excel. You can select the reports in Doc Center>Reports or in the individual modules
- Select print
- Towards the bottom, select Print to File
- Select Excel File
- Select the location and name of the file to export