I would like to create Excel spreadsheets that have "live" links to the Great Plains databases to eliminate the need for downloading data into the spreadsheet every time it needs to be updated.
Ideally, I would like to be able to enter parameters (e.g. account number and date range) in the spreadsheet to filter the data pulled from GP.
I was told this could be done, but I'm not certain how to go about it.
*This post is locked for comments
Use the Microsoft query wizard and use a parameter that will be a chic during the wizard. I can give more instructions if you need it, I am really crunched for time right now. I have been doing this for many many years. You will absolutely love it.
Kind regards,
Leslie
Dynamics GP 10 and up comes with a pre-defined set of Excel reports (over 350 If I'm not wrong) that you can install with the Excel report wizard from GP. This is basically the content of the built-in smart-lists made available thru Excel with a direct link to the GP Database. This will give you a very good overview of the Excel query possibilities which is a great tool for doing data mining if you know of to use it. If you use Office 2007 or 2010, I would even suggest you download the PowerPivot for Excel tools from Microsoft, that way you can do great BI analysis.
Leslie is right by saying that MS-query does really unleash the power of Excel with Database connectivity.
I forgot about the pivot tables - I need to go download them.
I have to problems with the .odc reports
1 they use a view that is a pain to change
2 you cannot use spreadsheet cell parameters within the .odc
Good news - you can create an odc from your ms query.
Kind regards,
Leslie
Leslie,
You're right about the parameters and the .odc file. But at least it gives you an understanding on how this works, for someone that never had linked Excel Workbooks to a database. The other thing is the permissions that need to be considered. With the OoB Excel report wizard comes a set of permission roles on the SQL server that gets installed where you have to put the user into in order to give them access to the data. I actually never got a response from Microsoft about this question on 'how to manage' the permission for the Excel reports in GP...
Even if you don't use the GP Excel reports, you'll have to deal with the data permissions on the SQL side. My best practice is to use the domain user's identity if it's in place, otherwise just create a dedicated SQL user for the sake of the Excel reporting queries (like you do with any other reporting tools). Linked AD users is just more secure in cas you manage several hundreds of data connections or reports with several users and departments. Using views is also more simplier to assign permissions, since you select much less objects that crawling thru tons of tables...
The best way i have found is to utilize the ODC option available in Office 2010, this way you can create a master excel file and have users use this as their template. So if the copy, modify layout, use pivot tbale...the can save their own copy without losing that live connection. Also, another benefit is that you can implement SQL and File Security. In office 2010 go to the Data tab, select From Other Sources>then From Data Connection Wizard. Using this allows you to avoid having to create an ODBC connection each client machine also.
Leslie, can you provide more detail about what you talked about in your first response? I need to do something similar with passing parameters through a query.
I'm finding the out of the box Excel Reports/Excel data connections meet some of my needs some of the time, it would be a more complete solution if I could build in some way of letting users specify parameters before generating the report. This is because the raw data for the reports the users need are several hundred thousand rows long, so Excel Reports can't even load a "master excel file." Also, trying to hardcode selections on this data is futile because the users have a virtually unknowable/changing combination of parameters for their reports. It needs to be ad-hoc.
One trick i have been using to pass parameters through a sql query is to create an extender window with the date ranges...then create the corresponding view with it. Then use that view within the sql view you are using through excel. When the user changes the date range in the Extender window it will update the sql view or excel file once refreshed. I have foind, placing the Window on the Smartlist toolbar works very well as you are not compromising security and not having to give access to a window that user would normally not have.
Hi Nathan,
As explained in this same thread earlier this year, you can do this by using the capability of MS-Query to deal with parameters. Thought they are limited in quantity, most of the time it's more then enough for the basic needs. Use the query from the Excel GP reports as an example and than modify it to accept parameters and then it can either a pop-up when the data is refreshed that asks for the value or you can even put the value in a pre-defined cell of the Excel sheet and point the parameter field to this cell... The data can be refreshed automagically when the value is updated. Have a look around internet on how to use the MS-Query in Excel with parameters.
I've used this for many years now and it just opened to all the users a whole new potential of using data from the back-end.
Good luck.
Perhaps I should have gone into more detail myself.
So there are two different scenarios. The first is to use the MS query wizard, the second is to use Excel Reports out of GP.
I have used the MS query wizard, connect to GP as the data source, created criteria, and then gone through the data > connection > properties > parameters windows to create parameters. So it allows me to pass parameters, which is what I want.
The first problem is, I have to hand recode the query statements because the MS query wizard doesn't pull in the GP objects correctly. E.g. MS query wizard wants to bring in Accounts.Account Description which needs to be formatted as Accounts.[Account Description] in order to work. And then there's some connection string changes I'd like to make so that it can be more user friendly.
The second problem is, I have to create a lot of reports. The time involved may or may not be acceptable to the users.
So in the second scenario, I use the Excel Reports out of GP. The column names come in properly, the connection string is more user friendly, and there are hundreds of reports out of the box. So that's all positive.
The problem is, when I go to data > connection > properties > parameters is greyed out. Theoretically, the OLEDB data source supports parameterized SQL statements, but I am not getting them to work in this scenario.
Here is my where clause: WHERE (Accounts.Active='Yes') AND (Accounts.Segment4=?) Works fine in queries that use ODBC but its not so simple to just copy & paste into queries that use OLEDB, and whatever additional steps are required, or supported, for Excel Reports with GP are currently unknown to me.
I am not sure if this approach will be acceptable from a business/efficiency standpoint, either, but I'd like to prove to myself what the technical capabilities are.
So... that's a lot to read. I'm hopeful that if someone has had success with parameterized queries and OLEDB data sources (preferably applied to the Excel Reports tool in GP) they can provide some insight.
Share
Report
All responses (
Answers (
