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