Editing SME and Excel - Extracting Data in Meaningful ways

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:

#    Output search grids to excel
#    Connect Excel through ODBC or direct to SQL Server
#    Print out 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.

<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.

 

 

*    Connect Excel through ODBC or direct SQL Server

This is the most powerful and flexible way to utilize SME with excel, however takes a bit more effort to setup.

#    Excel 2007 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
<br>2.    To setup an ODBC connection see [[ODBC Connection]]

<br>3.    For Excel 2003:  Select +New SQL Server Connection.odc

<br>[[File:03000005.png]]


<br>4.Enter the Server Name and select Next

<br>[[File:03000006.png]]

<br>5.Select Database and table and select next
<br>[[File:03000007.png]]

<br>6.Provide a file name, description and search terms and select Finish
<br>[[File:03000008.png]]

<br>7.Select a location for the data
<br>8.You can also edit the query for the data
<br>[[File:03000009.png]]

<br>9.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.

*    The final method is to print a 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