How to Surface Excel Data in the Business Analyzer

The New Business Analyzer in Microsoft Dynamics GP 2010 R2 renders SQL Server Reporting Services (SRS) reports at the time and place that makes sense for you. Business Analyzer can display reports from your Navigation Lists or right on your desktop. Not only can reports be viewed but you can also take actions against those reports to easily edit and collaborate with others.

Here's how.

1. Create an ODBC connection to the Excel file you want to use as a data source

Start>>Control Panel>>System and Security>>Administrative Tools>>Data Sources (ODBC)

Select the System DSN from the ODBC Data Source Administrator window

 

Select the Add

Select Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb) from drop down list of available drivers

 

 Select Finish

Enter a Data Source Name and Description

Select the Select Workbook ... Button

Browse to the file you want to use as the data source and select OK.

Select OK

Your data source should now appear in the list of System DSN's.

Select OK to close the window.

2. Create the SQL Server Reporting Services Report

a. Open Report Builder 2.0 or Report Builder 3.0 (Start > All Programs > Report Builder 2.0/3.0)

b. Select to create a blank report.

Right Mouse click over Data Sources and select Add Data Source

Enter a Name for the data source

Select radio option to “Use a connection embedded in my report”

Select ODBC from the Select connection type drop down

Select the Build button

On the Connection Properties window select the System DSN you created

Select Test Connection

Select OK to close the window

Select OK to close the Data Source Properties window

Perform a right mouse click over Datasets and select Add Dataset

Enter a Name for the dataset.

Select the radio option to “Use a dataset embedded in my report.”

Select “Excel” as your data source (step 2D)

Enter “Select * from [sheet$] “in the query field

Select Query Designer…

Execute the query by selecting the red exclamation point to valid your query

Select OK to close the Query Designer window

Select OK to close the Dataset Properties window

You now have your Excel file set as your data source and you are ready to build a report.